Hi All, I`m trying to add totals to the my across report, some how I`m not successful with the following example code. Any help will be appreciated.
SET BYDISPLAY = ON
DEFINE FILE CAR
DUMMY/A1 = ' ';
END
TABLE FILE CAR
SUM
DEALER_COST OVER
RETAIL_COST OVER
SALES
ACROSS CAR AS ''
BY DUMMY NOPRINT
ON DUMMY RECAP
DUM/A1 = DUMMY;
ACT/D20.2 =DEALER_COST+RETAIL_COST+ SALES;
ACT1/D20.2 = DEALER_COST+RETAIL_COST+ SALES;
ON DUMMY SUBFOOT
"Total:<ACT<ACT1"
WHERE COUNTRY EQ 'ENGLAND'
END
-EXIT
This message has been edited. Last edited by: <Emily McAllister>,
WebFocus-8/Windows/HTML, PDF, EXCEL
September 27, 2016, 03:30 PM
MartinY
Is that what you're looking for ?
SET BYDISPLAY = ON
DEFINE FILE CAR
DUMMY/A1 = ' ';
END
TABLE FILE CAR
SUM
DEALER_COST OVER
RETAIL_COST OVER
SALES
ACROSS CAR AS ''
ACROSS-TOTAL
BY DUMMY NOPRINT
ON DUMMY RECAP
DUM/A1 = DUMMY;
ACT/D20.2 =DEALER_COST+RETAIL_COST+ SALES;
ACT1/D20.2 = DEALER_COST+RETAIL_COST+ SALES;
ON DUMMY SUBFOOT
"Total:<ACT<ACT1"
WHERE COUNTRY EQ 'ENGLAND'
END
-EXIT
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
September 28, 2016, 06:50 AM
Krishna.edara
quote:
ACROSS CAR AS '' ACROSS-TOTAL
Hi Martin, Thanks for reply, I want column total, like total for Dealer cost, Retail cost, Sales, not row totals, Please let me know how can I achieve column totals while I`m doing across.
Thanks,
WebFocus-8/Windows/HTML, PDF, EXCEL
September 28, 2016, 08:38 AM
MartinY
With both totals : column and row
TABLE FILE CAR
SUM DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
ACROSS CAR AS ''
ACROSS-TOTAL
ON TABLE COLUMN-TOTAL AS 'Total '
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
September 28, 2016, 09:01 AM
Krishna.edara
Hi Martin, Thanks again for your reply, I'm using OVER for transposing the data
SET BYDISPLAY = ON
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES
BY COUNTRY NOPRINT
ACROSS CAR AS ''
ACROSS-TOTAL
WHERE COUNTRY IN('ENGLAND','JAPAN')
ON TABLE COLUMN-TOTAL AS 'Total '
END
not able to get column totals
Thanks,
WebFocus-8/Windows/HTML, PDF, EXCEL
September 28, 2016, 09:16 AM
MartinY
You do have the total, it's just that your data did not create a different total than the detailed data.
Here a sample:
SET BYDISPLAY = ON
DEFINE FILE CAR
NCOUNTRY /A10 = DECODE COUNTRY ('FRANCE' 'EAST' 'ITALY' 'EAST' 'ENGLAND' 'EAST' 'W GERMANY' 'WEST' 'JAPAN' 'WEST');
END
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES
BY COUNTRY
ACROSS NCOUNTRY AS ''
ACROSS-TOTAL
ON TABLE COLUMN-TOTAL AS 'Total '
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
September 28, 2016, 10:14 AM
Krishna.edara
Hi Martin, May be I`m not explaining properly, I need column totals not row totals, your code is not populating column totals, just an empty row.
SET BYDISPLAY = ON
DEFINE FILE CAR
END
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES
BY COUNTRY NOPRINT
ACROSS COUNTRY AS ''
ON TABLE COLUMN-TOTAL AS 'Total '
END
Thanks,
WebFocus-8/Windows/HTML, PDF, EXCEL
September 28, 2016, 10:30 AM
MartinY
My code display Column AND Row total. There is no empty row, the total is displayed "Under" the word "Total" because you are using a NOPRINT for the value (COUNTRY) which as the grand total for.
This one display only Column total and I've DEFINEd a field that generate a Column total which is the total of several values so you can see a different result than in your sample code:
SET BYDISPLAY = ON
DEFINE FILE CAR
NCOUNTRY /A10 = DECODE COUNTRY ('FRANCE' 'EAST' 'ITALY' 'EAST' 'ENGLAND' 'EAST' 'W GERMANY' 'WEST' 'JAPAN' 'WEST');
END
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES
BY COUNTRY
ACROSS NCOUNTRY AS ''
-*ACROSS-TOTAL
ON TABLE COLUMN-TOTAL AS 'Total '
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
September 28, 2016, 11:09 AM
Krishna.edara
Hi Martin, you are right, but I want grand total for EAST (in your case), but this is giving total by Dealer_cost, Retail_cost and Sales individually, hope you got my issue.
Thnaks,
WebFocus-8/Windows/HTML, PDF, EXCEL
September 28, 2016, 11:32 AM
MartinY
Because of the usage of OVER the Total bottom line is given for each SUM values
By adding a COMPUTEd field it gives you the total for the three SUM fields but it also be included for each break down (COUNTRY):
SET BYDISPLAY = ON
DEFINE FILE CAR
NCOUNTRY /A10 = DECODE COUNTRY ('FRANCE' 'EAST' 'ITALY' 'EAST' 'ENGLAND' 'EAST' 'W GERMANY' 'WEST' 'JAPAN' 'WEST');
END
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES OVER
COMPUTE GTOT/P10.2C = DEALER_COST + RETAIL_COST + SALES; AS 'Region Total'
BY COUNTRY
ACROSS NCOUNTRY AS ''
-*ACROSS-TOTAL
ON TABLE COLUMN-TOTAL AS 'Total '
END
Then following all sample code provided, I think that you may be able to play around and find out how to accomplish your request.
Otherwise remove the OVER and it will gives you another look which make a lot of sense according to me
SET BYDISPLAY = ON
DEFINE FILE CAR
NCOUNTRY /A10 = DECODE COUNTRY ('FRANCE' 'EAST' 'ITALY' 'EAST' 'ENGLAND' 'EAST' 'W GERMANY' 'WEST' 'JAPAN' 'WEST');
END
TABLE FILE CAR
SUM DEALER_COST
RETAIL_COST
SALES
COMPUTE GTOT/P10.2C = DEALER_COST + RETAIL_COST + SALES; AS 'Region Total'
BY COUNTRY
ACROSS NCOUNTRY AS ''
-*ACROSS-TOTAL
ON TABLE COLUMN-TOTAL AS 'Total '
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007