Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Is it possible to use a WHERE TOTAL while also using an ACRO...


robert fuschetto

Recommended Posts

Is it possible to use a WHERE TOTAL while also using an ACROSS.

I have a report the shows:

DEPT (BY)

CODE (BY)

Period (ACROSS)

Units is the measure.

I wanted to exclude any rows where the grand total for the row was less than 50. I tried a WHERE TOTAL. It does not seem to work. This is the first time I recall using the ACROSS though

Link to comment
Share on other sites

By employing the multiverb feature, I was able to create a report with ggsales that filters out the rows that fall below 300,000 of total unit sales.

TABLE FILE GGSALES

SUM

COMPUTE ROW_UNITS/I11 = UNITS;

BY REGION

BY CATEGORY

SUM UNITS

BY REGION

BY CATEGORY

ACROSS DATE

WHERE TOTAL ROW_UNITS GT 300000;

END

 

 

image.png748437 37.8 KB

Link to comment
Share on other sites

The following code avoids the use of the multiple verb feature by creating a hold file. I would think that it could be used as a model for implementing in InfoAssist.

TABLE FILE GGSALES

SUM UNITS

BY REGION

BY CATEGORY

ON TABLE HOLD AS REPORTDATA

END

TABLE FILE GGSALES

SUM UNITS

BY REGION

BY CATEGORY

BY COMPUTE ROW_UNITS/I11 = DB_LOOKUP(REPORTDATA,REGION,REGION,CATEGORY,CATEGORY,UNITS); NOPRINT

ACROSS DATE

WHERE ROW_UNITS GT 300000

END

Link to comment
Share on other sites

Thanks Dave. I like first solution better and since this particular fex is relatively straight forward I may put some comments in there instructing a future modifier to comment out certain rows if they need to get to the GUI for formatting or something.

Ill look at your second solution as well. I had thought about creating HOLD file as well but I was going to create a HOLD file of DEPT, CODE, and GRANDTTL then my initial HOLD file and JOIN them.

Link to comment
Share on other sites

One more questionon your ORIGINAL solution. I just noticed that your solution seems to add a column of the grand total summed units to the report. Can that be hidden If yo have the ACROSS in efefct and the ROW totals turned on, the row total is now doubleit includes the new SUM Units. Hope this makes sense. NO PRINT did not seem to work.
Link to comment
Share on other sites

This is probably a question of how does WF works internaly with its matrix

Below an option to make it run

Note that I have a SET at top and an AS name.

Without that, it wont work

SET ASNAMES = ON

 

TABLE FILE GGSALES

SUM COMPUTE ROW_UNITS/I11 = UNITS;

BY REGION

BY CATEGORY

SUM UNITS AS 'QTY'

BY REGION

BY CATEGORY

BY DATE

WHERE TOTAL ROW_UNITS GT 300000;

ON TABLE HOLD AS TMP1

END

 

TABLE FILE TMP1

SUM QTY

BY REGION

BY CATEGORY

ACROSS DATE ROW-TOTAL

ON TABLE SET PAGE-NUM NOLEAD

END

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...