I posted some code a week or so ago. I was reporting BadDebt, Charges and a Percentage of the two trended across by month and subtotalling by Dept. The Percentage subtotals for each month were additive, not 'recomputed'. The forum helped me add the RECOMPUTE VERB and all was fine.

Now I added GRAND TOTALS. The final set of columns (totals for all months- bad Debt, Charges, %) is now summing the percentages again instead of 'recomputing' them on the Dept subtotal and for the final grand total row.

I assume I need another: RECOMPUTE...somewhere...


THANKS for your HELP!!!

TABLE FILE HOLDMERGE
SUM
HOLDMERGE.HOLDMERG.BADDEBT/P20C AS 'Bad Debt'
HOLDMERGE.HOLDMERG.CHGS/D12 AS 'Charges'
COMPUTE PERCENTAGE/D12.2% = ( HOLDMERGE.HOLDMERG.BADDEBT / HOLDMERGE.HOLDMERG.CHGS ) * 100; AS 'Percentage'
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.RPTBANBR
BY J0.TBLMSTR_BANBRBANAMEXREF.RPTBANAME
ACROSS LOWEST HOLDMERGE.HOLDMERG.FY
ACROSS LOWEST HOLDMERGE.HOLDMERG.PERIOD AS 'Period'

ON J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY RECOMPUTE
SUM. 'HOLDMERGE.HOLDMERG.BADDEBT'
SUM. 'HOLDMERGE.HOLDMERG.CHGS'
PERCENTAGE AS '*TOTAL'
WHERE ( HOLDMERGE.HOLDMERG.FY EQ &FY.Enter 4 Digit Fiscal year. ) AND ( J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY,TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY IN tblmstr_banbrbanamexref)).Select the desired Specialty/s. );
WHERE HOLDMERGE.HOLDMERG.PERIOD LE &PERIOD.Enter the Ending Period in the format: YYYYMM, Ex: thru July 2014: 201407.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K

dept subtotal % was summing. Some forum help pointed me to the RECOMPUTE verb and all was then fine.

Now I added Grand Totals. The grand total works fine by month but the Grand Total for ALL months is now additive and I am not sure how to 'recompute' it.

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

Instead of
ON TABLE COLUMN-TOTAL
use
ON TABLE SUMMARIZE

Tried the above...see code below...it had no effect.

To be specific, I get my months going across segmented into BadDebt / Charges / % under each month.

The report subtotals at DEPT. This all works fine EXCEPT for the LAST 3 columns of the report Total BadDebt / Charges/ %....on the Dept subtotal and on the last row (grand total), the percentages are added, not calculated. Again, entire report is fine except the subtotal lines for the LAST 3 columns as well as theGRAND total for the last 3 columns.

ON TABLE HOLD AS HOLDMERGE FORMAT ALPHA
MORE
FILE HOLDCHGS
END

JOIN
LEFT_OUTER HOLDMERGE.HOLDMERG.BANBR IN HOLDMERGE TO UNIQUE
TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXBANBR
IN TBLMSTR_BANBRBANAMEXREF TAG J0 AS J0
END
TABLE FILE HOLDMERGE
SUM
HOLDMERGE.HOLDMERG.BADDEBT/P20C AS 'Bad Debt'
HOLDMERGE.HOLDMERG.CHGS/D12 AS 'Charges'
COMPUTE PERCENTAGE/D12.2% = ( HOLDMERGE.HOLDMERG.BADDEBT / HOLDMERGE.HOLDMERG.CHGS ) * 100; AS 'Percentage'
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.RPTBANBR
BY J0.TBLMSTR_BANBRBANAMEXREF.RPTBANAME
ACROSS LOWEST HOLDMERGE.HOLDMERG.FY
ACROSS LOWEST HOLDMERGE.HOLDMERG.PERIOD AS 'Period'

ON J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY RECOMPUTE
SUM. 'HOLDMERGE.HOLDMERG.BADDEBT'
SUM. 'HOLDMERGE.HOLDMERG.CHGS'
PERCENTAGE AS '*TOTAL'
WHERE ( HOLDMERGE.HOLDMERG.FY EQ &FY.Enter 4 Digit Fiscal year. ) AND ( J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY,TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY IN tblmstr_banbrbanamexref)).Select the desired Specialty/s. );
WHERE HOLDMERGE.HOLDMERG.PERIOD LE &PERIOD.Enter the Ending Period in the format: YYYYMM, Ex: thru July 2014: 201407.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE ROW-TOTAL AS 'TOTAL'
-*ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE SUMMARIZE AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K

Ok, I tried commenting out the ON TABLE ROW-TOTAL AS TOTAL line. Now it works.

Can some explain what is going on? Its not clear to me why or what the SUMMARIZE is doing and why I need to comment out the ON TABLE ROW / ON TABLE COLUMN statements.

Can you set this RECOMPUTE / SUMMARIZE via the GUI or must you always update the text?

Ahhhhh!

No, its not working. As my cubemate just pointed out...the total columns for the rows are now gone!

I need those!

To fix your subtotals, either do not mix SUM. and normal results, or set the option that allows you to mix those (I forgot which, but it has to be set to NEW or something).

Frankly though, you don't need to specify SUM. there, the RECOMPUTE will take care of that because those columns aren't COMPUTE results. The only way to recompute them is to SUM them.

For the grand-total, I'm fairly certain that you can also specify ON TABLE RECOMPUTE just like you did for the subtotal RECOMPUTEs.

When I turn on Grand totals (across and down), it is the subtotals and grand totals that appear in the newly added last 3 columns that are causing teh grief.

The ON TOTAL RECOMPUTE sounds good but no luck so far.

To get me thru, I came up with this...seems to work....but I have not had success in moving the totals to the end right...seems they need to be to the left or they repeat for each month. Tehre must be a simpler way...there is a verb or something I am undoubtable missing...there must be other folks with a report trended by month with a few totals and a percentage...who need grand totals for all months...

TABLE FILE HOLDMERGE
SUM
HOLDMERGE.HOLDMERG.BADDEBT/P20C AS 'Total Bad Debt'
HOLDMERGE.HOLDMERG.CHGS/D12 AS 'Total Charges'
COMPUTE PERCENTAGE/D12.2% = ( HOLDMERGE.HOLDMERG.BADDEBT / HOLDMERGE.HOLDMERG.CHGS ) * 100; AS 'Total Percentage'
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.RPTBANBR
BY J0.TBLMSTR_BANBRBANAMEXREF.RPTBANAME
ON J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY RECOMPUTE
SUM. 'HOLDMERGE.HOLDMERG.BADDEBT'
SUM. 'HOLDMERGE.HOLDMERG.CHGS'
PERCENTAGE AS '*TOTAL'
SUM
HOLDMERGE.HOLDMERG.BADDEBT/P20C AS 'Bad Debt'
HOLDMERGE.HOLDMERG.CHGS/D12 AS 'Charges'
COMPUTE PERCENTAGE/D12.2% = ( HOLDMERGE.HOLDMERG.BADDEBT / HOLDMERGE.HOLDMERG.CHGS ) * 100; AS 'Percentage'
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY
BY LOWEST J0.TBLMSTR_BANBRBANAMEXREF.RPTBANBR
BY J0.TBLMSTR_BANBRBANAMEXREF.RPTBANAME
ACROSS LOWEST HOLDMERGE.HOLDMERG.FY
ACROSS LOWEST HOLDMERGE.HOLDMERG.PERIOD AS 'Period'


ON J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY RECOMPUTE
SUM. 'HOLDMERGE.HOLDMERG.BADDEBT'
SUM. 'HOLDMERGE.HOLDMERG.CHGS'
PERCENTAGE AS '*TOTAL'
WHERE ( HOLDMERGE.HOLDMERG.FY EQ &FY.Enter 4 Digit Fiscal year. ) AND ( J0.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY,TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY IN tblmstr_banbrbanamexref)).Select the desired Specialty/s. );
WHERE HOLDMERGE.HOLDMERG.PERIOD LE &PERIOD.Enter the Ending Period in the format: YYYYMM, Ex: thru July 2014: 201407.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
-*ON TABLE ROW-TOTAL AS 'TOTAL'
-*ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON

Robert,

Here is a solution to your problem:

  
TABLE FILE CAR
SUM 
RCOST NOPRINT DCOST NOPRINT 
BY COUNTRY
SUM 
RCOST DCOST COMPUTE RATIO/D7.2=RCOST / DCOST;
BY COUNTRY
ACROSS SEATS COMPUTE TRCOST/D6=C1; TDCOST/D6=C2; TRATIO/D7.2=TRCOST / TDCOST;
ON TABLE SUMMARIZE
END

quote:

Originally posted by RobertF:
The ON TOTAL RECOMPUTE sounds good but no luck so far.

TABLE, not TOTAL.

****CLOSED

thanks..I had no luck with the last two offerings...leaving my code as is...we have some folks going to training soon...I'll send this as an example to get some assistance with...