Hello,

Trying to generate report which looks like

CostGrp Cur_Bud Cur_Act Account
A 100 120 4550
A 110 110 4600
A 0 0 4700
B 100 100 4550
B 0 0 4600
B 0 0 4700
C 0 0 4550
C 0 0 4600
C 100 105 4700

My First Data COST set which contains CostGrp, Cur_Bud, Cur_Act and Account but Account values varies by each CostGrp constant like report want all account regardless this account exists in the CostGrp.

DATASET for Cost
CostGrp Cur_Bud Cur_Act Account
A 100 120 4550
A 110 110 4600
B 100 100 4550
C 100 105 4700

Other DataSet is Acct which contains all the Account but no CostGrp.

DataSet for Acct
Account
4550
4600
4700

For Report I want to show CostGrp and summarized Fields from Cost table which is summarized by each account from Acct table regardless the this account exists in the CostGrp or not want to show with value will be zero for that account.

Hope I tried to explain or if you have question let me know.

Here is my code

TABLE FILE ACCT
SUM
CP_ACTUAL AS 'CURRENT&COMMA.EVALACTUALS'
CP_BUDGET AS 'CURRENT&COMMA.EVALBUDGETS'
FOR ACCOUNT 'G21R1.INPT' GET CHILDREN ADD
BY COSTGRP
END

Thanks,
Viral.

This message has been edited. Last edited by: Kerry,

-* viral1.fex - Use FML to generate rows

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

FILEDEF COST DISK cost.txt
-RUN

-WRITE COST A 100 120 4550
-WRITE COST A 110 110 4600
-WRITE COST B 100 100 4550
-WRITE COST C 100 105 4700

FILEDEF MASTER DISK cost.mas
-RUN

-WRITE MASTER FILENAME=COST, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=COST, $
-WRITE MASTER FIELDNAME=CostGrp, FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=FILL1,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=Cur_Bud, FORMAT=I3, ACTUAL=A3, $
-WRITE MASTER FIELDNAME=FILL2,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=Cur_Act, FORMAT=I3, ACTUAL=A3, $
-WRITE MASTER FIELDNAME=FILL2,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=Account, FORMAT=A4, ACTUAL=A4, $
-RUN

FILEDEF ACCT DISK acct.txt
-RUN

-WRITE ACCT 4550
-WRITE ACCT 4600
-WRITE ACCT 4700

FILEDEF MASTER DISK acct.mas
-RUN

-WRITE MASTER FILENAME=ACCT, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=ACCT, $
-WRITE MASTER FIELDNAME=Account, FORMAT=A4, ACTUAL=A4, $
-RUN

TABLE FILE ACCT
PRINT
COMPUTE COUNTER/D9 = LAST COUNTER + 1 ; NOPRINT
COMPUTE FOR_CAR/A23 = IF COUNTER EQ 1 THEN '''' | Account | '''' ELSE 'OVER ''' | Account | '''' ;
BY Account NOPRINT
ON TABLE HOLD AS HACCT
END
-RUN

TABLE FILE COST
PRINT
Cur_Bud
Cur_Act
BY CostGrp
FOR Account
-INCLUDE HACCT
ON TABLE HOLD AS HCOST
END
-RUN

TABLE FILE HCOST
PRINT
CostGrp
Cur_Bud
Cur_Act
E02 AS 'Account'
END
-RUN

I'm using the ACCT table to generate the FML FOR commands. Unfortunately, in a hold file, the FOR column does not get a column-name, just an alias, E02 in this case.

Hi Francis,

Thanks for your suggestion,
Let me try and see it works out for me.

Thanks,
Viral.

Hi, tried generating a example using CAR file:

 
TABLE FILE CAR
BY COUNTRY 
ON TABLE HOLD AS FILE1
ON TABLE SET ASNAMES ON
END
-RUN
TABLE FILE CAR
BY CAR 
ON TABLE HOLD AS FILE2
ON TABLE SET ASNAMES ON
END
-RUN
TABLE FILE FILE2
PRINT
COMPUTE KEY/A1 = ' ' ;
CAR
ON TABLE HOLD AS FILE2IND FORMAT FOCUS INDEX KEY
END
-RUN
JOIN CLEAR *
JOIN KEY WITH COUNTRY IN FILE1 TO ALL KEY IN FILE2IND AS J1
DEFINE FILE FILE1
KEY/A1 WITH COUNTRY = ' ' ;
END
TABLE FILE CAR
SUM
SALES 
BY CAR
BY COUNTRY
WHERE RECORDLIMIT LE 6
ON TABLE HOLD AS FILE3
ON TABLE SET ASNAMES ON
END
-RUN

MATCH FILE FILE1
PRINT
*
BY CAR NOPRINT 
BY COUNTRY NOPRINT
RUN
FILE FILE3
SUM
SALES 
BY CAR NOPRINT 
BY COUNTRY NOPRINT 
AFTER MATCH HOLD AS FILE4 OLD-OR-NEW
ON TABLE SET ASNAMES ON
END
-RUN

TABLE FILE FILE4
PRINT 
CAR
COUNTRY
SALES
END
-RUN
-EXIT

Hi Francis,

I have tested and work as a I want. Great.

Thanks for your suggestion. I will try to implement in the my code.

Thanks,

Viral.