RE: Query Manager Totals Only

[Gary Thompson <gthompson@xxxxxxxxxxx>]

Thanks to Chuck and Vernon for your very informative replies.
I quickly added GROUP BY to the SQL part of the QM Query so I have an acceptable report.
Today did not go as planned so I've just now quickly reviewed and will take more time to digest . . .
 

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Saturday, February 28, 2015 8:27 AM
To: Midrange Systems Technical Discussion
Subject: Re: Query Manager Totals Only

I see that this works fine for a final-totals report - but not for subtotals - the contributing detail is included in the *QMQRY.

This is because there are no aggregate functions in this conversion - more on this in a minute.

So the resulting query for a level break on the STATE field came out like this:

SELECT
   ALL       STATE, (CDTLMT), (BALDUE), (CDTDUE)
   FROM      QIWS/QCUSTCDT T01
   ORDER BY  001 ASC

Then subtotals are done in the *QMFORM.

The statement could be changed to have GROUP BY STATE and have SUM() functions on the 3 fields - interesting that the parentheses are there, as if someone KNEW they needed the SUM() function.

So here's the revision I tried:

SELECT
   ALL       STATE, SUM(CDTLMT), SUM(BALDUE), SUM(CDTDUE)
   FROM      QIWS/QCUSTCDT T01
   GROUP BY STATE
   ORDER BY  001 ASC

With a little tweaking of the *QMFORM I got this result:

            TOTAL          TOTAL          TOTAL
            Credit        Balance         Credit
ST         Limit           Due            Due
-----  ----------  -------------  -------------
CA            700         250.00         100.00
CO            400          58.75           1.50
GA          9,999       3,987.50          33.50
MN         14,999         510.00            .00
NY          2,100         589.50            .50
TX          5,200          62.00            .00
VT            700         439.00            .00
WY          9,999            .00            .00
        ==========  =============  =============
TOTAL      44,097       5,896.75         135.50

This is probably what Sam's suggestion could have generated.

Another thing to do is run ANZQRY over the *QRYDFN - I got the message
QWM2323 - Detail records will not be omitted.

In that message are things to do to get the summary only. One is to omit final summaries in the *QRYDFN. I tried this and eventually got this:

                   TOTAL          TOTAL          TOTAL
                   Credit        Balance         Credit
ST                Limit           Due            Due
------------  ----------  -------------  -------------
CA                   700         250.00         100.00
CO                   400          58.75           1.50
GA                 9,999       3,987.50          33.50
MN                14,999         510.00            .00
NY                 2,100         589.50            .50
TX                 5,200          62.00            .00
VT                   700         439.00            .00
WY                 9,999            .00            .00

FINAL TOTALS      44,097       5,896.75         135.50

So there are options - with a little tweaking it might be what you want.

HTH
Vern

On 2/28/2015 8:41 AM, Vernon Hamberg wrote:
> Hi Gary
>
> I used RTVQMQRY and RTVQMFORM to pull the *QRYDFN into source. Then I 
> created the *QMQRY and *QMFORM - I got a summay-only result.
>
> This was on a V5R3 box, so it should work for anyone.
>
> You need to have created and then include the *QMFORM when running the 
> *QMQRY.
>
> I tried a simple *QRYDFN over QIWS/QCUSTCDT with the following 
> relevant items:
>
> Selected files
>   ID     File           Library       Member        Record Format
>   T01    QCUSTCDT       QIWS          *FIRST        CUSREC
> Report column formatting and summary functions
>   Summary functions:  1-Total, 2-Average, 3-Minimum, 4-Maximum, 
> 5-Count           Overrides
>   Field           Summary    Column Dec  Null       Dec  Numeric
>   Name            Functions  Spacing  Column Headings         Len Pos  
> Cap   Len  Pos  Editing
>    CDTLMT          1          2        Credit 4    0          4 0
> Limit
>    BALDUE          1          2        Balance 6    2 6    2
> Due
>    CDTDUE          1          2        Credit 6    2          6 2
> Due
> Selected output attributes
>   Output type . . . . . . . . . . . . . . Display
>   Form of output  . . . . . . . . . . . . Summary only
>
> The output from running this *QRYDFN was this:
>
>          Credit        Balance         Credit
>          Limit         Due             Due
> FINAL TOTALS
> TOTAL    44,097       5,896.75         135.50
>
> The output from running the *QMQRY with the *QMFORM (STRQMQRY
> QMQRY(VHAMBERG1/TOTALSONLY) QMFORM(*QMQRY)) was this:
>
>     TOTAL          TOTAL          TOTAL
>     Credit        Balance         Credit
>     Limit           Due            Due
> ----------  -------------  -------------
>     44,097       5,896.75         135.50
>
> The SELECT statement created by RTVQMQRY was this:
>
> SELECT
>   ALL       SUM(CDTLMT), SUM(BALDUE), SUM(CDTDUE)
>   FROM      QIWS/QCUSTCDT
>
> You would add substitution variables to a WHERE clause here.
>
> HTH
> Vern
>
> On 2/27/2015 4:56 PM, Gary Thompson wrote:
> > Easy to do in a *QRYDFN but I'm not seeing how to set a *QMQRY to 
> > print only totals ?
> >
> > We have a local command that converts *QRYDFN to *QMQRY which I used 
> > to create a *QMQRY version that I can pass parameters to so users can 
> > create a totals only report, but have yet found how to do that for 
> > the Query Manager report.