Hi,

I have one Monthly report,based on year month displayed in across. Every time no, of months is changed based on year you selected. Suppose you selected current year then you have month in across upto October.

We also have column total in this report,But after column total requirement is like we need to add one more cumulative field, so i am not able to do find out any solution for this cumulative total, so please help me...

Below is the example of my report..Upto column total i have done my report, only problem with this cumulative total.

Site Jan Feb Mar Apr May Jun
A 100 200 300 400 500 600
B 100 100 200 300 200 400
C 500 200 100 500 200 400
--------------------------------------------
Total 700 500 600 1200 900 1400
*** Total 700 1200 1800 3000 3900 5300

Goldy,

Use a matrix report aka FML and add a recap for your extra, cumulative, total row.

Ok, so I just know you're going to ask for an example so here it is for everyone. It is a sample that I use to provide an insight into how to write flexible FML for any of my Clients.

-DEFAULT &Column = 'ST'
-DEFAULT &Year = 1996
-* --------------------------
-* Firstly extract in comma format all column values
-* --------------------------
TABLE FILE GGSALES
SUM &Column
    COMPUTE COMMA/A2 = ', ';
BY &Column NOPRINT
ON TABLE SAVE AS COLUMNS FORMAT ALPHA
END
-RUN
-? &
-SET &Cnt = 0;
-SET &Iter = &LINES;
-REPEAT :Loop1 &Iter TIMES;
-READ COLUMNS, &Column&Cnt.EVAL
-SET &Cnt = &Cnt + 1;
-:Loop1
-* --------------------------
-* Now report the data
-* --------------------------
DEFINE FILE GGSALES
  YEAR/YY = DATE;
  MONTH/tM = DATE;
END
TABLE FILE GGSALES
   SUM DOLLARS
ACROSS MONTH AS ''
WHERE YEAR EQ &Year
FOR &Column
-* --------------------------
-* Here we repeat the FOR statements as many times as there are column values
-* We also build the RECAP for the Total line
-* --------------------------
-SET &Cnt = 0;
-SET &TotalLine = '';
-REPEAT :Loop2 &Iter TIMES;
'&Column&Cnt.EVAL'   LABEL LABEL&Cnt.EVAL OVER
-SET &TotalLine = IF &Cnt EQ 0 THEN 'LABEL&Cnt.EVAL' ELSE &TotalLine | ' + LABEL&Cnt.EVAL';
-SET &Cnt = &Cnt + 1;
-:Loop2
RECAP YTOTAL = &TotalLine; AS 'Total' OVER
RECAP YCUMUL = YTOTAL ; AS 'Rolling Total' OVER
RECAP YCUMUL(2)  = YCUMUL(1)  + YTOTAL(2)  ; OVER
RECAP YCUMUL(3)  = YCUMUL(2)  + YTOTAL(3)  ; OVER
RECAP YCUMUL(4)  = YCUMUL(3)  + YTOTAL(4)  ; OVER
RECAP YCUMUL(5)  = YCUMUL(4)  + YTOTAL(5)  ; OVER
RECAP YCUMUL(6)  = YCUMUL(5)  + YTOTAL(6)  ; OVER
RECAP YCUMUL(7)  = YCUMUL(6)  + YTOTAL(7)  ; OVER
RECAP YCUMUL(8)  = YCUMUL(7)  + YTOTAL(8)  ; OVER
RECAP YCUMUL(9)  = YCUMUL(8)  + YTOTAL(9)  ; OVER
RECAP YCUMUL(10) = YCUMUL(9)  + YTOTAL(10) ; OVER
RECAP YCUMUL(11) = YCUMUL(10) + YTOTAL(11) ; OVER
RECAP YCUMUL(12) = YCUMUL(11) + YTOTAL(12) ; 
ON TABLE SET HTMLCSS ON
END

The only item you need to change to see the flexibility is the variable &Column. Chnage it to CATEGORY and see it still work - very useful if you want to cut a years data by different columns.

I know the RECAP statements for YCULUM are a bit lengthy but I've never found FML to behave properly when using offsets (e.g. YCUMUL(1,*,1) = YCUMUL(*-1)+YTOTAL(*); etc.) so I prefer to control it rather than have it fail to calculate my recaps.

Enjoy

T

This message has been edited. Last edited by: <Maryellen>,

Hi Tony,

Thanks for reply.....

Your code is working with my report but still i have a one problem and i am not able to solve it.
In cumulative total,first 7 of value is double then column-total value and last 3 value is as it is.

Please help me..

Thanks,
Goldy

Hi Goldy,

Here's another example that might work for you.

-*
-* HOLD DOLLARS BY MONTH
-*
DEFINE FILE GGSALES
YEAR/YY = DATE;
MONTH/tM = DATE;
M_DOL/I8 = DOLLARS;
Y_DOL/I8 = DOLLARS;
END
TABLE FILE GGSALES
SUM
Y_DOL
BY ST
BY YEAR
SUM M_DOL
BY ST
BY YEAR
BY MONTH
WHERE YEAR EQ 1996
ON TABLE HOLD AS HOLD1
END
-*
-* ADD CUMULATIVE DOLLARS
-*
DEFINE FILE HOLD1
C_DOL/I8 = IF ( (ST EQ LAST ST) AND (YEAR EQ LAST YEAR) ) THEN C_DOL + M_DOL ELSE M_DOL;
END
TABLE FILE HOLD1
PRINT
M_DOL
C_DOL
BY ST
BY YEAR
BY Y_DOL
BY MONTH
ON TABLE HOLD AS HOLD2
END
-*
-* GENERATE REPORT
-*
DEFINE FILE HOLD2
M_JAN/I8S = IF MONTH EQ 1 THEN M_DOL ELSE 0;
M_FEB/I8S = IF MONTH EQ 2 THEN M_DOL ELSE 0;
M_MAR/I8S = IF MONTH EQ 3 THEN M_DOL ELSE 0;
M_APR/I8S = IF MONTH EQ 4 THEN M_DOL ELSE 0;
M_MAY/I8S = IF MONTH EQ 5 THEN M_DOL ELSE 0;
M_JUN/I8S = IF MONTH EQ 6 THEN M_DOL ELSE 0;
M_JUL/I8S = IF MONTH EQ 7 THEN M_DOL ELSE 0;
M_AUG/I8S = IF MONTH EQ 8 THEN M_DOL ELSE 0;
M_SEP/I8S = IF MONTH EQ 9 THEN M_DOL ELSE 0;
M_OCT/I8S = IF MONTH EQ 10 THEN M_DOL ELSE 0;
M_NOV/I8S = IF MONTH EQ 11 THEN M_DOL ELSE 0;
M_DEC/I8S = IF MONTH EQ 12 THEN M_DOL ELSE 0;
M_TOT/I8S = M_DOL;
-*
C_JAN/I8S = IF MONTH EQ 1 THEN C_DOL ELSE 0;
C_FEB/I8S = IF MONTH EQ 2 THEN C_DOL ELSE 0;
C_MAR/I8S = IF MONTH EQ 3 THEN C_DOL ELSE 0;
C_APR/I8S = IF MONTH EQ 4 THEN C_DOL ELSE 0;
C_MAY/I8S = IF MONTH EQ 5 THEN C_DOL ELSE 0;
C_JUN/I8S = IF MONTH EQ 6 THEN C_DOL ELSE 0;
C_JUL/I8S = IF MONTH EQ 7 THEN C_DOL ELSE 0;
C_AUG/I8S = IF MONTH EQ 8 THEN C_DOL ELSE 0;
C_SEP/I8S = IF MONTH EQ 9 THEN C_DOL ELSE 0;
C_OCT/I8S = IF MONTH EQ 10 THEN C_DOL ELSE 0;
C_NOV/I8S = IF MONTH EQ 11 THEN C_DOL ELSE 0;
C_DEC/I8S = IF MONTH EQ 12 THEN C_DOL ELSE 0;
END
TABLE FILE HOLD2
SUM
M_JAN AS ' JANUARY'
M_FEB AS ' FEBRUARY'
M_MAR AS ' MARCH'
M_APR AS ' APRIL'
M_MAY AS ' MAY'
M_JUN AS ' JUNE'
M_JUL AS ' JULY'
M_AUG AS ' AUGUST'
M_SEP AS 'SEPTEMBER'
M_OCT AS ' OCTOBER'
M_NOV AS ' NOVEMBER'
M_DEC AS ' DECEMBER'
M_TOT AS ' TOTAL'
-*
C_JAN NOPRINT
C_FEB NOPRINT
C_MAR NOPRINT
C_APR NOPRINT
C_MAY NOPRINT
C_JUN NOPRINT
C_JUL NOPRINT
C_AUG NOPRINT
C_SEP NOPRINT
C_OCT NOPRINT
C_NOV NOPRINT
C_DEC NOPRINT
BY YEAR
BY ST
ON YEAR SUBFOOT
" "
"TOTAL <ST.M_JAN <ST.M_FEB <ST.M_MAR <ST.M_APR <ST.M_MAY <ST.M_JUN <0X
<ST.M_JUL <ST.M_AUG <ST.M_SEP <ST.M_OCT <ST.M_NOV <ST.M_DEC <ST.M_TOT"
" "
"CUMUL <ST.C_JAN <ST.C_FEB <ST.C_MAR <ST.C_APR <ST.C_MAY <ST.C_JUN <0X
<ST.C_JUL <ST.C_AUG <ST.C_SEP <ST.C_OCT <ST.C_NOV <ST.C_DEC"
" "
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT PDF
ON TABLE SET STYLE *
TYPE=REPORT,SIZE=8,SQUEEZE=ON,ORIENTATION=LANDSCAPE,$
-*
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=01, POSITION=M_JAN,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=02, POSITION=M_FEB,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=03, POSITION=M_MAR,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=04, POSITION=M_APR,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=05, POSITION=M_MAY,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=06, POSITION=M_JUN,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=07, POSITION=M_JUL,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=08, POSITION=M_AUG,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=09, POSITION=M_SEP,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=10, POSITION=M_OCT,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=11, POSITION=M_NOV,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=12, POSITION=M_DEC,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=13, POSITION=M_TOT,$
-*
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=01, POSITION=M_JAN,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=02, POSITION=M_FEB,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=03, POSITION=M_MAR,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=04, POSITION=M_APR,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=05, POSITION=M_MAY,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=06, POSITION=M_JUN,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=07, POSITION=M_JUL,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=08, POSITION=M_AUG,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=09, POSITION=M_SEP,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=10, POSITION=M_OCT,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=11, POSITION=M_NOV,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=12, POSITION=M_DEC,$
ENDSTYLE
END

Jim

Hi Jim,

Thanks for the Reply.......

Your code is working nicely, But I have one bookings report, for that i have one year dropdown box, user can select any of the year and based on that year i have months means suppose user select 2006 i have 4-5 months, if 2007 then 2 months so every time no. of months is changed.
Your code is based on all the 12 months..So Problem.....

Thanks,
Goldy

Goldy,


I had a quick look at this and Jim's option can be modified as follows to fit what I think you're looking for : (For my comments, see KL....)

 

DEFINE FILE GGSALES
YEAR/YY = DATE;
MONTH/tM = DATE;
-* KL....Add a regular month to the define. (MTH)MTH/M    = DATE; 
M_DOL/I8 = DOLLARS;
Y_DOL/I8 = DOLLARS;
END
TABLE FILE GGSALES
SUM
Y_DOL
BY ST
BY YEAR
SUM M_DOL
BY ST
BY YEAR
BY MONTH
-* KL....Add the MTH to the query
BY MTH
WHERE YEAR EQ 1996
ON TABLE HOLD AS HOLD1
END

-* KL....
-* Determine the max month in the query.  Store
-* this in the parameter &MTH.

TABLE FILE HOLD1
SUM MAX.MTH
ON TABLE SAVE AS MTHDATA 
END
-RUN
-SET &MTH = ' ';
-READ MTHDATA &MTH.A2.
-TYPE MAX MONTH IS &MTH
-*
-* ADD CUMULATIVE DOLLARS
-*
DEFINE FILE HOLD1
C_DOL/I8 = IF ( (ST EQ LAST ST) AND (YEAR EQ LAST YEAR) ) THEN C_DOL + M_DOL ELSE M_DOL;
END
TABLE FILE HOLD1
PRINT
M_DOL
C_DOL
BY ST
BY YEAR
BY Y_DOL
BY MONTH
ON TABLE HOLD AS HOLD2
END
-*
-* GENERATE REPORT
-*
DEFINE FILE HOLD2
M_JAN/I8S = IF MONTH EQ 1 THEN M_DOL ELSE 0;
M_FEB/I8S = IF MONTH EQ 2 THEN M_DOL ELSE 0;
M_MAR/I8S = IF MONTH EQ 3 THEN M_DOL ELSE 0;
M_APR/I8S = IF MONTH EQ 4 THEN M_DOL ELSE 0;
M_MAY/I8S = IF MONTH EQ 5 THEN M_DOL ELSE 0;
M_JUN/I8S = IF MONTH EQ 6 THEN M_DOL ELSE 0;
M_JUL/I8S = IF MONTH EQ 7 THEN M_DOL ELSE 0;
M_AUG/I8S = IF MONTH EQ 8 THEN M_DOL ELSE 0;
M_SEP/I8S = IF MONTH EQ 9 THEN M_DOL ELSE 0;
M_OCT/I8S = IF MONTH EQ 10 THEN M_DOL ELSE 0;
M_NOV/I8S = IF MONTH EQ 11 THEN M_DOL ELSE 0;
M_DEC/I8S = IF MONTH EQ 12 THEN M_DOL ELSE 0;
M_TOT/I8S = M_DOL;
-*
C_JAN/I8S = IF MONTH EQ 1 THEN C_DOL ELSE 0;
C_FEB/I8S = IF MONTH EQ 2 THEN C_DOL ELSE 0;
C_MAR/I8S = IF MONTH EQ 3 THEN C_DOL ELSE 0;
C_APR/I8S = IF MONTH EQ 4 THEN C_DOL ELSE 0;
C_MAY/I8S = IF MONTH EQ 5 THEN C_DOL ELSE 0;
C_JUN/I8S = IF MONTH EQ 6 THEN C_DOL ELSE 0;
C_JUL/I8S = IF MONTH EQ 7 THEN C_DOL ELSE 0;
C_AUG/I8S = IF MONTH EQ 8 THEN C_DOL ELSE 0;
C_SEP/I8S = IF MONTH EQ 9 THEN C_DOL ELSE 0;
C_OCT/I8S = IF MONTH EQ 10 THEN C_DOL ELSE 0;
C_NOV/I8S = IF MONTH EQ 11 THEN C_DOL ELSE 0;
C_DEC/I8S = IF MONTH EQ 12 THEN C_DOL ELSE 0;
END
-*
-* KL....
-* Add -IF to check to see if the maximum month
-* has been achieved.  If so, then skip over the
-* display of the remaining columns.
-*
TABLE FILE HOLD2
SUM
M_JAN AS ' JANUARY'
-IF &MTH EQ '01' THEN GOTO :ENDMTH ;
M_FEB AS ' FEBRUARY'
-IF &MTH EQ '02' THEN GOTO :ENDMTH ;
M_MAR AS ' MARCH'
-IF &MTH EQ '03' THEN GOTO :ENDMTH ;
M_APR AS ' APRIL'
-IF &MTH EQ '04' THEN GOTO :ENDMTH ;
M_MAY AS ' MAY'
-IF &MTH EQ '05' THEN GOTO :ENDMTH ;
M_JUN AS ' JUNE'
-IF &MTH EQ '06' THEN GOTO :ENDMTH ;
M_JUL AS ' JULY'
-IF &MTH EQ '07' THEN GOTO :ENDMTH ;
M_AUG AS ' AUGUST'
-IF &MTH EQ '08' THEN GOTO :ENDMTH ;
M_SEP AS 'SEPTEMBER'
-IF &MTH EQ '09' THEN GOTO :ENDMTH ;
M_OCT AS ' OCTOBER'
-IF &MTH EQ '10' THEN GOTO :ENDMTH ;
M_NOV AS ' NOVEMBER'
-IF &MTH EQ '11' THEN GOTO :ENDMTH ;
M_DEC AS ' DECEMBER'
-:ENDMTH
M_TOT AS ' TOTAL'
-*
C_JAN NOPRINT
C_FEB NOPRINT
C_MAR NOPRINT
C_APR NOPRINT
C_MAY NOPRINT
C_JUN NOPRINT
C_JUL NOPRINT
C_AUG NOPRINT
C_SEP NOPRINT
C_OCT NOPRINT
C_NOV NOPRINT
C_DEC NOPRINT
BY YEAR
BY ST
ON YEAR SUBFOOT
" "
"TOTAL " "
"CUMUL " "
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT PDF
ON TABLE SET STYLE *
TYPE=REPORT,SIZE=8,SQUEEZE=ON,ORIENTATION=LANDSCAPE,$
-*
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=01, POSITION=M_JAN,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=02, POSITION=M_FEB,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=03, POSITION=M_MAR,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=04, POSITION=M_APR,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=05, POSITION=M_MAY,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=06, POSITION=M_JUN,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=07, POSITION=M_JUL,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=08, POSITION=M_AUG,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=09, POSITION=M_SEP,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=10, POSITION=M_OCT,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=11, POSITION=M_NOV,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=12, POSITION=M_DEC,$
TYPE=SUBFOOT, LINE=2, OBJECT=FIELD, ITEM=13, POSITION=M_TOT,$
-*
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=01, POSITION=M_JAN,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=02, POSITION=M_FEB,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=03, POSITION=M_MAR,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=04, POSITION=M_APR,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=05, POSITION=M_MAY,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=06, POSITION=M_JUN,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=07, POSITION=M_JUL,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=08, POSITION=M_AUG,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=09, POSITION=M_SEP,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=10, POSITION=M_OCT,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=11, POSITION=M_NOV,$
TYPE=SUBFOOT, LINE=4, OBJECT=FIELD, ITEM=12, POSITION=M_DEC,$
ENDSTYLE
END

 

I tried it by running the report for September only and it worked fine.

Ken

This message has been edited. Last edited by: <Maryellen>,

Hi,

Thanks to All for replying...

I solved my problem for that I followed Jim code and used some If condition in between code. It's a lengthy code but it works perfectly as per my requirement.

Thanks,
Goldy

Goldy,

That's why I suggested FML as an option as the number of months in the data is immaterial to the resultant report - it only displays what is there - no (possibly) complex IF THEN ELSE logic in DM. The resultant code "should" be more concise and easy to maintain.

Glad you got it working though!