Report Definition – Understanding in Pega
In this blog article, we will see in detail about report definition rule, how to create a new RD and its usage.
Update: The concept of Report Definition rule remains the same across different Pega versions. Most of the screenshots in this blog article were reused from Pega 7 version and few were updated. Use this blog article to learn the concepts and you can try the entire tutorial in the latest versions as well.
I love the term ‘Data’. Every business process requires some data. Think of any organization – bank, finance. They collect the data and use the data for their business processes.
Every BPM application requires some data for processing.
Let’s take the usual example – Amazon. They may need the customer data, product data, vendor data, courier transportation data etc. They can store these data in some database (Oracle, MySQL, Postgres, etc) and use the data when required in their applications
Now how can Amazon application talk with the database? They need some language right?!
Any application can talk with the database using Structured Query Language (SQL)
Pega stores the permanent data in database tables and temporary data in memory.
So Pega talk to the database using SQL. There is a rule type Connect-SQL to support this.
So what is the use of report definition here?
I would say report definition is a User-friendly version of connect-sql rule 😊
Important note: Report definitions can only be used to browse the record from the database table. It cannot be used to alter/update/delete the records.
What is a report definition rule?
– A report definition rule generates SQL query to retrieve data from the database tables and can generate HTML to display the results.
– Reports definition supports chart and can be used in dashboard reports.
– Report definition can retrieve data from two or more tables.
– Report definition supports parameter and can be rendered dynamically.
Where can we refer to a report definition rule?
a) Data pages – as a source to fetch multiple results. ( page list).
b) Used in many UI controls – Table layouts, list, autocomplete
c) Called from activity – Rule-Obj-ReportDefinition.pxRetrieveReportData
How to create a new report definition rule?
Before continuing let me explain my requirements.
Scenario – I need to fetch the details – pyID, CustomerName and PhoneNumber related to Case S-1.
Select pyID,customername,phonenumber from pc_pks_myknowpega.com where pyid=S-1.
Below is my SQL query.
Let’s see how we can achieve the same using report definition rule
1. Determine which table we need to browse
We know that all concrete classes in Pega are mapped to database tables. We need to check which instances we need to browse and from which table.
Here, we need to browse the Sales class instances, Let’s open the class and check to what table it points to?
It is pointing to pc_PKS_myKnowPega table. ( required table).
So we can create a report definition rule on the Sales class instance.
Step 1: Records -> Reports ->new
Applies to – Decides on which instances we are going to report.
Here in this demo, I am going to report on all the sales cases in the application. So my applies to class is ‘PKS-MyKnowPega-Work-Sales’
We completed the below part.
2. Determine what are the columns you need to report
There are 5 Important tabs in a report definition rule.
a) Query tab
b) Chart tab
c) Report Viewer
d) Data Access
e) Parameters
Query tab – Edit columns.
In this block, you can specify an array of columns you need to fetch from the database table and can also decide the presentation for each column.
Column Source – here you got 2 options.
a) Use single value property – You can either use optimized or un-optimized columns ( affects performance, totally not recommended)
b) Use calculation builder to use a function template and format the column value.
We will see about SQL functions in a separate blog article.
Now save it and run the report definition rule.
You get all three sales cases from the database table.
The other 3 columns in the ‘Edit Column’ block, determines the UI representation of the results.
a) Summarize
-Count, Count distinct, Max, Min, Sum, Avg .
This list varies based on the property type. Obviously for the property pyID of type ‘text’, we don’t need the Sum and Average summarization 🙂
We will see in later about the differences between List reports and summary reports.
b) Sort type – It can be blank, Lowest to highest (ascending) and Highest to lowest (descending).
c) Sort Order – With in the sort type you can determine the priority of sort order. It can be 1-N.
Imagine I am fetching the country and state in the search results. I gave country as Sort order 1 and State as sort order 2.
You can see in the below picture, sorting is first applied to country and then within the country sorting is applied for states.
You got a Settings icon on each row. Let’s check it. There are few additional options available.
Column width – You can determine the Column width in px or % ( dropdown option)
Column format – You can determine presentation of the column values.
Hide column – You also got an option to hide column.
Filtering option –
Auto – Displays both list and search range.
List only – No search
Search range – No lists. Only search option.
When you select summarize, you get different additional options.
You get an option to display values across columns. Now you know, how to include the columns and format the results.
3. Determine and configure the filter conditions.
Condition – you can specify the condition logic label. If there are more than one condition in where clause, then you can differentiate them using labels.
Caption – Description on the filter condition displayed in the report viewer. We will see more in detail later in this post.
Column source – You can specify the column name on which you are going to apply the filter condition. You can also use calculation builder to use some functions.
Relationship –
You can make use of any arithmetic operations J
Value – you got variety of options.
a) You can either hardcode the value or get the parameterised value dynamically.
b) Select Value – You can click on the link to select the available values ( Internal query executed to get all the data).
This list all the available pyID values for the sales instances. You can select one.
c) Calculation builder – you can also make use of the function to render the calculated values.
d) Other Filter Options.
Report Viewer Changes –
What is report viewer?
When you run a data transform, you get a window to display the results.
a) Allow any changes – Default, we can update both the column and its values
b) Allow changes to value only – We can update only the column values
c) Read only, allow no change – We cannot update the filter conditions
d) Field not visible in Viewer – Filter section is not visible
We will test
Use Null if empty –
Imagine we have a where condition where the value is passed dynamically. Say the filter condition is pyID=Param.pyID
If Use Null if empty – True – then the where condition will be .pyID=””
If Use Null if empty – False – then the entire where condition is ignored.
Ignore Case – You can also ignore the case ( case sensitive or not) for the values.
Filter conditions to Apply –
If you have more than one filter rows, then you can use the logical operators to satisfy the requirement.
Update: for a summary report, you get an option to use filter condition after aggregation
Here you can make use of aggregate column as filtering option.
Filter Caption conditions –
a) Allow caption to entire condition
b) Allow caption to left side of condition
Allow caption to entire condition – Instead of displaying pyID=”S-1” as a filter condition, we can use a caption to give some meaningful description.
I am using FirstCase as a caption to Condition A.
Run the report, you can see the caption in the filter conditions.
Allow caption to left side of condition – You can allow the caption to only left side of condition.
Like FirstCase=S-1
Remove duplicate rows –
True -Removes duplicate rows in the search results.
You can see in the below picture that S-1 & S-3 contains same customer name and phone number.
Step 1: Add only customername and phonenumber as select columns. For testing remove all the filter conditions
Step 2: check the Remove duplicate rows checkbox.
Step 3: Run the report definition.
You can see only 2 reports, because a duplicate row is removed.
Ranking –
You can rank the results and filter and display only few results.
You can either select Top ranked (highest value) or bottom ranked (lowest value).
You can specify the number of rows to retrieve.
You can use either OverAll – To use all the search results in ranking; For Each Group – Ranking within the group
Based on – You can specify any field to rank.
Scenario – Here I need to fetch and display recently created Sales case.
My ranking criteria will be Top ranked = Highest values = maximum created date
Run the report.
You can see it returned the recently created sales case S-3.
Scenario for ‘For each group’
Scenario – You need to fetch and display the operators who are getting the highest salary in their department ( Sales, Service)
You can enable ‘For each group’ Also you need to do some configuration in Report viewer tab.
Check group results and decide what are the columns can be group headings.
In the above picture, I selected 1 – 1, So the sort order = 1 column will be the column heading.
Run the report, by select ‘For each group’ in ranking.
– You can see ranking is done based on the phone number grouping.
– S-1 and S-3 have same phone number, but the highest ranked case is S-3 (recently created) J
End of first tab.
Chart tab, Report Viewer – Explained in a separate blog article.
Data access tab
Use this tab to control all the data configuration settings.
We will look into a separate blog article on Class Joins, Declarative Index Joins, Associations, Sub reports.
What are the other data configuration options?
Maximum number of rows to retrieve – This is disabled, because paging I enabled in the report viewer tab. If you need to set maximum record count, then you need to disable paging and set the value.
Maximum elapsed time in seconds – You can specify the maximum number of seconds, the report is allowed to run. If does not return the results in the specified time, then the run is interrupted.
Maximum number of rows for export – Specify the maximum number of rows to export.
Maximum elapsed time in seconds for export – You can specify the maximum number of seconds the export process is allowed to run.
You can see the default configuration values above.
Report on descendant class instances –
What is a descendant class?
Say my class is PKS-Myknowpega-Work-Sales, then I can say PKS-Myknowpega-Work-Sales-Fashion is a descendant class to sales class.
On selecting this option, I can fetch all the descendant class instances too.
Include Implementation class only – It used only Implementation layer class mapping.
Include all descendant classes – this is default select and include all instances irrespective of layering.
Use alternate database – You can use an alternate database table to report on.
Note: Alternate database table details are specified in the database table instance
Row Key settings –
On selecting this option, the row key (pzInskey) will be default added in the select columns.
Security –
You can specify a privilege rule. The operators with the specified privilege can only run the report!
Parameters tab
This is the most commonly used tab.
Introducing parameters can support reusability. At run time we can pass different values to the parameter and get the result.
Step 1: Add a parameter ID
Step 2: In the Query tab – filter conditions add the Parameter value as condition value.
Step 3: Only for testing, set the parameter default value to S-1
Step 4: Run the report definition rule.
You can see the results. You can pass this parameter dynamically at run time.
How to debug a report definition rule?
1. After the report definition rule is run, You can check a page ‘pyReportContentPage’ in the clipboard.
Update: This page may not visible in the higher pega version. Instead, you can use the second option
You can also see the pxResults pagelist to see the results content.
2. Run the tracer, enable the DB query option in tracer settings. This will capture the query in the tracer.
