Re: Filtering a summarized report

["Korbel, Gordon E." <GEKORBEL@NORSTAN.COM>]

Kris,

Although it might not work in your particular application, and the
performance might suck, but there is a possible solution with SQL --
involves retrieving the summary and detail at the same time (in the same
row) -- but it will probably only work if you have one main SQL statement
which drives your program.

e.g.

begin-select
a.business_unit
a.amt
b.amt
sum(c.amt) &Subtotal

from PS_ITEM A, PS_ITEM_ACTIVITY B, PS_ITEM C
WHERE A.KEYS = B.KEYS AND
A.KEY_SUBSET = C.KEY_SUBSET
Order by SOME_FIELDS
GROUP BY A.BUSINESS_UNIT, A.AMT, B.AMT
HAVING SUM(C.AMT >= 2)

In this case, Key_subset refers to the group of rows which determines a
subtotal level.  KEYS refers to whatever the normal join criteria of your
program is.  Tables A and C should be the same -- different alias-names.
Ignore &subtotal and print your report as usual -- you should now only see
the rows where subtotal is greater than or equal to 2.

Note that the SUM might be a COUNT or some other aggregate function.

If your report is of a main-select structure (that is, all the reported rows
are returned by a single SQL statement) this solution could probably be
adapted to your requirements -- and would likely be faster to implement than
the array idea below, which would certainly work for virtually any set of
requirements and or program structures.

Sorry for being so verbose.

-Gordon

-----Original Message-----
From: John Milardovic [mailto:milardj@SX.COM]
Sent: Wednesday, June 02, 1999 7:55 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Filtering a summarized report


Hi Kris.

I'm relatively new to SQR but I did something similar with a report.
Instead of printing the rows as they are retrieved stuff them into an array.
After all the rows are returned you can then process your array and print
only those elements that meet your condition.

The difficulty will be in storing the sub-total in a way that allows you to
tie it with the proper array elements.

I think I would create an on-break procedure which would loop through the
array after sub-total was determined and add it to a field within the
appropriate array elements.

You will need (at the very least):   -an array with fields for every column
to be printed
                                                 - a field for the subtotal
                                                 - a counter to keep track
of the last element of the array
that had a subtotal added to it.

Your procedure to add the sub-total would look something like this.

while #x < #y     ! #x is first element of the new group #y is total rows
returned at this point
        arrayname.subtotal(#x) = #subtotal   ! subtotal will be the same for
all rows in group
        add 1 to #x
end-while

Hope this helps.  (As I said I'm new to this so if anyone has an easier way
please post it)

> -----Original Message-----
> From: Kris Cagan [SMTP:kristinc@SLAC.STANFORD.EDU]
> Sent: Tuesday, June 01, 1999 8:16 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Filtering a summarized report
>
> I have been working on this report for WEEKS, and I am at an impasse.  Any
> help would be IMMENSELY appreciated:
>
> The original report shows detail comparing transactions on two tables over
> a period of time.  For each item, there is a subtotal, at the end of the
> report are grand totals.  I used ON-BREAK processing to get the different
> levels of the report.  Looks nice.  The subtotals are calculated AS the
> detail is printed with an ADD to the Subtotal variable which is printed
> later when the item-level breaks.
>
> Okay, nice straight forward, no problem.  User came back with the request
> for a second report that showed the SAME INFORMATION but only for those
> items with a subtotal greater than 2.  The subtotal is done AFTER the
> detail is printed.  Is there a way to have SQR do such a filter?
>
> thanks in advance.
>
> Kris