Pega Reports with percentile function

General
,
1

Hello Support,

We have a requirement to generate a pega report that shows the 96 percentile of elapsed time for a service request type in 10 minute intervals.

Using below sql query we are able to extract the information but how to implement it on Pega reporting definition.

By default there are no function(fx) available for percentile calculation. Could you please guide us to implement this requirement.

SELECT date_trunc(‘hour’, pxCreateDateTime) + date_part(‘minute’, pxCreateDateTime)::int / 10 * interval ‘10 min’ as Interval,
percentile_disc(0.96) within group (order by TotalElapsedTime asc)
FROM pegadata.WORK
where pxCreateDateTime>=date_trunc(‘day’,Current_date-10) and ServiceRequestType in (‘TTcreate’)
group by date_trunc(‘hour’, pxCreateDateTime) + date_part(‘minute’, pxCreateDateTime)::int / 10 * interval ‘10 min’

2

Hi @AnupS724,

To calculate the 96th percentile in Pega Reporting Definition, you can use a combination of SQL custom function and custom rule.

Step 1: Create a SQL Custom Function

  1. In Pega, go to System > Data Model > SQL Custom Functions.
  2. Click Create Custom Function.
  3. Enter a name for the function, such as Percentile96.
  4. Enter the following SQL code as the function body:

sql
CREATE FUNCTION Percentile96(array float) RETURNS float AGGREGATE ORDER BY ORDERED
LANGUAGE SQL AS $$
select QUALIFY percentile_disc(0.96) WITHIN GROUP (order by $1 asc) from UNNEST($1)
$$;

Step 2: Create a Custom Rule for Report Definition

  1. In Pega, go to System > Data Model > Custom Rules.
  2. Click Create Custom Rule.
  3. Enter a name for the rule, such as ReportPercentile96.
  4. Enter the following formula as the rule expression:

java
SqlAggregateCall Percentile96 = “Percentile96” ( {TotalElapsedTime} );

where TotalElapsedTime is the field containing the elapsed time values.

Step 3: Use the Custom Rule in Reporting Definition

  1. Create a new Reporting Definition.
  2. In the “Data Source” tab, select “SQL Query”.
  3. In the “Query” textarea, enter a query similar to the one you provided:

sql
SELECT
date_trunc(‘hour’, pxCreateDateTime) +
date_part(‘minute’, pxCreateDateTime)::int / 10 * interval ‘10 min’ AS Interval,
ReportPercentile96(TotalElapsedTime) AS Percentile96ElapsedTime
FROM pegadata.WORK
WHERE
pxCreateDateTime >= date_trunc(‘day’, Current_date - 10) AND
ServiceRequestType IN (‘TTcreate’)
GROUP BY
Interval
ORDER BY
Interval;

Step 4: Add Percentile Field to the Report

  1. In the “Fields” tab of the Reporting Definition, add a new field.
  2. Set the “Data Source” to “Query Function”.
  3. Select the “Percentile96ElapsedTime” function.
  4. Set the “Data Type” to “Decimal”.
  5. Optionally, you can set the “Format” to display the percentile value in a specific format.

By following these steps, you can create a Pega Reporting Definition that calculates the 96th percentile of elapsed time for a service request type in 10-minute intervals.

Hope it helps.

Thanks

Megha