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

[robert fuschetto]

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

[David Beagan]

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

[robert fuschetto]

Well it works butI cant get back into the GUIfunny thing is the error it showsis exactly what you wanted me to do!!! Is there a way to do this in the GUI

[David Beagan]

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

[robert fuschetto]

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.

[robert fuschetto]

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.

[Martin Yergeau]

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

[David Beagan]

robertf:

 

NO PRINT did not seem to work.

 

 

I tried NOPRINT (without a space) and it worked for me.

[Martin Yergeau]

It also work.

Dont know what I did at first try to not have it working as expected.

TABLE FILE GGSALES

SUM

COMPUTE ROW_UNITS/I11 = UNITS; NOPRINT

BY REGION

BY CATEGORY

SUM UNITS

BY REGION

BY CATEGORY

ACROSS DATE ROW-TOTAL

WHERE TOTAL ROW_UNITS GT 300000;

END

[robert fuschetto]

Thanks all. This helps!