One of my colleagues recently posed a very interesting PEGA question : suppose there are two sets of records called A and B (A and B might or might not be of the same class), and the requirement is to create a UNION of these two record sets and show it in a screen as a common list (A+B).
Now, a quick search within PDN told me the same answer over and over again : there is no straightforward OOTB method to get the data like this. The answer which has got best response is that of creating a View in the database , and do a Union of the records directly within the DB. But creation of a view is sometimes inconvenient : in large financial companies, having write access to DB is not always possible , not to mention exporting it to higher environments. Sub Reports and Joins within Report Definition were also attempted, but did not work out.
A small digging within PEGA provided a very convenient solution , and that too Out-of-the-box: usage of Aggregate Sources within Data pages . This functionality allows UNION of disparate data sets (which can be in the form of Report result or Connector response), and merge them directly as a resultset within the Data Page response. The solution is scalable (addition of a record set C would not break it, unlike a DB view which needs to be re-written), and whats more : you can add conditions to the aggregation and make the same Data page work for various data combinations !
If you find any other elegant solutions present for the same use case within PEGA , do let me know…
@VTALUKDAR how can we display it in a report def? I see your requirement is only to get union of two datasets. But how to report on that, if it’s not a view class ? Any thoughts guys.
As per my experience to perform a UNION of different data sets in PEGA, bypass direct database methods. Instead, use PEGA’s Aggregate Sources within Data Pages splunk and you can follow these steps:
Create/modify a Data Page.
Add multiple Aggregate Sources (from Report Definitions or Connector responses).
Implement conditions for diverse data combinations.
The Data Page will then provide the union of datasets.
This approach is scalable and doesn’t require direct database access, making it efficient and adaptable.
@MiaS16923913 This is the same technical use case as the one I implemented. Not sure what is different in this article which might help me, but thanks for sharing !
@Sushilc I dont get the question…Do you intend to use this as a Report somewhere ? If yes, then yes you cannot do it, as the data is not available through one Data Set in a Report Def.
But you have a section, then instead of getting the data from a Report Def, you can easily configure it to get from a Data Page instead.
