Dear All,

I'm trying to built a report defined by Age Group.

I replicated the codes for each of the Age Group and make the necessary changes.

When I run the report in Developer Studio Tool, I will get each Age Group result successfully.

However, when I run the report in Text Editor, the end result always stopped at Age Group 26 -35.

Below is the code snippet :

-* File Transaction_AgeVsMCC_V3.fex
-INCLUDE gen_def_env_vars
-SET &IMGURL = IF &WFFMT EQ 'HTML' THEN &HTMLIMGURL ELSE &PDFIMGURL

SET EMPTYREPORT = ON
JOIN
CUST_V3.CUST_V3.IPKIPN IN CUST_V3 TO MULTIPLE CARD_V3.CARD_V3.KTKIPN IN CARD_V3
TAG J0 AS J0
END
JOIN
J0.CARD_V3.KTKRTN IN CUST_V3 TO MULTIPLE PURCH_V3.PURCH_V3.TRKRTN IN PURCH_V3
TAG J1 AS J1
END
JOIN
FILE CUST_V3 AT J1.PURCH_V3.TRMCC TO MULTIPLE FILE MCC_V3
AT MCC_V3.MCC_V3.MCMCC TAG J2 AS J2
WHERE J2.MCC_V3.MCNETW EQ 'M' AND J1.PURCH_V3.TRMCC EQ J2.MCC_V3.MCMCC;
END
DEFINE FILE CUST_V3
DATE1/A10='&dateFrom';
DD1/A2=EDIT(DATE1,'99$$$$$$$$');
MM1/A2=EDIT(DATE1,'$$$99$$$$$');
YY1/A4=EDIT(DATE1,'$$$$$$9999');
DATE2/A10='&dateTo';
DD2/A2=EDIT(DATE2,'99$$$$$$$$');
MM2/A2=EDIT(DATE2,'$$$99$$$$$');
YY2/A4=EDIT(DATE2,'$$$$$$9999');
DATEFROM/YYMD=DATECVT(EDIT(DD1|MM1|YY1),'I8DMYY','I8YYMD');
DATETO/YYMD=DATECVT(EDIT(DD2|MM2|YY2),'I8DMYY','I8YYMD');
CURRDT/DMYY='&DMYY';
DOB/DMYY=HDATE(CUST_V3.IPFDAT, 'YYMD');
AGE/I5-=DATEDIF(DOB, CURRDT, 'Y');
BDAT/DMYY=HDATE(J1.PURCH_V3.TRBDAT, 'YYMD');
18_25/I7=IF AGE GE 18 AND AGE LE 25 THEN + 1 ELSE + 0;
26_35/I7=IF AGE GE 26 AND AGE LE 35 THEN + 1 ELSE + 0;
36_45/I7=IF AGE GE 36 AND AGE LE 45 THEN + 1 ELSE + 0;
46_55/I7=IF AGE GE 46 AND AGE LE 55 THEN + 1 ELSE + 0;
56_ABOVE/I7=IF AGE GE 56 THEN + 1 ELSE + 0;
END

SET COMPOUND=OPEN NOBREAK
-*AGE 18 - 25
TABLE FILE CUST_V3
SUM
'CUST_V3.CUST_V3.18_25/I9C' AS 'No. OF TRANSACTION'
BY 'J1.PURCH_V3.TRMCC' AS 'MCC CODE'
BY 'J2.MCC_V3.MCDESC' AS 'MERCHANT CATEGORY'
ACROSS 'J1.PURCH_V3.TRRKID' AS 'PRODUCT'
ON TABLE SUBHEAD
" "
" "
HEADING
"TRANSACTION BY MCC FROM <+0>&dateFrom<+0> <+0>TO<+0> <+0>&dateTo"
" "
"AGE VS MCC (18 - 25)"
" "
WHERE PURCH_V3.TRTRST EQ 0;
WHERE PURCH_V3.TRMCC NE '';
WHERE BDAT GE DATEFROM;
WHERE BDAT LE DATETO;
ON TABLE SET PAGE-NUM ON
ON TABLE SUMMARIZE AS 'TOTAL'
ON TABLE PCHOLD FORMAT &WFFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *



$
ENDSTYLE
END



I would very much appreciate it if anyone could help to debug it.

Thanks..JS

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

Do you need the "+" signs in the below code?

quote:

18_25/I7=IF AGE GE 18 AND AGE LE 25 THEN + 1 ELSE + 0;
26_35/I7=IF AGE GE 26 AND AGE LE 35 THEN + 1 ELSE + 0;
36_45/I7=IF AGE GE 36 AND AGE LE 45 THEN + 1 ELSE + 0;
46_55/I7=IF AGE GE 46 AND AGE LE 55 THEN + 1 ELSE + 0;
56_ABOVE/I7=IF AGE GE 56 THEN + 1 ELSE + 0;

Dear N.Selph,

I have removed the '+' sign for further testing and I still manage to get the same result. I guess the sign does not play a role here. Thanks...

However, I'm still not able to get the whole result.


Regards,
JS

Syntax error:

  
18_25/I7=IF AGE GE 18 AND AGE LE 25 THEN LAST 18_25 + 1 ELSE 0;
26_35/I7=IF AGE GE 26 AND AGE LE 35 THEN LAST 26_35 + 1 ELSE 0;
36_45/I7=IF AGE GE 36 AND AGE LE 45 THEN LAST 36_45 + 1 ELSE 0;
46_55/I7=IF AGE GE 46 AND AGE LE 55 THEN LAST 46_55 + 1 ELSE 0;
56_ABOVE/I7=IF AGE GE 56 THEN         LAST 56_ABOVE + 1 ELSE 0;

You show only the opening report in the compound-report series, for the first age group total, 18_25. Presumably that is followed by similar ones for the other four age-group sums that you define.

I suggest you grab all the necessary data in a single pass (summing a general counter by an age_group indicator into a hold file), and generate all the reports against the resulting hold file. At least that gives you a means to verify that all the data are accessible.

If the range of PRODUCT values (the set of ACROSS columns) to appear is constant over the age groups, you can avoid the compound report entirely:

JOIN ...
DEFINE FILE CUST_V3
. . .
 AGE_GROUP/A1=
IF AGE LT 18 THEN ' ' 
ELSE IF AGE LE 25 THEN 'A' 
ELSE IF AGE LT 35 THEN 'B' 
ELSE IF AGE LT 45 THEN 'C' 
ELSE IF AGE LT 55 THEN 'D' ELSE 'E';
 COUNTER/I9C with MCMCC=1;
. . .
END
TABLE FILE CUST_V3
SUM COUNTER
BY AGE_GROUP
BY TRMCC
BY MCDESC 
BY TRRKID
WHERE ...
ON TABLE HOLD
END

DEFINE FILE HOLD
 DEMOGRAPHIC/A20=DECODE AGE_GROUP (
A '18 - 25'
B '26 - 35'
C '36 - 45'
D '46 - 55'
E '56 and Above'
ELSE ' ');

END
TABLE FILE HOLD

HEADING
. . .
"AGE VS MCC <DEMOGRAPHIC"
. . .

SUM COUNTER AS 'NO OF TRANS'
BY AGE_GROUP NOPRINT PAGE-BREAK
BY . . .
ACROSS PRODUCT
WHERE AGE_GROUP NE ' ';
. . .
END

Since this is a boolean test, I would code it like this:

18_25/I7=AGE GE 18 AND AGE LE 25;
26_35/I7=AGE GE 26 AND AGE LE 35;
36_45/I7=AGE GE 36 AND AGE LE 45;
46_55/I7=AGE GE 46 AND AGE LE 55;
56_ABOVE/I7=AGE GE 56;

This is more efficient and WebFOCUS/FOCUS assigns a value of 1 when true and a value of 0 when false.

Dear All,

Thank you very much for sharing your ideas and codes with me. I truely appreciate all the assistance provided by you guys.


Thanks... JS