Charge Query Specifications Processing Options

Performing Query Functions › Charge Query Panels › Charge Query Specifications Processing Options

Charge Query Specifications Processing Options

Located on the second line of the Charge Query Specifications Panel are three processing options:

Summarize
Break
Bud/DBCR

These options determine the type of report that CA PMA Chargeback generates. The Summarize and Break fields work in unison and allow results to be displayed in three different types of formats:

Summary
Summary-with-Breaks
Detail

The Bud/DBCR field, when set to Y, allows budget, debit, and credit information to display along with charge results. CA PMA Chargeback displays budget, debit, and credit results in one of the above formats. If Bud/DBCR is set to N, CA PMA Chargeback displays the budget, debit, and credit fields without any data.

Summary Report (Summarize=Y, Break=N)

When you set the summarize option to Y and the Break option to N in the Charge Query Specifications panel, CA PMA Chargeback summarizes the result fields of the records which meet query criteria. It then displays these results as a one-page summary report.

CA PMA Chargeback determines the values of the result fields: Rec Count, Units, Rate, and Charge by using the SQL functions specified in the Sum column of the Charge Query Specifications panel. The result fields can hold a summation, average, minimum, or maximum of the selected records. To change the default SQL functions for these result fields, specify a new function for the default displayed on the Charge Query Specifications panel. Valid choices are:

To select a summary report set the Summarize option to Y as shown on the panel below.

+-- CBQQRYS ------------- Charge Query Specifications ------------------------+
|      Panel   Exit   Help                                                    |
| --------------------------------------------------------------------------- |
| Type information for Query CPUCOST   Desc CPU COST FOR PER.1-3    Table TAB |
|                                                                             |
| Summarize Y  Break N  Bud/DBCR N           Version : PROD PROD              |
|                                   Brk Srt                            Op Sum |
| ORD Type . . . .                   N      Rec Count                   > SUM |
| Charge Element .                   N      Units  . .                  > SUM |
| Qualifier  . . .                   N      Rate . . .                  > AVG |
| Period Number  .       thru        N      Charge . .                  > SUM |
| DIVISION . . . .                   N                                        |
| DEPARTMENT . . .                   N                                        |
+ ----------------------------------------------------------------------------+

After pressing F2 (Exec), the following Charge Query Results panel appears. Note the following:

The first column reiterates the values on the Charge Query Specifications panel.
The values in the Record Count, Units, and Charge fields are summations of the Record Count, Units, and Charge of the records meeting the criteria specified in the Charge Query Specifications panel (see A., B, and D below).
The Rate field is an average Rate of the records meeting the criteria specified in the Charge Query Specifications panel (see C below).

Since there is no query criteria, CA PMA Chargeback selects and summarizes all records from the CCCTAB. See Example 1: Entering Selection Criteria Within the Charge Element Field for an example of a summary report with selection criteria.

USERID CA PMA Chargeback MM/DD/YY HH:MM:SS __ Acctdefs Cbdefs Query Data Period Options Exit Help +-- CBQQRYZ ----------------- Charge Query Results ---------------------------+ | | | Table . . . . . : TAB | | Version . . . . : PROD PROD Record Count : 177 A. | | ORD Type . . . . : Units . . . : 91.16665 B. | | Charge Element . : Rate . . . . : 185.95238 C. | | Qualifier . . . : Charge . . . : 22861.99500 D. | | Period Number . : thru Debit Amt . : | | DIVISION . . . . : Credit Amt . : | | DEPARTMENT . . . : Adjust Charge: | | Budget . . . : | | % of Budget : | | | | | | | | | | | | | +-----------------------------------------------------------------------------+ Command ===> ____________________________________________________________ F1=Help F3=Exit F4=Prompt F9=Command F10=Action Bar F12=Cancel PA1=Susp

Summary-with-Breaks Report (Summarize=Y Break=Y)

If you select both the Summarize and Break options, CA PMA Chargeback displays the result fields of the selected records in summary format. However, in contrast to a Summary report, result records are broken into groups. CA PMA Chargeback groups the records with identical values in the fields selected in the Brk column of the Charge Query Specifications panel. It displays the result fields for each group separately.

Depending upon the function you specify in the Sum column, CA PMA Chargeback:

Summarizes
Averages
Minimizes
Maximizes

the result fields Rec Count, Units, Rate, and Charge for each group of records. The scroll bar denotes the number of groups available for viewing.

Remember if you set the Break option to Y, you must set at least one field in the Brk column to Y.

To generate a summary-with-breaks report and change the default SQL function SUM, to AVG within the Units field, do the following:

Set the Summarize and Break fields to Y (see A below)
Select Brks for ORD Type and Charge Element (see B below) by entering Y in the Brk column

Change SUM to AVG in the Sum column (see C below)

  USERID                    CA PMA Chargeback                 MM/DD/YY HH:MM:SS
  __    Acctdefs   Cbdefs   Query   Data   Period   Options   Exit   Help
 
 +-- CBQQRYS ------------- Charge Query Specifications ------------------------+
 |      Panel   Exit   Help                                                    |
 | --------------------------------------------------------------------------- |
 | Type information for Query CPUCOST   Desc CPU COST FOR PER.1-3    Table TAB |
 |            A.                                                               |
 | Summarize Y  Break Y  Bud/DBCR N           Version : PROD PROD              |
 |                                   Brk Srt                            Op Sum |
 | ORD Type . . . .                   Y      Rec Count                   > SUM |
 | Charge Element .                   Y B.  Units  . .                 > AVG C.| 
 | Qualifier  . . .                   N      Rate . . .                  > AVG |
 | Period Number  .       thru        N      Charge . .                  > SUM |
 | DIVISION . . . .                   N                                        |
 | DEPARTMENT . . .                   N                                        |
 |                                                                             |
 |                                                                             |
 |                                                                             |
 |   F2=Exec   F5=Save   F6=Delete   F11=Refresh                               |
 +-----------------------------------------------------------------------------+
 
  Command ===> ____________________________________________________________
  F1=Help  F3=Exit  F4=Prompt  F9=Command  F10=Action Bar  F12=Cancel  PA1=Susp

The next panel shows two rows of results (see 1 and 2). Note the following:

CA PMA Chargeback summarizes and displays each unique combination of ORD Type and Charge Element separately. Three groups of records result (see A below).
Values within the Record Count and Charge fields are total Record Count and Charge values for the records in the group (see B and D below).

Values within the Units and Rate fields are an average of the units and rates of the records in the group (see C below).

Note: In a summary-with-breaks report, CA PMA Chargeback displays any SQL function applied to the Record Count, Units, Rate, or Charge fields, other than SUM (see C below).

+-- CBQQRYB ----------------- Charge Query Results ---------------------------+

|       Panel   Exit   Help                              1.                   |

|-----------------------------------------------------------------------------|

|                                          More: + - 1     Row: 0001 of 0003  |

|  Table  . . . . . : TAB                            A.                       |

|  Version  . . . . : PROD PROD         Record Count :  B.           134      |

|  ORD Type . . . . : MBJ               Units  . . . :     C.    4.00980 AVG  |

|  Charge Element . : CPU-CHARGE        Rate . . . . :          159.05882 AVG |

|  Qualifier  . . . :                   Charge . . . :    D. 14459.99500      |

|  Period Number  . :                   Debit Amt  . :                        |

|  DIVISION . . . . :                   Credit Amt . :                        |

|  DEPARTMENT . . . :                   Adjust Charge:                        |

|                                       Budget . . . :                        |

|                                       % of Budget  :                        |

+-- CBQQRYB ----------------- Charge Query Results ---------------------------+

|       Panel   Exit   Help                              2.                   |

|  ---------------------------------------------------------------------------|

|                                          More: + - 1     Row: 0002 of 0003  |

|  Table  . . . . . : TAB                            A.                       |

|  Version  . . . . : PROD PROD         Record Count :   B.           42      |

|  ORD Type . . . . : MBJ               Units  . . . :      C.   7.00000 AVG  |

|  Charge Element . : CPX-CHARGE        Rate . . . . :          400.00000 AVG |

|  Qualifier  . . . :                    Charge . . . :  D.   8400.00000      |

|  Period Number  . :                   Debit Amt  . :                        |

|  DIVISION . . . . :                   Credit Amt . :                        |

|  DEPARTMENT . . . :                   Adjust Charge:                        |

|                                       Budget . . . :                        |

|                                       % of Budget  :                        |

|                                                                             |

|   F7=Bkwd   F8=Fwd                                                          |

+-----------------------------------------------------------------------------+

Detail Report (Summarize=N, Break=N)

If you do not select the Summarize and Break options, you receive a detail report of the records within the CCCTAB or CCCMOD tables that meet any selection criteria. CA PMA Chargeback displays all fields of the selected records individually. The scroll bar denotes the number of records available for viewing.

In the next example, CA PMA Chargeback displays all the fields of the CCCTAB table since this is a detail report. All the records of the CCCTAB table are displayed because there is no query criteria. The CCCTAB contains 21 rows. The example below shows two of these records.

Note the two additional fields on the Detail Charge Query Results panel:

A.

If a value appears for the Modifier field, the record is a modified record.

B.

Split/Modified is a two-character flag. The first character indicates whether the record has a split charge associated with it. The second character indicates whether the record has been modified.

+-- CBQQRYD ----------------- Charge Query Results ---------------------------+

|       Panel   Exit   Help                     1.                            |

| --------------------------------------------------------------------------- |

|  Table  . . . . . : TAB                  More: + - 1     Row: 0001 of 0021  |

|  Version  . . . . : PROD PROD                                               |

|  ORD Type . . . . : MBJ               Record Count :                1       |

|  Charge Element . : CPU-CHARGE        Units  . . . :          1.00000       |

|  Qualifier  . . . : SHIFT1            Rate . . . . :           .50000       |

|  Period Number  . : 1                 Charge . . . :           .50000       |

A.Modifier . . . . : WEEKEND Debit Amt . : |

B.Split/Modified . : NY Credit Amt . : |

|  DIVISION . . . . : RESTON            Adjust Charge:                        |

|  DEPARTMENT . . . : TECHWRIT          Budget . . . :                        |

|                                       % of Budget  :                        |

+-- CBQQRYD ----------------- Charge Query Results ---------------------------+

|       Panel   Exit   Help                       2.                          |

| --------------------------------------------------------------------------- |

|  Table  . . . . . : TAB                  More: + - 1     Row: 0002 of 0021  |

|  Version  . . . . : PROD PROD                                               |

|  ORD Type . . . . : MBJ               Record Count :                1       |

|  Charge Element . : CPU-CHARGE        Units  . . . :          2.00000       |

|  Qualifier  . . . : SHIFT1            Rate . . . . :           .50000       |

|  Period Number  . : 2                 Charge . . . :          1.00000       |

A. Modifier . . . . : WEEKEND Debit Amt . : |

B. Split/Modified . : NY Credit Amt . : |

|  DIVISION . . . . : RESTON            Adjust Charge:                        |

|  DEPARTMENT . . . : TECHWRIT          Budget . . . :                        |

|                                       % of Budget  :                        |

|                                                                             |

|   F7=Bkwd   F8=Fwd                                                          |

+-----------------------------------------------------------------------------+

Bud/DBCR

The Bud/DBCR option displays budget, debit and credit information in the format specified using the Summarize and Break fields. If you specify selection criteria on the Charge Query Specifications panel, CA PMA Chargeback displays budget, debit, and credit information for only those records that meet the criteria.

Note: Using the Bud/DBCR option adds to the processing requirements of the query, and should not be used as a matter of course.

The following fields contain budget, debit, and credit information on the Result panels.

Debit Amt: The amount by which CA PMA Chargeback reduces the Charge value.
Credit Amt: The amount by which CA PMA Chargeback increases the Charge value.
Adjust Charge: The charge derived by subtracting the Debit Amt and adding the Credit Amt to the Charge value.
Budget: The budget amount allocated for the specified record.
% of Budget: The percentage of budget used up to this point.

The example below shows how to select budget, debit, and credit information in a detail report:

Set the Summarize and Break fields to N

Set the Bud/DBCR option to Y

 +-----------------------------------------------------------------------------+
 | Type information for Query CPUCOST   Desc CPU COST FOR PER.1-3    Table TAB |
 |                                                                             |
 | Summarize N  Break N  Bud/DBCR Y           Version : PROD PROD              |
 |                                  Brk Srt                             Op Sum |
 | ORD Type . . . .                   N      Rec Count                   > SUM |
 | Charge Element .                   N      Units  . .                  > SUM |
 | Qualifier  . . .                   N      Rate . . .                  > AVG |
 | Period Number  .       thru        N      Charge . .                  > SUM |
 | DIVISION . . . .                   N                                        |
 | DEPARTMENT . . .                   N                                        |
 |                                                                             |
 |   F2=Exec   F5=Save   F6=Delete   F11=Refresh                               |
 +-----------------------------------------------------------------------------+

The next panel shows the results of this query.

The result of this query is a detail report with the Debit Amt, Credit Amt, Adjust Charge, Budget, and % of Budget fields displaying values based on the result records selected during query execution. Note the following:

CA PMA Chargeback displays all the fields of the CCCTAB table, since this is a detail report. Because we did not specify selection criteria, CA PMA Chargeback displays all of the records of the CCCTAB table. The example below shows two of these records.
For Period Number 1 (see A below), the application applied no debits or credits. Therefore, the adjusted charge value is equal to the charge value (see B below).

The Budget amount for the Techwrit department for the Period 1 and Charge Element CPU-CHARGE is –50 (see C below). Department Techwrit used only .01% of their budget allocation (see D below).

+-- CBQQRYD ----------------- Charge Query Results ---------------------------+

|       Panel   Exit   Help                       1.                          |

| --------------------------------------------------------------------------- |

|  Table  . . . . . : TAB                  More: + - 1     Row: 0001 of 0021  |

|  Version  . . . . : PROD PROD                                               |

|  ORD Type . . . . : MBJ               Record Count :                1       |

|  Charge Element . : CPU-CHARGE        Units  . . . :          1.00000       |

|  Qualifier  . . . : SHIFT1            Rate . . . . :           .50000       |

|  Period Number  .A.: 1               Charge . . . :           .50000        |

|  Modifier . . . . : WEEKEND           Debit Amt  . :          0.00000       |

|  Split/Modified . : NY                Credit Amt .B.:        0.00000        |

|  DIVISION . . . . : RESTON            Adjust Charge:          0.50000       |

|  DEPARTMENT . . . : TECHWRIT          Budget . . .C.:       50.00000        |

| % of Budget :D. .01000 |

+-- CBQQRYD ----------------- Charge Query Results ---------------------------+

|         Panel   Exit   Help                    2.                           |

|---------------------------------------------------------------------------- |

|  Table  . . . . . : TAB                  More: + - 1     Row: 0002 of 0021  |

|  Version  . . . . : PROD PROD                                               |

|  ORD Type . . . . : MBJ               Record Count :                1       |

|  Charge Element . : CPU-CHARGE        Units  . . . :          2.00000       |

|  Qualifier  . . . : SHIFT1            Rate . . . . :           .50000       |

|  Period Number A.. : 2               Charge . . . :          1.00000        |

|  Modifier . . . . : WEEKEND           Debit Amt  . :          0.50000       |

|  Split/Modified . : NY                Credit Amt B. :        0.00000        |

|  DIVISION . . . . : RESTON            Adjust Charge:          0.50000       |

|  DEPARTMENT . . . : ACCOUNTING        Budget . . C. :       50.00000        |

|                                       % of Budget  D. :       .01000        |

|                                                                             |

|   F7=Bkwd   F8=Fwd                                                          |

+-----------------------------------------------------------------------------+