Reported for Customer Service for Healthcare (CSHC) 7.2.1 on Pega 7.2.1 and the Pega-provided report PegaCPMHC-Work CPMHCGetRelatedServiceItemsDetails in the ruleset PegaCPMHC:07-21-01 and called to populate the D_CSHCInteractionServiceCases Data Page
Although the issue was reported for CSHC 7.2.1, the problem and its solution pertain to the Pega 7 Platform, Pega 7.2.1.
In a CSHC Interaction’s View 360, see Recent Interactions and click a Recent Interaction to see a list of Service Cases created in the course of this Interaction. However, the Pega-provided report CPMHCGetRelatedServiceItemsDetails does not show the Service Case, even though one was created during the course of the Interaction. You can find this service case in the database usimg the appropriate query.
According to the Pega 7.2.1 Help topic, Report Definitions, the Data Access tab, the option "Report on descendant class instances", when you select this option and the "Include all descendant classes" suboption, the Report Definition retrieves all the instances belonging to the class it is defined on and all the instances of this class's descendant classes. This is achieved by creating UNION statements in the generated SQL query.
But these options did not work with a report created from the Pega-provided Report Definition: The report did not include all descendat classes. To work around this unexpected result, you make a copy of (Save As) the Pega-provided Report Definition and customize it, selecting the option "Include all descendant classes".
You also check the mappings and ensure that all the property references in the Report Definition are optimized.
Again, the report returns an empty list of Services Cases.
Your Tracer indicates that the correct Report Definition is called (with option "Include all descendant classes" selected) and that no UNION statement is created in the generated SQL query. In addition, clicking Actions > Run (to run the report), displays the error message.
Error MessagesPartial Results shown. Some results cannot be included as data is not optimized. Contact technical support for assistance.
Steps to Reproduce
- In an Interaction’s View 360, the Recent Interactions are displayed.
- Click a Recent Interaction and expect to see the list of Service Cases created in the course of this Interaction.
No Service Cases are displayed.
Most of the existing Report Definitions prior to Pega 7.2.1 do not use the new option, "Include all descendant classes".
Different Service Cases are stored in different database tables. Therefore, when you run the report using the options "Report on descendant class instances" > "Include all descendant classes", the system expects all data to be optimized. For the case reported, although the MemberID property is optimized, some table columns corresponding to some descendent classes had a data type specified as Varchar(32) in the database, while others had a data type specified as Varchar(64). Hence, the descendant classes were treated as not optimized, and the report did not retrieve the information for them.
Perform the following local-change:
Change the data type of the MemberID column to VARCHAR(64) in all tables corresponding to the descendent classes whereever it was VARCHAR(32) in the database.
Most of the existing Report Definitions (prior to Pega 7.2.1) do not use the new option introduced in Pega 7.2.1, "Include all descendant classes". Therefore, you first have to copy (Save As) any Pega-provided report to enable this option.
Also, double check that each and every property used in the Report Definition is actually optimized (database column is exposed) in each and every database table that is mapped or be potentially mapped to any of the descendant class instances that you need queried by the Report Definition. Especially, make sure that each of these properties is mapped to columns with exactly the same type in the database tables. You will experience issues if, for some unknown reason, you have a property corresponding to exposed columns in different tables with most columns set at VARCHAR(64) and other columns set at VARCHAR(32).Related Contenthttps://pdn.pega.com/sites/default/files/help_v72/procomhelpmain.htm#rule-/rule-obj-/rule-obj-report-/rule-obj-report-definition/dataaccess.htmhttps://pdn.pega.com/community/product-support/question/report-definition-not-getting-instances-descendant-classes