How to write complex query in report definition ?
Current requirement is to display work table along with another table storing multiple rows of data with pyid. Same report was working fine in List view since we were able to manipulate data.
Given some other modifications, we are converting it to Report definition but it is very restricted in terms of query. Requirement can be satisfy with pivot query, group by query but not in report definition.
Can any one has any idea how to write complex query in report definition ?
requirement:
Work table: pyid
Detail table: pyid, activity. This table has multiple rows based on different activity.
Business wants to see latest 2 activity along with pyid. This can be done multiple way in query but not in report definition.
pyid, activity1, activity2.
Any hint/help will be appreciated.
Publicis Sapient
IN
Hi @PatelAmit
Report Definitions can’t handle complex queries like showing the latest two activities for each pyID in one row, you can try these two approaches:
- Create a database view that uses SQL to get the latest two activities and pivot them into columns — then map this view to an external class in Pega and build your report on it.
- If creating a DB view isn’t possible, use a Data Page with an activity that fetches and formats the top two activities manually and shows them as activity1 and activity2.
Aegis
US
Thaks for update. Meanwhile, DB view can be checked with DBA.
Option 2:
How can one update data set return by report definition? Is there a placeholder allow us to modify result set? Is there way to call data page from report?
HCA Healthcare
US
@PatelAmit Report Definition cannot directly handle complex SQL queries like pivot or grouping multiple rows into columns. To show pyID with the latest two activities in one row (like pyID, activity1, activity2), you can create a database view in the background using SQL with row_number or group by logic to pick the latest two activities per pyID. Then, map this view as an external table or data type in Pega and build a Report Definition on top of it. Another approach is to use a Data Transform or Activity to load and reshape the data using a declare page and then display it in a section. Report Definitions are limited in transformation, so heavy logic should be done outside using SQL or preprocessing methods.