Re: Query Manager Totals Only

Chuck

See my further replies to this question - much of what you say was already addressed there, including mention of ANZQRY and the message about details, as well as some options I took based on that message.

The OLAP functions are likely to be a better approach, I agree.

Vern

On 2/28/2015 10:27 AM, CRPence wrote:

On 28-Feb-2015 08:41 -0600, Vernon Hamberg wrote:

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.

I used RTVQMQRY and RTVQMFORM to pull the *QRYDFN into source. Then
I created the *QMQRY and *QMFORM - I got a summary-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 Col Dec Null Dec Numeric
Name Functions Spc Column Hdgs 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.

The example offered does not require an explicitly retrieved\created form; simply requesting STRQMQRY ALWQRYDFN(*ONLY) QMFORM(*SYSDFT), having named that *QRYDFN in the QMQRY() parameter specification, will produce the same report. The reason...

That example is an extreme\special case of summary-only output; a scenario that is rarely utilized in reporting, whereby there is an aggregation over the entire file without any /report breaks/. Almost any revision to the Query Definition object for the reporting, for which that special-case no longer holds, the Analyze Query (ANZQRY) would warn that the "Detail records will not be omitted." per QWM2323; i.e. the /summary-only/ nature of the reporting will have been lost to the QM Query reporting. The one revision to that query that is also supported is having just one /break/ for which FINAL TOTALS are omitted [i.e. Suppress Summaries=Yes for BrkLvl=0 established in the *QRYDFN], for which the derived SQL query /assumes/ the Query/400 result intended collation on the /break field/ according to msg QWM2307 "Break field DBILIB is not a sort field.".

The OP will likely want to compose a grouping query [possibly OLAP query; e.g. WITH ROLLUP] against which an effectively detail-records report-FORM (QMFORM) presents the aggregate results with /breaks/ and further summaries are defined.