Question
LTIMindtree
IN
Last activity: 6 Jun 2023 23:31 EDT
Report Definition not accepting Date value in where clause
Hello,
I have used Data page to show the cases in the user work queue. In data page , I have used report definition as a source to fetch the results. But I am not able to see any results in user work queue because of the below error.
In where clause of the report definition, I have used date property in left hand side and used the Pega provided date option "Today" in the right hand side with relationship as less than.
Ex: TestDate < Today
When I try to run the RD with above condition, following exception is getting triggered and same has been captured in logs as well.
---------------------------------------------------------------------------------------------------------
Error:
An error occured on executing the query for the report definition - Database column <unknown> in database pegadata, table <unknown> has an unknown type: JDBC Type: Date
Displaying 0 records
-------------------------------------------------------------------------------------------------------------
Based on the observation, Function "Today" has been considered as datetime whereas not date. Tried the below the approach but it didn't worked.
1. Tried to pass current date as parameter.
2. Tried to pass a page which contains current date property.
Kindly share your suggestion to resolve the issue.
Thanks,
Seetharaman
Accepted Solution
Updated: 6 Jun 2023 23:31 EDT
ING Nederland
NL
Hi @Seetharaman, your observation is correct you cannot directly compare the date property with "Today" or any other OOTB values because they work only for date time properties. Also, I don't think there's an OOTB function which does the conversion of date to date time within the RD itself.
The only possible solution that I could imagine was to have a new exposed property, let's say CurrentDate, it will be linked to a declare expression which in return will calculate current date using the @CurrentDate() function and this property can be then used on the right hand side of your RD's condition.
Virtusa
IN
@Seetharaman Hey,
Did you optimized this Date property? and are you seeing the column in DB table?
CPF
SG
@Seetharaman you can use Data Page with Data transform.
In Data transform set the currentdate to date property. and refer the Data page in Filter right side value as "Datapage.DatePropertyName". Make sure in pages and classes define the data page
LTIMindtree
IN
Hi @Renuka,
Yes I tried this approach. If we run the Rd standalone with above approach its working fine. But during run time again we are getting the same error message.
Updated: 6 Jun 2023 5:01 EDT
Bits in Glass
IN
@Seetharaman if your requirement is to display assignments created before today,
then please use
.pxCreateDateTime< Today
If based on cases create date , please join report with work class and
Work. pxCreateDateTime< Today
If you are trying to pass current date, then current date<today will not give any results.
We have function alias to convert text to datetime, but no function alias to convert date to datetime.
If you want to use current date in left hand side(column source), of filter ->
Please click on fx-> and choose function-> The current system date and time as a Datetime value
LTIMindtree
IN
Please note that my requirement is not to show cases based on creation date. "Test Date" which I have mentioned is a clipboard property specific to Work object.
Also I passed the current date as param to right side of the where clause not to the left side. As "Today" is considered as datetime.
Left Side - TestDate --> Date property
Right side - Today --> Pega provided
TestDate < Today --> 06/01/2023 < 06/06/2023
Functions provided in the where clause are related only to Datetime whereas not to date. Also it is not accepting the customized function in the right side . Only populated functions can be used.
Please suggest how to compare 2 dates ( only date) in the where clause of the report definition.
Bits in Glass
IN
1. Comparing datetime with datetime
I would suggest you to use ootb today filter in the rhs, that is datetime and in lhs-> your property converted to text and that converted to datetime.
So you would need a property holding text value of your date property, and then you can use function alias to convert that text value to datetime.
https://support.pega.com/question/date-string
2nd-> comparing date with date
You can do this way also. Converting current datetime to your date format and comparing them both
https://support.pega.com/question/how-convert-datetime-specific-date-format
Accepted Solution
Updated: 6 Jun 2023 23:31 EDT
ING Nederland
NL
Hi @Seetharaman, your observation is correct you cannot directly compare the date property with "Today" or any other OOTB values because they work only for date time properties. Also, I don't think there's an OOTB function which does the conversion of date to date time within the RD itself.
The only possible solution that I could imagine was to have a new exposed property, let's say CurrentDate, it will be linked to a declare expression which in return will calculate current date using the @CurrentDate() function and this property can be then used on the right hand side of your RD's condition.
LTIMindtree
IN
Hi @SaurabhG7134,
Yes this approach is working. Now I am able to view the results in work queue.
Thanks for the help!