First things first, I love the new EXPANDBYROW accordian reports in WebFOCUS 7.7.02. Having said that, based on the documentation, "A SUB-TOTAL command is automatically added for the next-to-last BY field. Each level will be presented at the aggregated level, and the data values will represent the aggregation of the lowest level BY." I need to make the aggregation into a summarization rather than a summation.

I've tried "ON TABLE SUMMARIZE AS 'TOTAL'" but that didn't do anything. How can I fix this?

Note: I used "SET EXPANDBYROW=ON" instead of "ON TABLE SET EXPANDBYROW=ON". I didn't see a difference.

This message has been edited. Last edited by: jjeanjaquet,

Hi all,

FYI, Joe has a case opened regarding this issue, which has been in research at the moment.

Joe, if you hear any update/solution from the case, can you please kindly post it here? Thank you in advance for sharing with all.

Cheers,

Kerry

Hi Joe,

Here is some additional info that may be of help:

Accordion reports are designed as a turnkey feature that will automatically setup all the required subtotals and functionality with a single SET command. For situations where you want to use something other than a standard SUBTOTAL within your levels you can override the system generated definitions and designate your own SUBTOTAL structures.

The rows that open within the accordion are SUBTOTAL rows. So for the tree to function correctly each of your BY fields (except the last one) must have a defined SUBTOTAL row. To include SUBTOTAL rows with SUMMARIZED or RECOMPUTED values you can define your own SUBTOTAL rows. Once the server sees any SUBTOTAL lines defined it turns off the automatic SUBTOTAL generation and works only on what has been user defined. So as long as you define SUBTOTAL rows for each of your BY fields, except the last one, you should have a fully functional accordion tree to meet your current report requirements.

A basic example:

TABLE FILE CAR
SUM RC DC
COMPUTE TOTVAL/D14=RC*DC;
BY COUNTRY BY CAR BY MODEL
ON COUNTRY SUBTOTAL
ON CAR RECOMPUTE
ON TABLE SET HTMLCSS ON
ON TABLE SET EXPANDBYROW ON
END


Cheers,
Kerry

Good call Kerry,
That does exactly what I wanted it to do. The overall subtotals initiated by the EXPANDBYROW are over-riden by the invididual:
ON {BY COLUMN} RECOMPUTE AS 'TOTAL'
As shown in the above example.

Thanks a bunch Kerry,
Joe