Report Definition - Text property holding date values. Need to provide search criteria on date ranges like 'Yesterday' or 'Past 30 days' on this property
An Embedded page in the pyWorkPage contains a Text property where it holds Date values. Now, we are looking to provide search criteria option on this property to user where he can get the report data for 'Yesterday' or 'past 30 days'. we have raised an SR-A90558 on the same and were suggested to reach Pega Product Support. Could you please help us resolve this issue.
***Updated by Moderator: Lochan. Removed user added Ask the Expert tag. Apologies for confusion, shouldn't have been an end-user option***
Note that symbolic values like 'Today', 'Yesterday', 'Last 30 days' etc. only work with PRPC property type as 'Date' or 'DateTime'.
One option would be to create a new property (of type Date or DateTime) whose value is populated from the existing property (declare expression maybe) and then save that to a new column in the table. Use this property in your reports with symbolic values.
Alternatively, you can create SQL function which converts the String to Date (or DateTime) and then use with symbolic values in a report. Note that this would not perform well.
Rajiv, I have taken the first option to create a DateTime property and on the 'Onchange' event of UI of the Text property (which holds the Date value), I am using a Data Transform to set the new Date Time property value by converting using @pxFormatDateTime. It is converting properly and saving to clipboard and work object as '20160812T040000.000 GMT'.
However, when I use it in the report definition the new DateTime property is not showing 'Symbolic Date' options (like - last day, last 30 days) when clicked on 'Select Values' of filter options. It still shows the old string value options.
Rajiv, the column type in database table is TIMESTAMP(6).
The issue is the old Text property (which holds the date value) and the DateTime property both lie in an embedded class of the work class. when the new DateTime property or any other Date/DateTime property which lies in the embedded page are reffered inthe report created in work class - the 'Select Values' is showing 'Available Values/Valid Values' which is usually shown for any Text property. However, when the report is created in the Embedded page class the 'Select Values' is showing correctly.
I believe this is a pega defect. Could you please check once.
So if I understand correctly, the report definition is using an embedded property which is not optimized as a column in the filter section and select values is not showing the right values? Any specific reason you are using an unoptimized property in your filters in report definition because that would cause performance problems.
No, I was using a optimized embedded property. And after we are setting values to the property they were visible, however when used in filter section - Select Values was showing string values rather than date calendar values.
I got a solution for this issue by placing the new property at 'pyWorkPage' level and in the action set of property I am setting the coverted date value to that property and when used in report criteria, the Select Values were showing correct values.
Overall, this is a PEGA defect that it is not showing correct Select Values for embedded date/datetime values.