Report Definition in Pega

Introduction

In this post, we will see in detail about report definition rule, how to create a new RD and its usage.

  • 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 need the customer data, product data, vendor data, courier transportation data etc.
  • They store these data in some database (Oracle, MySQL, Postgres, etc) and use the data when required.

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 ruleJ

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 a report definition rule?

a)      Data pages – as a source to fetch multiple results. ( page list).

b)      Used in many UI controls – repeating grid, list, autocomplete

c)       Called from activity – Rule-Obj-ReportDefinition.pxRetrieveReportData

How to create a new report definition rule?

Before continuing let me explain my requirement.

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 🙂

  • 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. ( equired 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’

You completed this part 🙂

  • 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

Step 1: 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)

b)      Use calculation  builder to use a function template and format the column value.

I will make a short post on, how to create a SQL function and use one 🙂

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.

  • 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  – We will see more in detail in separate post.

Data access

  • Use this tab to control  all the data configuration settings.

I am making a separate post 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 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.

You can also see the pxResults pagelist to see the results content.

2. Run the tracer and capture the query.

Let me end this post 🙂

 

22 thoughts on “Report Definition in Pega

  1. in filter condtion here we mentioning value = ‘ s-1’ so casse s-1 values only filtering but the user enter any value how we define the empty value in the filter condition
    ex: where pyid= s-1 bur the user enter any s-1 or s-2 how it fetch this value and retrun

  2. At the place where you say the table is pointing to pc_PKS_myKnowPega I don’t see anything saying that. Instead it looks like it says that the table is mapped to data.pc_PKS_MyKnowPega_Work in PegaData. Is there a mistake or type somewhere?

  3. Thank you Prem for your post on report definitions, waiting for your Case Management Post.

    I have a small query in report definitions, I have read in many posts that we can use un-optmized properties in column source part of EDIT COLUMNS block but cannot use them in column source of EDIT FILTERS block.

    But when i tried using un-optimized properties as column source in EDIT FILTER block in pega 7.3 they are working fine without any errors. Can you please clarify on this.

    Below are the backend queries generated by pega for both the scenarios :

    Scenarion 1: Un-Optimized property in Edit Columns block

    SELECT data.pr_read_from_stream(‘UnExposedProperty_Name’,’pzInsKey’,’pzPVStream’)
    FROM table_name;

    Scenario 2: Un-Optimized property in Edit FIlters block

    SELECT column_name
    FROM table_name
    WHERE data.pr_read_from_stream(‘UnExposedProperty_Name’,’pzInsKey’,’pzPVStream’) = ?;

    Thanks in Advance

    1. Hi Az,
      In report definition, you can use un-optimized columns in filtering.
      pr_read_from_stream is a standard UDF function specific to Pega and comes with the Pega package. But this will affect your performance.

      To your question, you can always use un-optimised column

Leave a Reply

Your email address will not be published. Required fields are marked *