Report Definition not getting instances from descendant classes
Hello.
First thing first, context : customer-designed implementation and framework built on CSHC 7.2.1 over Pega 7.2.1
When launching an interaction, in the the View 360, there is a list of previous interactions performed for the related member (or provider).
When lcicking on one of these interactions, the list of the service cases which were started in the scope of this previous interaction is displayed.
Problem: this list is almost always empty.
I think I tracked the problem down to the PegaCPMHC-Work CPMHCGetRelatedServiceItemsDetails Report Definition.
While the right option is checked in this RD it seems it is not "searching down" in descendant classes to retrieve instances.
And, of course, most service cases are not instances of PegaCPMHC-Work or of classes inheriting from PegaCPMHC-Work and belonging to the same class group but are instances of classes which are inheriting from PegaCPMHC-Work but are belonging to other class goups.
So basically in he database, most service case instances are stored in several tables.
Still, with the "Report on descendant class instances" checked, I was expecting (as it is specified in the documentation) that the RD was going to search in each and every table corresponding to all the class groups defined by classes inheriting from PegaCPMHC-Work with some UNION statements generated in the SQL query running "behing the scene".
Was I wrong when I expected just that?
Accepted Solution
Pegasystems Inc.
FR
OK.
I opened a SR on this one and here is the deal:
Most of the existing RD (meaning not new ones introduced in new versions) are not using the new "On all descendant classes" option. So you first have to "save as" any OOTB report you want to have this option on.
Second, doublecheck that each and every property used in the RD is actually optimized/exposed in each and every database table that is mapped to any of the descendant class potentially searched into by the RD. And make especially sure that each of these properties are mapped to columns with exactly the same type in the database tables. I had issues because, for some unknown reason, I had a property which was corresponding to exposed columns in different tables and while most of these columns where varchar(64) others were varchar(32).
Pegasystems Inc.
US
I don't believe any UNION clauses are generated in the query. As far as I know, when you check the "Report on descendant class instances" option, all it does is wildcard the object class. In other words, it changes:
... = 'PegaCPMHC-Work'
to :
... like 'PegaCPMHC-Work%'
Pegasystems Inc.
FR
Hello.
Here is what is found in the "Data access" tab of the help article when clicking on "Actions > Get help" on a Report Definition rule :
| Report on descendant class instances |
Select this check box to include data from one or more descendant classes of the Report Definition's primary class. If descendant classes are mapped to multiple class tables, the generated query will use UNIONs to include this data. You can select a specific subset of descendant classes to include or exclude by adding a filter condition on .pxObjClass. When selected, the following options are displayed:
Note: Performance may be poor unless all relevant database columns are indexed in all included class tables. |
Hello.
Here is what is found in the "Data access" tab of the help article when clicking on "Actions > Get help" on a Report Definition rule :
| Report on descendant class instances |
Select this check box to include data from one or more descendant classes of the Report Definition's primary class. If descendant classes are mapped to multiple class tables, the generated query will use UNIONs to include this data. You can select a specific subset of descendant classes to include or exclude by adding a filter condition on .pxObjClass. When selected, the following options are displayed:
Note: Performance may be poor unless all relevant database columns are indexed in all included class tables. Caution! If descendant classes are in multiple class tables, the query may fail if the referenced properties are not optimized. |
So, second sentence is :
If descendant classes are mapped to multiple class tables, the generated query will use UNIONs to include this data.
That's why I was expecting UNION statement(s) to be generated in the SQL query "behind the scene".
There is even a special "caution" part which is also implying that instances can be spread over multiple tables and still the RD should retrieve them all if a condition is met concerning property optimization:
Caution! If descendant classes are in multiple class tables, the query may fail if the referenced properties are not optimized.
The help topic is wrong then?
Pegasystems Inc.
US
The following PDN article may shed some light on this:
It basically says that generating a UNION is new behavior as of 7.2, and that previously existing report definitions retain the old behavior (no UNION). If this report was previously existing, it will not generate the UNION in the SQL query.
Pegasystems Inc.
FR
The Report Definition I'm having issues with is PegaCPMHC-Work CPMHCGetRelatedServiceItemsDetails.
It's in RS PegaCPMHC:07-21-01.
So according to the PDN article you pointed and according to your explanation it should create UNION statements in the generated SQL query, right?
But it doesn't seem to do so.
EDIT : ok, I think I finally got it (pretty evident but sometimes the obvious eludes the eye): on the PegaCPMHC-Work CPMHCGetRelatedServiceItemsDetails RD the option is still set to "Include Implementation class only"...
Pegasystems Inc.
US
What version of PRPC are you using? When I bring up the help page for report definitions in 7.1.6, I get the following, which does not mention unions:
| Report on descendant class instances |
If there is any implementation class in the application rule set list, check this checkbox to have the report build its query using the implementation class mapping. This feature eliminates the need to copy each framework class-based Report Definition into the implementation class.
|
This is extended behavior from versions previous to 7.1. If you want the report to be based on the parent framework class, make sure this checkbox is unchecked.Accepted Solution
Pegasystems Inc.
FR
OK.
I opened a SR on this one and here is the deal:
Most of the existing RD (meaning not new ones introduced in new versions) are not using the new "On all descendant classes" option. So you first have to "save as" any OOTB report you want to have this option on.
Second, doublecheck that each and every property used in the RD is actually optimized/exposed in each and every database table that is mapped to any of the descendant class potentially searched into by the RD. And make especially sure that each of these properties are mapped to columns with exactly the same type in the database tables. I had issues because, for some unknown reason, I had a property which was corresponding to exposed columns in different tables and while most of these columns where varchar(64) others were varchar(32).
Pegasystems Inc.
US
Frederi,
Thank you for your expertise, shared in SA-34830 derived from your SR.
https://pdn.pega.com/support-articles/partial-results-shown-data-not-optimized-descendant-classes