Press "Enter" to skip to content

Report Definition in Pega



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 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.


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.
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 🙂


  1. Rakesh Rakesh

    Waiting for deployment post bro..!!

    • Premkumar G Premkumar G

      I’ll post about it soon bro. Stay tuned. 🙂

  2. vinod vinod

    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

    • Premkumar G Premkumar G

      Hi vinod, to satisfy your requirement I mentioned using parameter to dynamically pass the filter condition.

  3. Murali Murali

    Thank u…prem.u given valueble information to us…

    • Premkumar G Premkumar G

      You are welcome. 🙂
      Thank you so much, Murali. 🙂

  4. Srikanth Srikanth

    Hi Prem,

    This tutorial is awesome.
    But I didn’t have real-time experience with reports.
    Could u please suggest me exactly for which scenarios we use RD in projects.


  5. Auditya Auditya

    Hi Prem, very nice post, thanks a lot for very brief explanation. 🙂
    I have small requirement. Can you please help me how to acheive this using RD?
    I want to display Grading in RD, I have created a RD where there are total number of resolved cases for each operator will be displayed. Based on the total number of resolved cases, i have added a new column where GRADE will be displayed. For example, if the total number of resolved cases are 10 for a particular operator and that operator has resolved 9 cases, then that operator should have GRADE-A, as that operator has more than 90% resolved cases. I want to calculate grading based on the below table. If the resolved cases are between:
    90 – 100 –> GRADE A
    80 – 89 –> GRADE B
    70 – 79 –> GRADE C
    Below 70 –> GRADE D
    Based on the above calculation, grade should be displayed for each operator in that RD.
    For this requirement, do I need to create my own function rule and use that function in calculation builder? Please suggest. Thanks.

  6. Surath Surath

    Well explained, Thanks.

    • Premkumar G Premkumar G

      Thank you and you are welcome, Surath. 🙂

  7. Parvez Parvez

    very well explained, thank you very much…….kindly request you to make a post on case management as well

    • Premkumar G Premkumar G

      You are welcome, Parvez. 🙂
      Yeah, I’ll post about case management soon. 🙂

  8. Gaurao Gaurao

    How can I have non-exposed un-optimized property in the filter condition? This is when I am not supposed to expose/optimize that particular property.

  9. Wayne Wayne

    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?

    • Premkumar G Premkumar G

      Hi Wayne, thanks for pointing it out :). It should be data.pc_PKS_MyKnowPega_Work. Here data refers to schema.

      • Wayne Wayne

        Great! Glad I wasn’t going crazy and glad I could help. Happy New Year!

        • Premkumar G Premkumar G

          Thank you so much, Wayne.
          Happy New Year, Wayne!!

  10. ips ips

    Very nice and useful. Thanks a lot for clearing the concept.

    • Premkumar G Premkumar G

      Thank you so much for your appreciation, Ips. 🙂
      You are welcome.

  11. Az Az

    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

    • Premkumar G Premkumar G

      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

    • Az Az

      Thank you so much for your response.

      • Premkumar G Premkumar G

        You are welcome, Az. 🙂

    • Srikka Srikka

      May I know where to check Select queries plz for unoptimized properties??

  12. Archisman Ghosh Archisman Ghosh

    Hi Premkumar,

    Loving your articles.
    Can you please elaborate on the options of Report Viewer options: 1.Allow any changes 2. Allow changes to value only. 3. Read only 4. Filter no visible.
    Not able to understand the difference of results for these 4 options.

    Thanks in Advance

  13. Thenuzan Thenuzan

    Nice Post!!

    I saw an error in What is report viewer?
    In the description it should be Report Definition instead of Data Transform.

    Please check it.

  14. Shivam Khandelwal Shivam Khandelwal

    Could you please list out differences between List View , Summary with Report definition.
    Why Report Definition is adopted , major differences.

  15. Murali Murali

    Hi…Prem Can you explain to me what is the difference between datapage and Report defination?

  16. shaik azmathulla shaik azmathulla

    Thanks for explaining each and each option 🙂


    • Premkumar G Premkumar G

      You are welcome, Shaik. 🙂

  17. Eshwar Eshwar

    Hi Prem,

    You are saying that “We can use un-optimized columns as filter conditions in RD” as answer to Az question.

    Can we really use un-optimized columns as filter conditions in RD?

    Thanks in Advance 🙂

  18. Meghana Meghana

    What is the difference between obj-browse vs report definition

  19. kamal kamal

    can u pls make a post on SQL Function

  20. NA NA

    Hi Prem,

    Nice explanation.I have one doubt in General Data access settings when we provide Maximum elapsed time.Can we show or write any message to logs or generate any communication when the report did not complete in provide time.
    Can you explain how we can handle errors.


  21. Ashirvad Ashirvad

    Can you explain the difference between data type and report definition?which is more advantages to use

  22. sharath sharath

    Thanks very usefull

    • Premkumar G Premkumar G

      You are welcome, Sharath. 🙂

  23. Mukkram Mukkram

    Hi, requirement is I want display the report based on there role
    Expand: manager can we all rows and columns.
    User only columns
    Operator only reports.
    How to achieve this.

  24. JS JS


    We need to fetch a data from a single table, then which method will be preferable reportdefination or obj-browse?

  25. sai sai

    How to fetch Single record from the data table in User interface?
    by click event(button/radio button/ check box)?

  26. Vivian Richard Vivian Richard

    Extremely Good Introduction to PEGA Report Definition.

  27. srilakshmi srilakshmi

    Thank you,can you please tell me the What is px, py, pz in pega?

  28. Ratna Ratna

    hi Prem

    how to call Activity in report Definition

  29. Dan Bell Dan Bell

    Hey Prem,

    Your articles are very helpful, appriate your effort.
    Can you let me know “What are hidden filters in RD?”

  30. Avinash Tankala Avinash Tankala

    Thank you for detailed information on RD.

  31. Asha Asha

    Well explained… I have understood cleary..can you please post a topic regarding charts in report defination with practical examples?

Leave a Reply

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

error: Content is protected !!