Hi,

how can we get the below report

Dept Div PO Cost
111 11 1 10
111 11 2 20
111 11 3 30

Total for 11 Div is 60

111 22 4 30
111 22 5 20
111 22 6 10

Total for 22 Div is 60

222 33 7 10
222 33 8 20
222 33 9 30

Total for 33 Div is 60

222 44 10 30
222 44 11 20
222 44 12 10

Total for 44 div is 60


Total for 111 dept is 120
Total for 222 dept is 120

The Grand total is 240


Thanking in advance

Navaneeth

Ginny,
He wants to see the subtotals together: McGuyver to the rescue!

Navaneeth,

The McGuyver technique allows, among other nice things, to multiply your data and thus create different ways to subtotal and display.
Using your question, here is a possible solution using the GGSALES file.

  
-* File Navaneeth1.fex
SET BYDISPLAY=ON
JOIN BLANK WITH ST IN GGSALES TO BLANK IN FSEQ AS M
DEFINE FILE GGSALES
BLANK/A1 WITH ST = ' ';
XCATEGORY/A12=IF COUNTER EQ 3 THEN 'Grand Total' ELSE CATEGORY;
REGION/A12=IF COUNTER GT 1 THEN ' ' ELSE REGION;
ST/A2=IF COUNTER GT 1 THEN ' ' ELSE ST;
END
TABLE FILE GGSALES
SUM
UNITS DOLLARS
BY COUNTER NOPRINT
BY XCATEGORY AS CATEGORY
BY REGION
BY ST
ON REGION SUBTOTAL AS 'Total'
WHEN COUNTER EQ 1
ON REGION SUBFOOT
" "
WHERE COUNTER LE 3
ON TABLE NOTOTAL
HEADING CENTER
"Units and Dollars by Categories and Regions"
END

The McGuyver file has already been posted on this forum but here it is again. The master:

  
 FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\FOCALPOINT\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$

and the data - notice that it must start with a space:

  
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER

Hi Daniel,

I am looking into your code and i am trying to understand it, but i have a question, you have written the code based on the fact that there will be there will be only 2 Dept ( i.e. based on my example).

How can we do it if we don’t know how many dept/ Div we have, i.e. how do we achieve this same functionality at runtime.


I did look into your code, but i guess it is way above my head, can you please explain me the code.....


Thanks & Regards,
Navaneeth

Navaneeth,
It seems that I must take a page out of Tony's book and be less elliptic.

So, here goes.

The McGuyver file (FSEQ) allows you to read each record in your source file more than once, the number of times depending on the field COUNTER. You will notice that I have used a screening condition WHERE COUNTER LE 3, meaning that I am using 3 copies of each record.
In your example you have 3 levels: Div, Dept and a grand total.
I have reproduced the same hierarchy using GGSALES where you have REGION, CATEGORY and Grand Total. I sort my report by COUNTER, meaning that I will have first all the CATEGORYs and REGIONs, then only the CATEGORYs and finally the Grand Total.
In my DEFINE, I re-create the fields ST, REGION and CATEGORY depending on the value of COUNTER in order to achieve what you want: REGION details with subtotals, then CATEGORY totals and finally the Grand Total.
So, no, I did not base my example on the fact that you show only 2 Dept but on the fact that you have a 2-level hierarchy.
If you have more than a 2-level hierarchy increase the value of COUNTER and redefine your fields accordingly.
Do you understand?