Classics Forum

Re: RE: save aggregate as variable in report

Posted By:
Date:

I saw your THANKS message -- I was amused that it got the tag number 101.

It isn't clear to me what your computations are doing - why you are summing
some values and counting others, for example.

Be aware that your B.G.O clause is dealing with grand totals - over the
entire report data set - not per group totals. And note that the code
shown is syntactically incorrect -- see the inserted line I added.

What I think you might want to do is:

DEFINE
VARIABLE var DECIMAL
END

...big chunk omitted...

AFTER GROUP OF hist_coopcode
IF hist_coopcode = "lis" THEN
BEGIN
LET var = 3 * (GROUP TOTAL OF hist_sze WHERE hist_coopcode =
"lis")
PRINT "Coop Code: ", hist_coopcode, COLUMN 15, " = ", COLUMN
18, var USING "####.##"
END
ELSE IF hist_coopcode = "021" THEN
BEGIN
LET var = 6 * (GROUP COUNT WHERE hist_coopcode = "021")
PRINT "Coop Code: ", hist_coopcode, COLUMN 15, " = ", COLUMN
18, var USING "####.##"
END
...

And then your ON LAST ROW block might read:

ON LAST ROW
LET var = 3 * (TOTAL OF hist_sze WHERE hist_coopcode = "lis") +
6 * (COUNT WHERE hist_coopcode = "021") +
...
PRINT "Total sales:", COLUMN 18, var USING "###&.&&"

Note that the WHERE clauses in the A.G.O block are technically redundant
(when the hist_coopcode = "021", the GROUP COUNT is only for the case where
hist_coopcode = "021"), but they clarify the code in the ON LAST ROW block.
You could also print the expressions on the RHS of the LET statements
directly in the PRINT statements in the A.G.O block, but again, the code is
clearer as written. Indeed, the BEGIN and END can be removed because a
single PRINT in the A.G.O block would do the job:

AFTER GROUP OF hist_coopcode
IF hist_coopcode = "lis" THEN
LET var = 3 * (GROUP TOTAL OF hist_sze WHERE hist_coopcode =
"lis")
ELSE IF hist_coopcode = "021" THEN
LET var = 6 * (GROUP COUNT WHERE hist_coopcode = "021")
...

PRINT "Coop Code: ", hist_coopcode, COLUMN 15, " = ", COLUMN 18, var
USING "####.##"

This is perhaps the best reason for introducing the variable - it makes it
clear what's different (the computed value) and what's the same (the print
formatting).

Also, I'm highly dubious about those hard-wired 3 and 6 values; those
should be being retrieved from the database, so that the multiplication is
done between two selected values.

If it were not for the odd-ball case with TOTAL versus everything else with
COUNT, then you might be able to handle the ON LAST ROW clause with a
suitable TOTAL OF clause. This is a domain-specific issue -- only you know
what a coopcode is, and why the rules are as they are. Note that if you
need to add coopcode "039" at some point, you have to do major surgery on
the report.

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management Solutions
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"


|---------+---------------------------->
| | "JAY ZWAGERMAN" |
| | <jay2@iafalls.com|
| | > |
| | Sent by: |
| | forum.subscriber@|
| | iiug.org |
| | |
| | |
| | 02/27/2003 09:31 |
| | AM |
| | |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: classics@iiug.org |
| cc: |
| Subject: Re: RE: save aggregate as variable in report [92] |
| |
>---------------------------------------------------------------------------------------------------------------------------------------------|




Let me clarify my question and give my code. Here is the bottom part of my
ace report. In the before group of, you see that I use if else statements
to find 4 different totals. I can’t do a group total of in the “on last
row� and when I do ust a total of in "on last row" It doesn't give me a
total of the computed numbers.

format

page header
print column 30, "The Spokesman"
skip 1 line
print column 20, "Web Sales Total",1 space,userbdate," -
",useredate
skip 1 line
print column 25, "Coops: 001 - 017 - 021 - lis"

before group of hist_coopcode
if hist_coopcode = "lis" then
print "Coop Code: ",hist_coopcode,column 15," = ",column 18,
total of (hist_sze * 3.00) using "####.##"
else if hist_coopcode = "021" then
print "Coop Code: ",hist_coopcode,column 15," = ",column 18,
count * 6 using "####.##"
[...JL: there's a line missing here...probably 'else if hist_coopcode =
"017" then'...]
print "Coop Code: ",hist_coopcode,column 15," = ",column 18,
count * 3 using "####.##"
else if hist_coopcode = "001" then
print "Coop Code: ",hist_coopcode,column 15," = ",column 18,
count * 3 using "####.##"
on last row
print "Total sales:" SOMETHING


Here is the output so far

Input begin date (using MMDDYY):02012003




The Spokesman

Web Sales Total 02/01/2003 - 02/28/2003

Coops: 001 - 017 - 021 - lis
Coop Code: 001 = 372.00
Coop Code: 017 = 372.00
Coop Code: 021 = 744.00
Coop Code: lis = 247.50
Total sales:

Messages In This Thread

• save aggregate as variable in report
  JAY ZWAGERMAN -- Tuesday, 15 April 2003, at 3:55 p.m.
  • RE: save aggregate as variable in report
    Simmons, Keith -- Tuesday, 15 April 2003, at 3:55 p.m.
  • RE: save aggregate as variable in report
    Jim Noble -- Tuesday, 15 April 2003, at 3:55 p.m.
  • RE: save aggregate as variable in report
    Jim Noble -- Tuesday, 15 April 2003, at 3:55 p.m.
    • Re: RE: save aggregate as variable in report
      JAY ZWAGERMAN -- Tuesday, 15 April 2003, at 3:55 p.m.
      • Re: RE: save aggregate as variable in report
        Marie Ann Mercado -- Tuesday, 15 April 2003, at 3:55 p.m.
      • Re: RE: save aggregate as variable in report
        Marie Ann Mercado -- Tuesday, 15 April 2003, at 3:55 p.m.
        • THANKS
          JAY ZWAGERMAN -- Tuesday, 15 April 2003, at 3:55 p.m.
      • Re: RE: save aggregate as variable in report
        Jonathan Leffler -- Tuesday, 15 April 2003, at 3:55 p.m.
  • RE: save aggregate as variable in report
    Konikoff, Rob (Contractor) -- Tuesday, 15 April 2003, at 3:55 p.m.
  • Re: save aggregate as variable in report
    Jonathan Leffler -- Tuesday, 15 April 2003, at 3:55 p.m.
    • RE: save aggregate as variable in report
      Konikoff, Rob (Contractor) -- Tuesday, 15 April 2003, at 3:55 p.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.