I have a report that contains 6 compound reports which do a column-total at the end of each one. Is there any way to get a grand total of all the column-totals from each report?
Here is an example
SET COMPOUND OPEN TABLE FILE CAR PRINT CAR DEALER_COST RETAIL_COST SALES BY COUNTRY WHERE COUNTRY EQ 'ENGLAND' ON TABLE COLUMN-TOTAL AS 'TOTAL' END SET COMPOUND CLOSE TABLE FILE CAR PRINT CAR DEALER_COST RETAIL_COST SALES BY COUNTRY WHERE COUNTRY EQ 'ITALY' ON TABLE COLUMN-TOTAL AS 'TOTAL' END
I want a row (at the bottom) that will show a grand total of column-total for England column-total for Italy
Thanks JVThis message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS 8105m Windows, All Outputs
December 18, 2013, 05:01 PM
Francis Mariani
If the data comes from the same table (as in your example) I would do something like this:
TABLE FILE CAR
SUM
COMPUTE LABEL1/A30 = 'COLUMN TOTAL FOR ' | COUNTRY;
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND', 'ITALY');
ON TABLE HOLD AS HTOTALS
END
SET COMPOUND OPEN
TABLE FILE CAR
PRINT
CAR
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
TABLE FILE CAR
PRINT
CAR
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY EQ 'ITALY'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
SET COMPOUND CLOSE
TABLE FILE HTOTALS
PRINT
LABEL1 AS ''
DEALER_COST
RETAIL_COST
SALES
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 18, 2013, 05:04 PM
Francis Mariani
If the data comes from different tables, I would first calculate the totals into two different hold files, then use MORE to concatenate them:
TABLE FILE CAR
SUM
COMPUTE LABEL1/A30 = 'COLUMN TOTAL FOR ' | COUNTRY;
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS HTOTAL1
END
TABLE FILE CAR
SUM
COMPUTE LABEL1/A30 = 'COLUMN TOTAL FOR ' | COUNTRY;
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY EQ 'ITALY';
ON TABLE HOLD AS HTOTAL2
END
SET COMPOUND OPEN
TABLE FILE CAR
PRINT
CAR
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
TABLE FILE CAR
PRINT
CAR
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
WHERE COUNTRY EQ 'ITALY'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
SET COMPOUND CLOSE
TABLE FILE HTOTAL1
PRINT
LABEL1 AS ''
DEALER_COST
RETAIL_COST
SALES
MORE
FILE HTOTAL2
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 19, 2013, 10:00 AM
Jveselka
Ok, Francis this helps, but the more I look at it I think all I need to do is put another report in the compound that prints a total for everything and only print the total column for that report.
How would I only print the Total Vol row for this?
TABLE FILE CAR PRINT * ON TABLE COLUMN-TOTAL AS 'TOTAL VOL'; END
WebFOCUS 8105m Windows, All Outputs
December 19, 2013, 10:25 AM
Francis Mariani
You should only specify the measures, and not use *
TABLE FILE CAR
SUM
COMPUTE LABEL1/A10 = 'TOTAL'; AS ''
DEALER_COST
RETAIL_COST
SALES
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 20, 2013, 10:07 AM
Jveselka
WOW! Francis that was easy enough!
Thanks for all your help......
JV
WebFOCUS 8105m Windows, All Outputs
December 20, 2013, 04:26 PM
Doug
And all along, until I completed reading the above post, I was expecting that "Fun with Compound reports" was going to be a bit of a dichotomy, or at least a point of frustration. I personally like the Compound Reports.