Report Definition Data Access tab

Use the Data Access tab to manage class joins and other settings related to accessing data from a database by using a report definition.

  • Define class joins to enable reporting on data from multiple classes or tables. For each class join, define one or more logical conditions that specify how to match or join corresponding instances in different classes. For example:

    MyOrg-CustomerService-Work.ProductID Is Equal MyOrg-Sales-Data-Products.SKU

  • List declarative indexes to allow including embedded properties in the report. A declarative index uses an Index- class defined by a Declare Index rule to store embedded properties in a separate database table, so they can be easily and automatically joined to the class table for the class of the report.
  • View the list of Association rules that are used within the report to add properties in different classes that the report references.
  • Identify and configure connections to the subreports that provide data for this report.
  • Set general data source settings, such as resource limits on the maximum number of rows of data to retrieve for this report.
    Note: You can adjust default settings for all reports on the Reporting > Settings landing page.
  • Define security access privileges for the report.
    Note: You can define property-specific restrictions on the Access Manager landing page, and these restrictions apply to reports run from the Report Browser.

Class Join

Field Description
Prefix Specify a short text string to be used as an alias for the joined class and its properties. The Prefix must be used to qualify all references to properties in the class on all tabs in the rule form.
Class Name Select a class to be the primary class for the join. The Prefix plus this class name specify the joined class.

If this work type is derived from Work-, choose carefully whether you want to join to an implementation class, or to a framework class.

Note: You cannot join to a class in a different database than the Applies To class of the report.
Edit Conditions Click to open the Enter filter conditions form and specify filter conditions that describe how to join or match corresponding instances in the Applies To class of the report and the joined class.

Add rows for multiple filter conditions by clicking the Add join filter link.

Complete the fields for each condition:
Field Description
Condition Specify a text label to identify the condition in this row. The label is used in the Filter conditions field to identify the condition.
Column Select a property within the joined class. This property must be an exposed column.
Relationship Select Is Equal.
Value Select a property in the report's Applies To class.
Filter conditions If you define multiple filter conditions in a join, specify an expression that describes how the conditions should be combined, using the condition labels, AND, OR, and parentheses as needed.

For example, if the form contains four conditions labeled A, B, C, and D, you can enter: (A OR B OR C) AND D in this field. This notation is the same as for the logic statement in when condition rules.

The expression entered must include every filter condition in the list.
Type Accept the default choice Only include matching instances to only include instances in each class that have a matching instance in the other class (referred to in database terms as an inner join).

If you choose another option, you can instead use an outer join for the report in which all instances of one of the classes are included in the report, even if they have no matching instances in the other class:

  • Select Include all rows in <class> to include all qualifying instances of the Applies To class of the rule, even if there is no match found in the prefix class.
  • Select Include all rows in <prefix> to include all qualifying instances of the joined class (identified by the value of the Prefix field), even if there is no match found in the Applies To class.
Note: When you define a join from one class to another, the report results might include multiple entries for each work item, if there are multiple corresponding entries in the other class or table.

Declarative Index Join

This area lists instances of Rule-Declare-Index rules used to access embedded properties from the Applies To class of the report. Normally, this area is read-only and does not need to be modified, because declarative indexes are automatically added to this list as references to embedded properties are added to the report.

Field Description
Prefix Specify a text string to be used as an alias for the joined declarative index class and all its properties. The prefix is assigned to all the properties in the class and used to reference these properties on other tabs of the rule form.
Index Name Select the Index- class created by the Declare Index rule.
Type

Optional. The default choice, Only include matching instances, indicates that the report will only include instances in the Applies To class that have a matching instance in the declarative index (referred to in database terms as an inner join). If you choose a different option, you can instead use an outer join for the report, in which all instances of the Applies To class are included in the report, even if they have no matching instances in the declarative index.

Associations

This read-only area lists any Association rules included in the report. See the Pega Community article When and how to create an association rule to support reporting.

Subreports

Subreports enable results from any report definition (including a report definition defined on a different Applies To class) to be referenced and included in another report definition. Any report definition to which you have access, and which does not have its own subreport, can serve as a subreport.

Using a report as a subreport does not change its definition or design, and you can still run the report directly and independently.

See the Pega Community article When and how to use sub-reports in Report Definition reports.

To add a subreport, click the Add subreport link in the Subreports section. Fill in the three fields:

Field Description
Prefix Specify a unique prefix for the subreport, consisting of letters, numbers, and the underscore character, with no spaces. This prefix must not be the same as the prefix for any other subreport, or for a Class join or Declarative Index join, in this tab.
Class name Select the Applies To class of the report you want to use as a subreport.
Name Select the name of the report to use as a subreport. If you select a report that has its own subreport, a message displays when you click the Configure button. You must select a report that does not have a subreport.

After you add a subreport, click the Configure subreport button to display the Configure subreport use form. Complete the form as follows:

Field Description
Number of rows returned by this subreport? Select either More than 1 Row (the default) or 1 Row. Your selection, together with your selection for the next option, Where will you use this subreport?, determines whether you must specify filter conditions to join or match corresponding rows of the subreport to those in the main report.
Where will you use this subreport? Indicate where columns from the subreport are referenced on the Query tab. Select all options that apply for any of the columns in the subreport:
  • Edit Columns section of Query tab
  • Edit Filters section of Query tab - Column Source
  • Edit Filters section of Query tab - Value
How will you join rows in the reports?
Click the Add join condition link to add filter conditions for joining or matching corresponding rows of the subreport to those in the main report. For each condition provide information in the fields as follows:
Field Description
Label Specify a unique alphanumeric label, with no spaces, for the condition.
Subreport Column Select a column from the subreport.
Relationship Select a relationship statement (Is Equal, Is Greater Than, and so on).
Value Select a property in the main report.
Use the labels to enter an expression in the Conditions field to describe how the conditions are combined, using the condition labels AND, OR, and parentheses as needed. Each filter condition must be referenced in the Conditions expression.

Select one of the options in the drop-down menu to specify whether the report includes:

  • Only matching rows from both reports
  • All rows from the main report, regardless of whether they have a matching row in the subreport
  • All rows from the subreport, regardless of whether they have a matching row in the main report

The default option is Do not match rows.

Note: You must specify join conditions for a subreport that returns more than one row, unless the only reference to the subreport is as a Value entry in the Edit Filters section of the Query tab.
Ignore filter conditions from the subreport in this report Select this option to ignore any filter conditions specified in the subreport. Selecting this option means that only the filter conditions in the main report determine the report results; otherwise, only the subreport results that satisfy the filter conditions of the subreport are included in the main report.
Local names for subreport columns For each column in the subreport, specify an alias (composed of letters, numbers, and the underscore character, with no spaces). The report uses these aliases in its selection menus when listing subreport columns as options.
Subreport parameters If the subreport has input parameters and you want the subreport to have the same values as the corresponding parameters in the main report, then select the Auto-populate parameter values from the main report box. This assumes that these parameters exist in both reports.

Otherwise, specify values for the parameters of the subreport.

General Data Source Settings

You can enter settings for some of these options on the Dev Studio > Reporting > Settings landing page that will serve as defaults for all reports.

Field Description
Maximum number of rows to retrieve Optional. Specify a maximum number of rows of results to retrieve for the report. As a best practice during testing, accept the default value 500. If you leave this field blank, the system enforces a limit of 500 rows.

If you exceed the established limit, the rows that have been retrieved are displayed, and the report header displays the total number of rows. No error dialog or error message display, and an error is not written to a log.

When the Use Paging check box on the User Interactions tab is selected, this value is ignored (and not available). You can advance through pages with no limit.
Maximum elapsed time in seconds Optional. Specify the maximum elapsed time in seconds that the report execution is allowed to run before being interrupted. If you leave this field blank, the system enforces a default limit of 30 seconds.

If the report exceeds the maximum elapsed time, a dialog opens explaining that the report took too long to run and that the filter conditions must be more specific. A report typically runs faster by adding additional filter conditions, or by making the existing ones more constraining.
Maximum number of rows for export Optional. Specify a maximum number of rows of results to be exported. If you leave this blank, the system enforces a limit of 10,000.
Maximum elapsed time in seconds for export Optional. Override the default of 30 seconds if your system generally requires more time to export a report.
Restore defaults Click to clear any custom values and to restore the system defaults for the four settings in this section.
Display unoptimized properties in data explorer Select this check box to have unoptimized properties (properties stored in the BLOB column) display as selection options in the Data Explorer for the Report Editor, in the Calculation Builder, and when defining report filters.
CAUTION:
Selecting an unoptimized property might significantly reduce report performance. See the Pega Community article When to use — and when not to use — unoptimized properties in reports.
CAUTION:
Calculations in reports cannot read data from encrypted BLOB columns. If the BLOB column is encrypted, you must optimize the properties that you want to use.
Report on descendant class instances Select this check box to include data from one or more descendant classes of the Report Definition's primary class. If descendant classes are mapped to multiple class tables, the generated query will use UNIONs to include this data.

You can select a specific subset of descendant classes to include or exclude by adding a filter condition on .pxObjClass.

When selected, the following options are displayed:

  • Include single implementation class – Uses implementation class mapping. When there are multiple implementation classes, this option has no effect. To search multiple implementation classes, create a custom version of the Report Definition that has the "applies to" implementation class.
  • Include all descendant classes – Default for new reports. Includes all descendant classes except as constrained by a filter condition on .pxObjClass.
    Note: Include all descendant classes applies to the Applies to Class. It does not apply to Join classes.
Note: Performance may be poor unless all relevant database columns are indexed in all included class tables.
CAUTION:
If descendant classes are in multiple class tables, the query may fail if the referenced properties are not optimized.
Data retrieval preference

Select the preferred method for retrieving data, either Elasticsearch or the database. Using Elasticsearch optimizes reporting without putting additional requirements on the database.

  • Prefer Elasticsearch index – Select this option to use Elasticsearch. If Elasticsearch cannot be used to retrieve data, the database is used.
  • Use the database (default) – Select this option to use the database.
Reporting database Select Primary data source only to run the report against the standard database. Select Prefer reporting data source if defined the report is run either on the default reports database or the alternate database, if you have specified one in the Data-Admin-DB-Table. For more information, see Setting up a reports database.
Ignore formatting when exporting to Excel Select to ignore the column formatting in the report definition in the Excel spreadsheet.
Ignore formatting when exporting to PDF Select to ignore column formatting in the report definition in the PDF file.
Ignore application skin when exporting to PDF Select to remove the styles that were applied from the application skin when exporting to PDF. This option is useful for improving performance when exporting to PDF. You can add custom styles to pyReport_ExportToPdfStyles.css when selected.

Row Key Settings

Field Description
Get row key If you select this check box, the key of the table (usually the pzInsKey property, for internal classes) is included among the columns listed in the select clause.

By default, this check box is selected. However, when you select the Remove Duplicate Rows option on the Design tab, this check box is cleared. If a unique row key is included, there are no duplicate rows.

Security

Field Description
Privilege Optional. Use SmartPrompt to select the security privileges that are required to run this report. Click the Add Privilege link to add additional privileges.