I am stuck with a logic challenge. Its best illustrated by an example. This is how my hold file looks:

srt col1 col2 col3 period amount
1 a aa li1 1/1/2012 $11
2 a aa li1 1/2/2012 $12
3 a aa li3 ...
4 a aa li4 ...
5 c dd li5 ...
6 c dd li6 ...
7 d ee li7 ...
8 e ff li8 ...

note1: The "srt" is a sorting column holding numerical value in ascending order to help with placing rows in a particular order. Since the final output is a financial statement, this approach is handy.
note2: in the final output, the "period" column is displayed as across.

This is how I would like my final report to look:
srt col1 col2 col3 1/1/2012 1/2/2012
1 a aa li1 $11 $12
2 li2 ...
3 li3 ...
4 c dd li5 ...
5 li6 ...
6 d ee li7
7 e ff li8 ...

However for the life of me, I am not able to get the similar data to blank out with "SUM" command due to the srt column's changing values. I thought about using "COMPUTE" but WF complains BY TOTAL COMPUTE is now allowed with Across keyword.

Please please do not suggest "FOR" or financial reporting since it comes with its own set of headaces!!



Can anyone please help me!!!

If you enclose the data and output samples in [ code ] tags, it's a lot easier on the audience:

srt col1 col2 col3 period    amount
1   a    aa    li1 1/1/2012  $11
2   a    aa    li1 1/2/2012  $12
3   a    aa    li3 ...
4   a    aa    li4 ...
5   c    dd    li5 ...
6   c    dd    li6 ...
7   d    ee    li7 ...
8   e    ff    li8 ...

srt col1 col2 col3  1/1/2012 1/2/2012 
1   a    aa    li1    $11     $12
2              li2    ...
3              li3    ...
4   c    dd    li5    ...
5              li6    ...
6   d    ee    li7
7   e    ff    li8   ...

As to your question: Try this
-- COMPUTE the line number
-- Use STYLE, SEQUENCE to promote the column from last to first position:

TABLE FILE CAR
WRITE SALES
BY COUNTRY
BY CAR
ACROSS SEATS
COMPUTE SEQ/I5=SEQ+1; AS 'srt'
ON TABLE SET STYLE * 
TYPE=REPORT, COLUMN=SEQ, SEQUENCE=1, $
ENDSTYLE
END

Thanks very much for the reply.
And did learn something new with the "SEQUENCE" keyword. However what you suggested will not work for me and partly its due to how I explained my issue.
Attached is a picture of data with the issue.
http://imageshack.us/photo/my-images/24/issue1.png/

And this is how the code looks,

 TABLE FILE HOLD1
SUM
     'newamount' AS ''
BY Srt
BY InqSection 
BY InqClass AS ''
BY InqDetail 
BY InqItem 
ACROSS  newperiod2
 

So using your approach does not work for me since "Inqsection" or Col1 (from first post) will not be sorted in alpha order. Thats where SRT play a critial role. Infact SRT eventually becomes a hidden column, but for example purpose I am displaying it.

So I would want to show my data as

  
InqSection  InqClass      InqDetail  InqItem ...
Revenue     Gross Sales   GAS        LI1
                                     LI2
                                     LI3

             Royalty     GAS         LI1
                                     LI2

What i almost need is a way for WF to use "SRT" to sort overall data interally but when displaying ignore it and continue from InqSection onwards!!

Hope its making sense!!

quote:

What i almost need is a way for WF to use "SRT" to sort overall data interally but when displaying ignore it and continue from InqSection onwards

In my sample code, SALES plays the role of your Amount measure, and COUNTRY & CAR play the role of your grouping key values (InqSection, InqClass, InqDetail, InqItem).

I fail to see how the original setof Srt values of the several observations (observations with matching values of the grouping keys, but for different periods) that map into a row of output would play any role whatsoever in the sort order of the output rows. Can you explain what's wrong with

TABLE FILE HOLD1
SUM
     'newamount' AS ''
-* 
BY InqSection 
BY InqClass AS ''
BY InqDetail 
BY InqItem 
ACROSS  newperiod2
...

?

I am a bit confused as well based on your example and how you said you wanted the final report to look j.gross's example is how you requested.

True the SRT column is changing values but your example for your output shows the same for when the LI1 InqItem lines go from two lines to one line the Li2's SRT value changes from 3 to 2.

Wanting all of the SRT values when you sum will not work unless you do a multi verb request but could you explain a bit more for why you need the SRT to use when placing rows in a particular order?

From your second post the reason why the InqSection isn't sorting in alphabetical order is because you are first sorting by SRT which should not be a BY field if you want to sum lines together.

Thanks guys for really helping me out here.
To make it real simple, I need to hide the repeating values under Inqsection, inqclass, inqdetail. However I feel the SRT column is hindering the repeating values to hide due to how SUM and BY work.

If I don't use the SRT column, and for example do a "BY InqSection" (as my first sort order instead of BY srt) then I lose the expected and required ordering of item and instead items are sorted by InqSection. So sorting by Inqsection will display Exepense section followed by Revenue section...which isn't the order I am needing.

You'd have to play around a bit with the following code to make it work in your environment. Here's an attempt:

TABLE FILE FIXFILE
SUM   NEWAMOUNT
BY    SRT
BY    INQSECTION
BY    INQCLASS
BY    INQDETAIL
BY    INQITEM
ACROSS PERIOD
ON TABLE SET ASNAMES ON
ON TABLE HOLD FORMAT ALPHA
END

DEFINE FILE HOLD
SECTION/A11 = IF INQSECTION EQ LAST INQSECTION THEN ' ' ELSE INQSECTION;
CLASS  /A15 = IF INQCLASS   EQ LAST INQCLASS   THEN ' ' ELSE INQCLASS;
END

TABLE FILE HOLD
PRINT SRT        AS 'Srt'
      SECTION    AS 'InqSection'
      CLASS      AS ''
      INQDETAIL  AS 'InqDetail'
      INQITEM    AS 'InqItem'
      NEW112011  AS 'Nov 11'
      NEW122011  AS 'Dec 11'
END

I have created a file called 'fixfile' that holds the data in an unsorted way. So the first I have to do is to sort the data in the desired way, while also doing the across, hold it and then just do print with using defines to blank out repeating values you do not wish to see.
Hope this sets you on your way...

Do you need to do this in one pass?

With two passes you could do something like:

TABLE FILE file
SUM
AMOUNT
COMPUTE SORT1/I9=IF COL1 EQ LAST COL1 THEN LAST SORT1 ELSE SORT1+1;
COMPUTE SORT2/I9=IF COL2 EQ LAST COL2 THEN LAST SORT2 ELSE SORT2+1;
COMPUTE SORT3/I9=IF COL3 EQ LAST COL3 THEN LAST SORT3 ELSE SORT3+1;
BY SRT
BY COL1
BY COL2
BY COL3
BY PERIOD
ON TABLE HOLD
END

TABLE FILE HOLD
SUM
AMOUNT
BY SORT1 NOPRINT
BY COL1
BY SORT2 NOPRINT
BY COL2
BY SORT3 NOPRINT
BY COL3
BY SRT NOPRINT
ACROSS PERIOD
END

quote:

...sorting by Inqsection will display Exepense section followed by Revenue section...which isn't the order I am needing.

If that's your only problem, you can impose a different order: compute the desired ordering of Inqsection values (as numeric values that map 1-1 to the alpha values), and make it a non-printing sort key:

TABLE FILE HOLD1
SUM
     'newamount' AS ''
     AND COMPUTE order/I1=DECODE Inqsection('Revenue' 1, 'Expense' 2, ..., ELSE 99); NOPRINT
BY TOTAL order NOPRINT 
BY InqSection 
BY InqClass AS ''
BY InqDetail 
BY InqItem 
ACROSS  newperiod2
... 
END

Or use the ROWS option to specify the required order:

BY InqSection 
 ROWS
  'Revenue' 
 OVER
  'Expense' 
 ...

I'm a bit confused, but if I follow you correctly in your data example,

  
srt col1 col2 col3 period    amount
1   a    aa    li1 1/1/2012  $11
2   a    aa    li1 1/2/2012  $12

these two items really need to have the same value for srt you need to do something similar to the below define:

  
DEFINE FILE WHATEVER
SRTFLD/A1 = IF ((COL1 EQ 'A') AND (COL2 EQ 'AA')) THEN '1' ELSE
IF ((COL1 EQ 'C') AND (COL2 EQ 'DD')) THEN '2' ELSE
IF ((COL1 EQ 'D') AND (COL2 EQ 'EE')) THEN '3' ELSE etc...;
END

Then in table request do

BY SRTFLD NOPRINT 
BY InqSection 
BY InqClass AS ''
BY InqDetail 
BY InqItem 
ACROSS PERIOD

Hope this helps...

Everyone thanks for providing your expert opinions and advise. I did one thing correctly confuse everybody, i suppose :-) Nonetheless I believe I have my solution by taking ideas from you'll posts and from ibi support. So this is it.

SUM
COMPUTE test/A100 = IF InqSection EQ LAST InqSection AND InqClass THEN '' ELSE IF InqClass; AS ''
COMPUTE test2/A100 =IF InqClass EQ LAST InqClass THEN '' ELSE InqClass; AS ''
COMPUTE test3/A100 =IF InqDetail EQ LAST InqDetail THEN '' ELSE InqDetail; AS ''
BY Srt
SUM
'newamount' AS ''
BY Srt NOPRINT
BY InqSection NOPRINT
BY InqClass NOPRINT
BY InqDetail NOPRINT
BY cInqItem AS ''
ACROSS newperiod2 AS ''

Regards

RD

quote:

COMPUTE test/A100 = IF InqSection EQ LAST InqSection AND InqClass THEN '' ELSE IF InqClass; AS ''

Huh? That's not a valid COMPUTE.


Back to the issue -- please explain how the Srt values are assigned in the first place?

Is Srt unique (as I think we all assumed) -- if so you'll never get more than one of the ACROSS columns populated in any given row, if "BY Srt" is the high-order sort of the report.

On the other hand, if Srt is assigned *with duplicates*, based on section+class+detail but not period, so as to map the details into the row where they belong, then I don't know what was your problem, in the first place, that this TABLE code solves.

Please elucidate.