====== Reporting with pagination from the back-end ======

Pega's data pages support pagination. In sections for classic ui and views in constellation pagination is supported and accessible using ui features.  

If you want to query a database table defining pages, for instance call a datapage with pagination from a data transform or activity to provide the data via a (REST) service, 
then pagination is not so obvious. 

Configuring a data page with report definition referencing the datapage from data transform or activity does not provide the tools/parameter inputs necessary to use pagination. 

There are however ways you can use pagination from the back end. 

===== Options =====

  * In recent plaform versions its possible to use the Constellation DX API v2 data_views API. This api provides pagination features. [[https://docs.pega.com/bundle/dx-api/page/platform/dx-api/dx-api-overview.html | dx api overview (docs.pega.com) ]]
  * Connect SQL rule (deprecated as data page source) see [[https://docs.pega.com/bundle/platform/page/platform/reference/rules/about-connect-sql-rules.html | About connect sql rules (docs.pega.com) ]]
  * Use Activity Rule-Obj-Report-Definition.pxRetrieveReportData in combination with a report definition

This article will describe the third option to use a report defintion in cobination with the pxRetrieveReportData activity. This option is in my view a balanced approach, compared to SQL connect it is for instance not necessary to know the exact database column names and no need to know SQL. In both cases knowledge about db optimization is necessary. Regarding the pega dx api, in the back end it uses the pxRetrieveReportData activity as well (when the indicated data page is sourced by a report) so in a sense using that option just adds more overhead (connector configuration in design time and api processing in run time). 

===== Using Activity Rule-Obj-Report-Definition pxRetrieveReportData =====

pre-requisites: 
  * Report definition defining the query to run. (Take care of optimizing properties as needed)

Approach:

Create a wrapper activity or perhaps a data transform that will serve as entry point (data source) and takes care of setting up context input parameters and pages, map the results to a desired format/location. This rule wil call the pxRetrieveReportData activity to get the desired data. 

The pxRetrieveReportData takes a couple of input parameters of which pyReportName and pyReportClass are required (refencing the report defintion sourcing the data).
// Also the parameters are passed to the report defintion, so any selection parameters (where clause) can be passed to the activity to take effect. // 

To query data with pagination it is necessary to provide a page with additional parameters:
   * This page must be of type Embed-QueryInputs. 
   * The default name of the page expected is pyReportParamPage but a different name can be used //(make sure to indicate the name with parameter pyReportParamPageName when calling pxRetrieveReportData)//


There are roughly two approaches for controlling the amount of returned results:
   - indicate pages and page size -> results in meta data indicating how many pages exist and how many results exist in total. 
   - indicate rowindex and rowcount -> results in meta data indicating with pxMore true/false if there are more records existing and does not indicate total amount of records.  

 
//The second approach seems useful for situations where you dont need to know the total amout of records, like progressively showing results until end user is satisfied. So like when scrolling down or just having a button (more/next). 
When you want to provide the entire set of data in consumable chunks then approach 1 seems more usefull so that its clear how many records to expect. It provides the possibility to show how many pages exits and select a specific page directly.//
 

==== Approach 1: Paging ====

Properties on pyReportParamPage

^Name^Value^|||
| pxObjClass|Embed-QueryInputs||||
| pyPagingSettings|^Name^Value^^
|:::||pxObjClass|Embed-PagingSettings||
|:::||pyPageIndex|2||
|:::||pyPageSize|3||
|:::||pyReturnResultCount|True| if false: pxTotalResultCount and pxPageCount not populated (will indicate 0) |
|:::||pyPagingEnabled|True| returns all results if False |


Results on content page:

^Name^Value^
| pxTotalResultCount | integer value indicating total records selected by the report/query |
| pxPageCount | integer value indicating number of pages given current page size | 
| pxPageSize | integer value indicationg number of rows per page |
| pxResultCount | integer value indicating number of current results |
| pxResults | page list containing the requested records/data |


==== Approach 2: Rowindex ====


Properties on pyReportParamPage

^Name^Value^
| pxObjClass|Embed-QueryInputs|
| pyStartIndex | //integer value // |
| pyRowCount | //integer value // |
| pyMaxRecords  | //optional, possible to override the max limit configured on the report definition // |

Results on content page:

^Name^Value^
| pxTotalResultCount | not available (always indicates 0) |
| pxMore | True/False |
| pxResultCount | integer value indicating number of current results |
| pxResults | page list containing the requested records/data |


<note>This article focusses on the inputs relevant to paging. There are more parameters that can be used to influence the query results. </note>
<note> Rule-Obj-Report-Definition pxRetrieveSearchData activity is also available providing option to use search to retrieve records.</note>
<note>Pega Common (data model) application uses REST api's sourced of which the servce get methods are implemented using report defintions, looks like they use the querying logic similar to how the pega application views api interact with reports via datapages. Looks a bit complicated though. The pega common data model training provides excersize systems that have the common app implemented</note>

{{tag>[Pega Reporting Pagination Rule-Obj-Report-Definition Back-End]}}