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.