I am developing a report and i need to get the report output in the below format.

 

	        Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Product1 2016	24	19	17	26	32	26	31	29	25	30	27	16
product1 2017	19	23	24	21	24	24	22	24				
Percent Change	-20.8%	21.1%	41.2%	-19.2%	-25.0%	-7.7%	-29.0%	-17.2%				
Product2 2016	46	40	60	61	44	42	68	49	41	50	44	47
Product2 2017	58	39	22	41	42	34	40	34				
Percent Change	26.1%	-2.5%	-63.3%	-32.8%	-4.5%	-19.0%	-41.2%	-30.6%				
Product3 2016	264	172	227	246	217	234	276	302	277	241	254	255
product3 2017	228	173	223	197	201	217	256	251				
Percent Change	-13.6%	0.6%	-1.8%	-19.9%	-7.4%	-7.3%	-7.2%	-16.9%	
 

I am able to bring the output as below.

	        Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Product1 2016	24	19	17	26	32	26	31	29	25	30	27	16
product1 2017	19	23	24	21	24	24	22	24				

Product2 2016	46	40	60	61	44	42	68	49	41	50	44	47
Product2 2017	58	39	22	41	42	34	40	34				

Product3 2016	264	172	227	246	217	234	276	302	277	241	254	255
product3 2017	228	173	223	197	201	217	256	251				

ProductId is the first BY field and Year is the second BY field and The count of Sales is the third BY field across the months.
How can i get difference/percentage of difference.

This message has been edited. Last edited by: FP Mod Chuck,

This is one option, other such as MacGyver technique may also be applicable

DEFINE FILE GGSALES
YR /YY    = DATE;
MM /MONTH = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
    COMPUTE VAL/A15V = FPRINT(DOLLARS, 'I08', 'A15V');
BY CITY
BY YR
BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR);
BY MM
ON TABLE HOLD AS EXTDATA
END
-RUN

DEFINE FILE EXTDATA
SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0;
SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0;
END
TABLE FILE EXTDATA
SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/A15V   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change';
BY MM
ON TABLE HOLD AS PCTDATA
END
-RUN

TABLE FILE EXTDATA
SUM VAL AS ''
BY CITY AS ''
BY YR NOPRINT
BY ROWLABEL AS ''
ACROSS MM AS ''
MORE
FILE PCTDATA
END
-RUN

I was heading down this road also. But since you did such a good job I can only add this tiny improvement

 DEFINE FILE GGSALES
YR /YY    = DATE;
MONTH_NUM /MONTH = DATE;
MM/A4 = DECODE MONTH_NUM(01 Jan 02 Feb 03 Mar 04 Apr 05 Mar 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec);
END
TABLE FILE GGSALES
SUM DOLLARS
    COMPUTE VAL/A15V = FPRINT(DOLLARS, 'I08', 'A15V');
BY CITY
BY YR
BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR);
BY MM
BY MONTH_NUM
ON TABLE HOLD AS EXTDATA
END
-RUN

DEFINE FILE EXTDATA
SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0;
SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0;
END
TABLE FILE EXTDATA
SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/A15V   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change';
BY MONTH_NUM
BY MM
ON TABLE HOLD AS PCTDATA
END
-RUN

TABLE FILE EXTDATA
SUM VAL AS ''
BY CITY AS ''
BY YR NOPRINT
BY ROWLABEL AS ''
ACROSS MONTH_NUM NOPRINT
ACROSS MM AS ''
MORE
FILE PCTDATA
END
-RUN 

quote:

I was heading down this road also. But since you did such a good job I can only add this tiny improvement

...

MM/A4 = DECODE MONTH_NUM(01 Jan 02 Feb 03 Mar 04 Apr 05 Mar 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec);

I did not want to go that far, we need to left some work to do to our friend srajeevan, I'm not paid to do that, he/she is, since I've already made a lot of job free of charges...

good point, teach a person to fish....

Thanks MartinY and Don for the help.
It really helped me to think more on logic and on doing more focus coding

I am trying to get the sum of 2016 sales in the last row and in the next row sum of 2017 sales also and also Total column for the products.
What steps i should follow.?

  
       Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec            Total
Product1 2016	24	19	17	26	32	26	31	29	25	30	27	16      302
product1 2017	19	23	24	21	24	24	22	24		                        181		
Percent Change	-20.8%	21.1%	41.2%	-19.2%	-25.0%	-7.7%	-29.0%	-17.2%				
Product2 2016	46	40	60	61	44	42	68	49	41	50	44	47
Product2 2017	58	39	22	41	42	34	40	34				
Percent Change	26.1%	-2.5%	-63.3%	-32.8%	-4.5%	-19.0%	-41.2%	-30.6%				
Product3 2016	264	172	227	246	 217	234	276	302	277	241	254	255
product3 2017	228	173	223	197	201	217	256	251				
Percent Change	-13.6%	0.6%	-1.8%	-19.9%	-7.4%	-7.3%	-7.2%	-16.9%	
Total 2016 : 
Total 2017 :  

It would be helpful if i get some direction on this.

As for the row total (beside Dec) you can try with ACROSS ROW-TOTAL but it will also create a sum for the Percent row which is probably not what you want and since (from my above sample) all the data is now as characters (alpha) it cannot be summarized.

You could have the ACROSS-TOTAL working if you keep your data as numeric but in a same column (per example under Jan) the data must all have the same format (cannot mix D6 with D6.2C% as example).

When I have such "customized" layout I normally build each row individually as I did in my sample and have them all alpha :

1- the detailed one. The one Product Year rows where I also create my own columns : months one and the total one
2- the percent one with months column and could also have a total one where you will have recomputed the percentage
3- the year total

Then merge everything into one file and display/format. Since everything is alpha column and in the same format you can MORE as many files as you wish while you have the same number of column (field) in each row.

In this case and because you also have month data, to be sure that the month are displayed in proper order, also include a numeric month which will be hidden (NOPRINT) in the report and to have the rows also in the proper order you may need to add a numeric rowed.

Consider using FML as this is a potentially good fit.

The only thing that you should watch out for is the fact that you have a compound key (product and year).

Plenty of examples throughout the forum.


T

I edited the code following your suggestion and brought in Row total (and percentage change total as well even if i don't need it.Will have to figure out a way to remove that once i get bring in all rows and columns).
For the Column total(Total 2016 and Total 2017) i created two more hold files with the same column but it is getting calculated for each product.(coz i have BY PRODUCT TYPE )
I commented the BY product type line but then i get error.I think it wont join unless it has same fields and same BY fields.

  
DEFINE FILE GGSALES
YR /YY    = DATE;
MM /MONTH = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
    COMPUTE VAL/12.2 = FPRINT(DOLLARS, 'I08', 'A15V');
BY CITY
BY YR
BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR);
BY MM
ON TABLE HOLD AS EXTDATA
END
-RUN

DEFINE FILE EXTDATA
SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0;
END
TABLE FILE EXTDATA

    COMPUTE VAL/D12.2   = SALES1
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Total 2016';
BY MM
ON TABLE HOLD AS PCTDATA1
END
-RUN

DEFINE FILE EXTDATA
SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0;
END
TABLE FILE EXTDATA

    COMPUTE VAL/D12.2   = SALES2
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Total 2017';
BY MM
ON TABLE HOLD AS PCTDATA2
END
-RUN

DEFINE FILE EXTDATA
SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0;
SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0;
END
TABLE FILE EXTDATA
SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/D12.2   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change';
BY MM
ON TABLE HOLD AS PCTDATA
END
-RUN

TABLE FILE EXTDATA
SUM VAL AS ''
BY CITY AS ''
BY YR NOPRINT
BY ROWLABEL AS ''
ACROSS MM AS ''
ACROSS-TOTAL
MORE
FILE PCTDATA
END
-RUN

  
                Jan	Feb	Mar     Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec     Total
Product1 2016	24	19	17	26	32	26	31	29	25	30	27	16      302
product1 2017	19	23	24	21	24	24	22	24		                        181		
Percent Change	-20.8%	21.1%	41.2%	-19.2%	-25.0%	-7.7%	-29.0%	-17.2%					xxxx
Total 2016:      
Total 2017:
Product2 2016	46	40	60	61	44	42	68	49	41	50	44	47
Product2 2017	58	39	22	41	42	34	40	34				
Percent Change	26.1%	-2.5%	-63.3%	-32.8%	-4.5%	-19.0%	-41.2%	-30.6%				
Total 2016:
Total 2017:
Product3 2016	264	172	227	246	 217	234	276	302	277	241	254	255
product3 2017	228	173	223	197	201	217	256	251				
Percent Change	-13.6%	0.6%	-1.8%	-19.9%	-7.4%	-7.3%	-7.2%	-16.9%	
Total 2016 : 
Total 2017 : 

How can calculate for the entire year ( i think i should not sort by product here.Commenting that BY field is not helping and the join is not working as per my understanding.)

quote:

How can calculate for the entire year

As I previously made in my sample for Percentage row, you need to fake the other BY field when creating the total for the years.
So yes you need to have the same number/format of BY fields but you can assign them fake value.

As Tony suggested, FML may also be an option to look at.

Sorry i didnt understand that part where u asked me to fake BY fields in PCDATA1(where i calculate VAL for 2016).

In the HOLD file PCDATA1

  
TABLE FILE EXTDATA
SUM

	COMPUTE VAL2/D12.2 = SALES1;
-*BY TOTAL COMPUTE FILECLASSS = 9999;
BY LOWEST FILE_CLASS_DASHBD_DESC;
BY TOTAL COMPUTE Defyear/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Total Violent 2016';
-*BY TOTAL COMPUTE ROWLABEL1/A20V = 'Total Violent 2016';
BY Defmonth
ON TABLE HOLD AS PCDATA1
-*FORMAT FOCUS
END
-RUN

I tried giving different BY FIELDS instead of BY LOWEST FILE_CLASS_DASHBD_DESC.
But i am getting error only.

How can i remove that BY FIELD and still achieve the join?
Please bear with me if i am asking silly questions.

Hi MartinY,

I understand that while appending we shud have all fields in files involved in the same format/number.
For me here, column VAL in the HOLD file PCDATA1 should no be calculated with BY FIELD FILE_CLASS_DASHBD_DESC,but still i have to append with the other HOLD files in the fex.

I am trying for a solution for that.Sorry if was not clear in my previous posts.I have created reports only in GUI on webfocus that is why i have lack of knowledge in custom development.

Iunderstand that i have to fake BY fields in PCDATA1 too.But i am getting error when i change the BY FIELDS in PCDATA1.

There you go.

All the job is done for you, you just need to adapt to your own data.
The important thing is to have all HOLD files with same number of field, with same name and format.

DEFINE FILE GGSALES
YR        /YY    = DATE;
MONTH_NUM /MONTH = DATE;
MM_NO     /D2    = MONTH_NUM;
MM        /A5V   = DECODE MONTH_NUM(01 Jan 02 Feb 03 Mar 04 Apr 05 Mar 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec);
END
TABLE FILE GGSALES
SUM DOLLARS/P10C
BY CITY
BY YR
BY MM_NO
BY MM
ON TABLE HOLD AS EXTDATA
END
-RUN

TABLE FILE EXTDATA
SUM DOLLARS
    COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V');
BY TOTAL COMPUTE ROWID /I2 = 1;
BY CITY
BY YR
BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR);
BY MM_NO
BY MM
ON TABLE HOLD AS DETDATA
END
-RUN

TABLE FILE EXTDATA
SUM DOLLARS
    COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V');
BY TOTAL COMPUTE ROWID /I2 = 1;
BY CITY
BY YR
BY TOTAL COMPUTE ROWLABEL /A20V  = 'Year ' | EDIT(YR);
BY TOTAL COMPUTE MM_NO    /D2    = 99;
BY TOTAL COMPUTE MM       /A5V   = 'Total';
ON TABLE HOLD AS DETROWTOT
END
-RUN

DEFINE FILE EXTDATA
SALES1 /P10C = IF YR EQ 1996 THEN DOLLARS ELSE 0;
SALES2 /P10C = IF YR EQ 1997 THEN DOLLARS ELSE 0;
END
TABLE FILE EXTDATA
SUM SALES1
    SALES2
    COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/A15V   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY TOTAL COMPUTE ROWID /I2 = 1;
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change';
BY MM_NO
BY MM
ON TABLE HOLD AS PCTDATA
END
-RUN

TABLE FILE PCTDATA
SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/A15V   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY TOTAL COMPUTE ROWID /I2 = 1;
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL /A20V = 'Percent Change';
BY TOTAL COMPUTE MM_NO    /D2   = 99;
BY TOTAL COMPUTE MM       /A5V  = 'Total';
ON TABLE HOLD AS PCTROWTOT
END
-RUN

TABLE FILE EXTDATA
SUM DOLLARS
    COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V');
BY TOTAL COMPUTE ROWID /I2  = 10;
BY TOTAL COMPUTE CITY  /A20 = 'Total';
BY YR
BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR);
BY MM_NO
BY MM
ON TABLE HOLD AS YRTOT
END
-RUN

TABLE FILE YRTOT
SUM DOLLARS
    COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V');
BY TOTAL COMPUTE ROWID /I2  = 10;
BY TOTAL COMPUTE CITY  /A20 = 'Total';
BY YR
BY TOTAL COMPUTE ROWLABEL /A20V = 'Year ' | EDIT(YR);
BY TOTAL COMPUTE MM_NO    /D2   = 99;
BY TOTAL COMPUTE MM       /A5V  = 'Total';
ON TABLE HOLD AS YRROWTOT
END
-RUN

DEFINE FILE YRTOT
SALES1 /P10C = IF YR EQ 1996 THEN DOLLARS ELSE 0;
SALES2 /P10C = IF YR EQ 1997 THEN DOLLARS ELSE 0;
END
TABLE FILE YRTOT
SUM SALES1
    SALES2
    COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/A15V   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY TOTAL COMPUTE ROWID /I2  = 10;
BY TOTAL COMPUTE CITY  /A20 = 'Total';
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL /A20V = 'Percent Change';
BY MM_NO
BY MM
ON TABLE HOLD AS YRTOTPCTDATA
END
-RUN

TABLE FILE PCTDATA
SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
    COMPUTE VAL/A15V   = FPRINT(PCT, 'P8.2C%', 'A15V');
BY TOTAL COMPUTE ROWID /I2  = 10;
BY TOTAL COMPUTE CITY  /A20 = 'Total';
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL /A20V = 'Percent Change';
BY TOTAL COMPUTE MM_NO    /D2   = 99;
BY TOTAL COMPUTE MM       /A5V  = 'Total';
ON TABLE HOLD AS YRTOTPCTTOT
END
-RUN

TABLE FILE DETDATA
SUM VAL
BY ROWID
BY CITY
BY YR
BY ROWLABEL
BY MM_NO
BY MM
ON TABLE HOLD AS RPTDATA
MORE
FILE DETROWTOT
MORE
FILE PCTDATA
MORE
FILE PCTROWTOT
MORE
FILE YRTOT
MORE
FILE YRROWTOT
MORE
FILE YRTOTPCTDATA
MORE
FILE YRTOTPCTTOT
END
-RUN

TABLE FILE RPTDATA
SUM VAL      AS ''
BY ROWID     NOPRINT
BY CITY      AS ''
BY YR        NOPRINT
BY ROWLABEL  AS ''
ACROSS MM_NO NOPRINT
ACROSS MM    AS ''

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, JUSTIFY=RIGHT, $
TYPE=DATA, ACROSSCOLUMN=VAL, JUSTIFY=RIGHT, $
END
-RUN

Thanks MartinY.
The report worked.I have to analyse the code and understand for my learning purpose.

My pleasure to have helped you start becoming a Focus coder

Please edit your first post then add [SOLVED] at the beginning of the subject.
Do that to all your post when solved.
See Forum Guidelines for further forum rules and status.

maybe this will work for you:

COMPUTE DIFF/D12.2=FIELDNAME - LAST FIELDNAME;

Srajeevan, What was your final solution?

Dough,

Sorry i missed your question.
My final solution is what MartinY has posted.
Let me know if you want to see the code.