Back Forward Report Definitions
Completing the Query tab

Reports category
  1. About 
  2. New 
  3. Query 
  4. Chart 
  5. Report Viewer 
  6. Data Access 
  7. Parameters 
  1. Pages & Classes 
  2. History 
  3. More... 

The Query tab defines the basic content of the report, including the rows and columns to be included on the report. The information on this tab determines the SQL query that will be executed to retrieve the data for the report.

Complete the Edit columns section at the top of the tab to define the columns to include, and how to format and display their values, and the Edit filters section to describe which class instances or records to include.

Edit columns

This section defines the columns in the report. They appear in the report in the same order that they appear in this list. To change the column display order, click and hold the small circle at the left of the column, and drag the column up or down until it is where it should be, then release the mouse button to drop it into its new position.

For each column in the report, the following fields are available:

Field Description
Column Source

Choose a Single Value property, identified by a property reference.

Instead of an existing single value, you can use the Calculation Builder to select a function template and provide database columns for it to operate on. The column in the report then displays the result of the SQL function.

If your application has declare index rules, you can choose one of the properties in the declare index as a column. Type part of the declare index name, then click the down-arrow. The rule appears as one of the "Best Bets".

If you enter the name of property that has not been optimized, your system may experience a performance slowdown when running the report. You may want to use unoptimized properties while working out the report's structure and contents, but make sure all properties are optimized before moving the code to a production system. See How to expose a property as a database column.

In many situations you can use a linked property to populate a column (or the right side of a filter condition; see below). If you are building a report on unresolved work items, you can include a column with the access group of the creator of the work item by using this linked property: .pxCreateOperator.pyAccessGroup.

You cannot reference data page properties that themselves take parameters (such as D_page1[companyName:D_page2[param2:Param.P1],param2:pxRequestor.pyUserIdentifier].pyLabel), although you can directly reference properties in a data page (such as D_page1.companyName).

Column Name

Enter a column heading to be used for this column on the report. By default, the label defined for the selected property appears as the column heading.

This field is required for most columns, but is not required when the Summary Function field has a value.

Summarize Choose one of the built-in functions COUNT(), COUNT DISTINCT(), SUM(), AVG(), MAX(), or MIN() to aggregate the data in the column. Depending on your choice of property for the column, some summary functions may not appear as options.
Sort Type

Optional. For each sorted column, you can choose one of the following:

  • Ascending (default): Values for the column are sorted in ascending order, from lowest to highest.
  • Descending: Values for the column are sorted in descending order, from largest to smallest.
Sort Order Optional. If you are sorting the values for more than one column on the report, enter a unique value for each sorted column as an integer between 1 and the number of sorted columns.

Click the gear icon at the right of any row to select formatting options for that column in the form that appears:

Option Description
Column width

Enter the desired column width for the column as a percentage of the window's width or in pixels. Enter an integer in the first field and, in the second field, select either px or %.

Column format Select a format to use for the values for the column. Leave blank to use the default format associated with the property. Many formats accept parameters.
Display Values Across Columns Available only for summary reports. Check to create a pivot-table style report, in which values for one or more columns display horizontally.
Hide column Available only for list reports. Check to hide the column in the displayed report.

Click the trash-can icon at the right of any row to delete the column from the report.

Click Add column at the bottom of the section to add new columns to the report.

Edit filters

This section defines filter conditions, which determine which rows of data are included in the report. If you leave this section empty, the report selects all instances of the Applies To class (or instances or records of whatever sources are indicated on the Data Access tab) up to the limit in the Maximum Number of Rows to Retrieve field on the Data Access tab. PRPC converts information in this section to an SQL WHERE clause when retrieving data from the database.

It is very important to define appropriate filter conditions in every report to prevent overly expensive reports. Always define filter conditions that are as restrictive as possible, while still including the data needed for the report user.

This section has three areas:

Filter conditions to apply BEFORE aggregation

If you define filter conditions in this section, you must enter a logical expression in the Filter conditions text box above the list of filter conditions. Using the labels entered in the Condition column, enter a logical expression that specifies how to combine the conditions. The expression can include parentheses and the operators AND and OR. For example, if the table contains four rows labeled A, B, C, and D, you can enter an expression like (A OR B OR C) AND D in this text box. The expression must include all labels within the Condition column; that is, it must include every filter condition.

Each row of the array in this section identifies a filter condition. The order of rows is not significant. PRPC converts information in this array to an SQL WHERE clause when retrieving data from the database.

Field Description
Condition

Enter a letter or letters that uniquely identifies this row, to reference this row in the Logic field. These can be as simple as A for the first row, B for the second row, and so on. A Condition can have letters and numbers, but no spaces; and you cannot use the words "And" or "Or", as they are used to relate the various filters.

Caption

Optional: Provide a caption to identify the filter condition in the Filters list that displays above the data when the report runs.

Depending on which radio button you selected in the Filter Caption Settings at the bottom of the section, the caption you set for each condition applies to the column within the condition (if the caption is "customer", then "customer = 'brown'"), or to the entire condition ("customer").

Column source

Enter a property reference for the conditions. The property referenced must be exposed as a database column in the Applies To class for the report value, or else be a fully qualified value to a clipboard page that is always present on the clipboard, such as the requestor page or process page.

Advanced You can specify only Single Value properties in the Column field. Each property you identify for selection criteria must be exposed as a column in the database table.

Note, however, that you can specify single value properties that are produced by a SQL function. To use the result of a SQL function query, click the Calculation Builder icon sql function icon to the right of the field. The Calculation Builder displays, and there you can select a SQL function and specify any parameters it needs.

NoteIf the Type of this property is TextEncrypted, special instructions apply to the Condition and Value fields. See Property rules — Implementing and using the TextEncrypted type.

NoteIf you enter .pxObjClass here and the Applies To key part of this report definition is a framework class, and if the developer has checked the Report on Descendant Class Instances check box in the Report Definition form, at runtime the report execution can use the corresponding implementation class, not the framework class, for comparisons and report content. This feature eliminates the need to copy each framework class-based report definition into the implementation class.

Relationship Select a logical operator for the comparison condition, such as Is Equal or Starts With.
Value

Enter one of the following options as a comparison value:

  • A literal constant, formatted in accordance with the property type. See Constants in expressions. To compare the Column value with multiple literal constants, surround each with double quote characters and separate each with a comma. Place a backslash character \ before any double quote character that appears within the constant. As described below, you can click the magnifying glass icon in some situations to review current clipboard values for the property identified in the Column field.
  • A fully qualified property reference to a Single Value property that is present on the clipboard at runtime. The Type of this property must match the Type of the property in the Column field.
  • A fully qualified property reference to a Value List or Value Group property present on the clipboard at runtime. The Type of this property must match the Type of the property in the Column field. To compare the Column value with multiple Value List or Value Group properties, separate each property name with a comma.
  • A reference to a parameter that is defined in the report's rule, in the format param.name.
  • The name of another Single Value property that is exposed as a column. This allows comparison of the values of two properties in the same instance (database row), if the types are identical or comparable.
  • a calculation. To compare with the result of a calculation, click the Calculation Builder icon sql function icon to the right of the field. The Calculation Builder displays, and there you can select a SQL function and specify any parameters it needs.
  • A symbolic date, for a Date or DateTime value. When the list value rule executes, this symbolic reference is converted to an actual date or date range based on the time zone of the user and the Condition value. For example, if the user selects Last Year and the Condition value is IS EQUAL TO, the result is a date range between January 1 and December 31 inclusive, of the previous calendar year.

If the Relationship field is set to IS NULL or IS NOT NULL, leave this field blank.

Commas correspond to an OR test: if the Value field contains two or more entries separated by commas, the comparison is true at runtime if the value of the Column field equals (or is greater than, or is contained in, and so on) any one of the entries.

When the Value field contains a Value List or Value Group property reference, the comparison is true at runtime if the value of the Column field is true for any element in the list or group.

When evaluating either of the two Condition values Greater or Greater or Equal at runtime, the result is the same as applying the test to the largest value in a list or group. Similarly, when evaluating the Condition values Less or Less or Equal at runtime, the result is the same as applying the test to the smallest value in the group or list. The Value List and Value Group options are most useful for other condition values, such as Contains or Starts with.

Using the Select Values pop-up window

For guided assistance in completing this field, click the magnifying glass icon . A pop-up window appears with one or more of these tabs:

  • Available Values — The system searches the column in the database corresponding to the property in the Column field to collect and display up to 1,000 values for the Column property in a new window. To add literal values to the Value field, check one or more boxes and click  OK. (You can also type literal constants directly, whether or not they appear on this list.) This tab appears at runtime only to users who hold the standard privilege @baseclass.ShowStoredValues. For such users, it is visible at runtime when the Display Available Values? box in the Prompt Settings area is selected.
  • Valid Values — The system presents values determined by the Table Type specification on the General tab of the property referenced in the Column field. To add literal values to the Value field, check one or more boxes and click  Apply . This tab appears at runtime when the Display Valid Values? box in the Prompt Settings area is selected.
  • Compatible Columns — The system lists exposed properties with the same Type as the property in the Column field, as candidates for this field. This tab appears at runtime when the Display Compatible Columns? box in the Prompt Settings area is selected.
  • Time Periods — For a property of type Date or DateTime, allows the selection of a symbolic date such as Yesterday or Current Year.

Click the gear icon at the right of any condition row to edit filter options:

Option Description
Field value key Provide the field value key used for localizing the pick-values pop-up window (described above) that appears in the Report Viewer when the user makes changes to filter conditions.
Report Viewer options

Select a value to determine how a user of this report can modify the condition in this row when the report is run and displayed in the Report Viewer.

  • Allow any changes- this is the default. The condition appears in the Filters link in the header of the report. The user can change any column, relationship, or value in the filter
  • Allow changes to values only - The condition appears in the Filters link in the header of the report. The User can only change the Value field within the condition.
  • Read only, allow no changes - The condition appears in the Filters link in the header of the report, but cannot be changed by users.
  • Filter not visible in Viewer - The condition is not displayed in the Filters link in the header of the report, and cannot be changed by users.
Use Null if empty

This option is important only when there is no value at runtime specified in the Value field. By default, when the Value field is empty at runtime, the criterion defined by this row is ignored; processing is the same as if this row is not present.

For example, assume the criteria is that the customer's last name starts with a given letter, and the value set in the Value field is param.Letter. If this report rule executes at a time when Param.Letter has the value "C", this criterion limits the report to contain only instances in which the Lastname property value starts with the letter C. However, if the same rule executes again when the Param.Letter parameter has no value, this criterion is dropped, so instances with any Lastname value (or none) are selected.

In situations where this default behavior is not desirable, select the Use Null box to force the Condition value to become Is Null when the Value value is blank at runtime. In the above example, the criterion is transformed to "Lastname is null" — which is different from having no restriction on Lastname.

Ignore case

Select to have runtime comparisons of the Field value and the Value value be case-insensitive; that is. "Smith" matches "SMITH" and also matches "sMiTH".

If selected, then at runtime "a" matches "A" and is less than "B".

Clear to cause tests of the Field value and the Value value to occur without case conversion. In this case, "a" is greater than both "A" and also greater than "B".

In most cases, do not select this option. Select this box only when necessary to obtain the rows of the report:

  • If your PegaRULES database is hosted by Microsoft SQL Server, comparisons are always case-insensitive, selecting this box does not affect the report contents, but may add processing.
  • If your PegaRULES database is hosted by Oracle, IBM UDB or most database vendor products, selecting this box may affect which rows appear in the report when values occur in mixed case. Conversion to uppercase can significantly slow database processing.
  • Case conversion is meaningful only for properties of type Text, Identifier, or Password. Regardless of database software, case conversion is not needed for numbers, dates, or text that contains only uppercase or lowercase characters.

Filter caption settings

This control lets you determine, by checking the appropriate radio button, whether the caption you set for each condition (such as "customer" applies to the left side of the condition ("customer = 'brown'"), or to the entire condition ("customer").

Filter conditions to apply AFTER aggregation

If your report includes an aggregate column, you can add filters the system applies after calculating aggregate values. Using these filters you can, for instance, display the rows that fall below or between some minimum (severity < 50) or maximum (rating > 75) aggregate value.

If you define filter conditions in this section, you must enter a logical expression in the Filter conditions text box above this list of conditions, as described above for the first set of filter conditions.

For each filter condition fields these fields are available. You do not provide captions for these filter conditions, as they do not appear in the display in the Report Viewer; nor the column source since it is the aggregate column of the report.

Field Description
Condition

Enter a letter or letters that uniquely identifies this row, to reference this row in the Logic field. These can be as simple as A for the first row, B for the second row, and so on. A Condition can have letters and numbers, but no spaces; and you cannot use the words "And" or "Or", as they are used to relate the various filters.

Function

Select the aggregation function for the aggregate column on which you want this filter to operate. If the function is COUNT and the column label is "customer", the function option appears here as "COUNT(customer)" to distinguish it from functions related to other aggregate columns in the report.

Relationship Select a logical operator for the comparison condition, such as Is Equal or Greater Than.
Value

Enter one of the following options as a comparison value:

  • A literal constant, formatted in accordance with the property type. See Constants in expressions.
  • A fully qualified property reference to a Single Value property that is present on the clipboard at runtime. The Type of this property must match the Type of the property in the Column field.
  • A reference to a parameter that is defined in the report's rule, in the format param.name.
  • The name of another Single Value property that is exposed as a column.

Commas correspond to an OR test: if the Value field contains two or more entries separated by commas, the comparison is true at runtime if the value of the Column field equals (or is greater than, or is contained in, and so on) any one of the entries.

When the Value field contains a Value List or Value Group property reference, the comparison is true at runtime if the value of the Column field is true for any element in the list or group.

When evaluating either of the two Condition values Greater or Greater or Equal at runtime, the result is the same as applying the test to the largest value in a list or group. Similarly, when evaluating the Condition values Less or Less or Equal at runtime, the result is the same as applying the test to the smallest value in the group or list. The Value List and Value Group options are most useful for other condition values, such as Contains or Starts with.

Additional options may appear:

Control Description
Remove Duplicate Rows For list-type reports, when this option is selected, the report discards rows that have duplicate values for the columns selected for the report. This action does not modify the stored data.
Filter By Rule Resolution

For Report Definitions defined on the Rule- class, or a class derived from Rule-, check this check box to restrict the rule instances included to those that satisfy PRPC's rule resolution procedures. Circumstancing is ignored.

Filter By Application Context

For Report Definitions defined on the Rule-class, or a class derived from Rule-, check this check box to restrict report coverage to those in a certain application context. When you check this check box, additional fields appear to let you set application context filters:

Field Description
Context Mode

Select one of the available modes to limit the report scope to:

  • All Rules in Application
  • All Rules in Application and Frameworks
  • All Rules in Application and Frameworks except PegaRULES
Application name Select the application on which to filter.
Application version Select the application version.

When using the application context filtering option, you can add and sort report results by the values in a hidden column like pyRulesetVersion.

Top/Bottom Rank

For list reports, you can limit the number and type of rows of data the report returns to those with the highest or lowest values of some property:

As an example, for a report that shows the three most recently-entered cases for each customer, sort the results by customer, check Group results to group the results by customer, and then in the Top/Bottom Rank section enter:

Display Top ranked 3 Rows For each group based on .pxCreateDateTime

Related topics Working with the Report Viewer
Working with the Chart Editor
Atlast Atlas — Standard Report Definitions
definition Filter

UpAbout Report Definitions