We have a very structured fml report.

Management wants a similar report but with some added flexibility that fml does not provide.

I created a WebFocus Report. Essentially it has two group levels. The 1st sort/group level subtotals on 2 values: REVENUE and EXPENSE. The 2nd sort/group level subtotals on various types of Revenue and Expenses. Account detail is also printed. A grand total nets out Revenue and Expenses. It worked nicely….until…

They want to add an additional stats *section* to the report following Revenue and Expenses. There a couple of issues. The stats do not naturally fall into two sorts/groupings so essentially the 1st sort/group and 2nd sort/group will be the same value. Detail is present. When I run the report two things happen:

1) The report prints the REVENUE section then the EXPENSE section as it should. The STATS section then prints. Since it’s 1st sort/group value (STATS) is exactly the same as it’s 2nd sort/group value (STATS), we essentially get what appears to the user as a duplicate subtotal line following the STATS detail. We can likely live with that.

2) The second issue is that the Grand Total row is now the net of Revenue, Expenses AND the stats. Not good!

So, the Grand Total issue is my biggest problem. I can add a 3rd sort/group level to accommodate the netting of Revenue and Expenses. I can then turn off grand totals. However I will now have 3 subtotal lines for the STATS section…all the same. BTW…I can see them also wanting additional stats sections down the road so continually having what the user sees as 3 duplicate subtotal lines won’t fly.

Is there a simple way to conditionally print a given subtotal. Something like saying PRINT the sort/group 1 subtotal if sort/group 1 value <> ‘STATS’ ELSE don’t print it. Ditto for sort/group2 etc etc.……or must I move the stats section to its own report.

As always you experience and reccomendations are appreciated..

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

quote:

Is there a simple way to conditionally print a given subtotal.

APP PREPENDPATH IBISAMP
-RUN
TABLE FILE GGSALES
SUM DOLLARS/I9C
BY  REGION
ON  REGION SUBTOTAL AS 'Total Region' WHEN REGION EQ 'Midwest'   
BY  ST 
ON  ST     SUBTOTAL AS 'Total State'  WHEN ST     EQ 'CA' 
ON  TABLE NOTOTAL
ON TABLE SET STYLE *
   INCLUDE=endeflt,$
ENDSTYLE
END
-EXIT  

Displays a subtotal for the 'region group' only for the 'Midwest.

Displays a subtotal for the 'state group' only for 'California'.

This message has been edited. Last edited by: David Briars,

quote:

ON REGION SUBTOTAL AS 'Total Region' WHEN REGION EQ 'Midwest'

quote:

ON REGION SUBTOTAL AS 'Total Region' WHEN REGION EQ 'Midwest'

Interesting...
When I try something like this:
ON REGION SUBTOTAL AS 'Total Region' WHEN REGION EQ 'Midwest'

It prints ALL regions.

If I try this it suppresses the MidWest and prints all others...which is what I wanted..

ON REGION SUBTOTAL AS 'Total Region' WHEN REGION NE'Midwest'


2 Questions

1) Does supressing a particular subtotal cause the value for that subtotal NOT to be included in a subsequent subtotal..Ie in your example, if I suppress MidWest, but later add another subtotal for say, Country, would the MidWest still be included in the total for country?

2) I can not figure out the syntax to suppres mutiple regions...EX: say I want to supress the MidWest and SouthWest....I tried a simple *OR*...did not work...tried two *WHENS*...got syntax error....

Thanks!

quote:

2) I can not figure out the syntax to suppres mutiple regions...EX: say I want to supress the MidWest and SouthWest....I tried a simple *OR*...did not work...tried two *WHENS*...got syntax error....

RE: 2) I can not figure out the syntax to suppres mutiple regions...EX: say I want to supress the MidWest and SouthWest....I tried a simple *OR*...did not work...tried two *WHENS*...got syntax error....

Ok, as soon as I hit Post I realized it likely shoudl have been an *AD* condition...so I think I know how to Supress MidWest and SouthWest...

Still not sure why using EQ MidWest allowed everything esle to print but so be it.

Would you think the suppressed subtotal would still be included in further subtaotal, again the country example....I guess I can create something and see....

..I meant *AND* condition...its early on a Monday!

Using Davids example code from above -

APP PREPENDPATH IBISAMP
-RUN
TABLE FILE GGSALES
SUM DOLLARS/I9C
BY  REGION
ON  REGION SUBTOTAL AS 'Total Region' WHEN REGION IN ('Midwest','Southeast')
BY  ST 
ON  ST     SUBTOTAL AS 'Total State'  WHEN ST     EQ 'CA' 
ON  TABLE NOTOTAL
ON TABLE SET STYLE *
   INCLUDE=endeflt,$
ENDSTYLE
END
-EXIT

-- or --

APP PREPENDPATH IBISAMP
-RUN
TABLE FILE GGSALES
SUM DOLLARS/I9C
BY  REGION
ON  REGION SUBTOTAL AS 'Total Region' WHEN NOT REGION IN ('Midwest','Southeast')
BY  ST 
ON  ST     SUBTOTAL AS 'Total State'  WHEN ST     EQ 'CA' 
ON  TABLE NOTOTAL
ON TABLE SET STYLE *
   INCLUDE=endeflt,$
ENDSTYLE
END
-EXIT

T

Thanks, similar syntax with AND worked but I like this better!