Question
BNY Mellon
IN
Last activity: 7 Jul 2020 10:00 EDT
How to refer Business Calander in "The difference in days between" function in Report Definition Field?(Pega 7.4)
Hi,
I have a column in Report Definition that will calculate the days between Current date and DeadlineTime date. I use the "The difference in days between" function in the calculation builder in the
Report Definition, but I need to refer business calendar so that only working days will be counted.
How do I refer the calendar in this function. Is there any other way to do this, Can I write a custom function to refer the calendar?. See attached screen on Report Definition Calculation Builder.
***Edited by Moderator Marissa to update SR Details***
Accepted Solution
Pegasystems Inc.
CA
You must have saved-as the base version of pxDifferenceInDays. You need to check the circumstanced version specific to Oracle if Oracle is your database.
Pegasystems Inc.
FR
Hello,
Can you send today's date as a parameter to your report. The help file is also stating: Use the Calculation Builder when you want your report to show values that are not available as properties. For example, if you have a date property but you want your report to show the day of the week, use the "day of the week" function.
BNY Mellon
IN
Hi,
Thanks for the response. But, how would having the today's date as parameter will help to fix this issue? Can you elaborate the solution.
I want to calculate the difference between two dates with respect to our business calendar. Result will be shown in new column. Is there a way like the below expression can be referred in RD?
@differenceBetweenDays(.pxDeadlineTime,@CurrentDateTime(), true, "default")
Thanks
Pegasystems Inc.
US
Hi Janardhan
Can you try to use the BusinessCalendar. differenceBetweenDays function to achieve what you want?
BNY Mellon
IN
Hi
As I mentioned earlier, I am aware of this function, my question is how do I refer this in Calculation builder in Report Definition. I do not see any function with Business Calendar listed in Calculation Builder. I see all the function displayed in Calculation Builder are from Embed-UserFunction class.
How do I get this function there @differenceBetweenDays, is there any other way to refer this in Calculation Builder? Please help to fix this.
Thanks
Pegasystems Inc.
CA
The @differenceBetweenDays function is a Java function. You can only reference SQL functions (Function Alias rules in Embed-UserFunction class) in Report Definitions. You will have to write your own custom SQL function to calculate business days.
Pegasystems Inc.
CA
It depends on the database you are using. For example, I found this on the net for Postgresql. https://dba.stackexchange.com/questions/207701/count-business-days-between-2-dates-in-postgresql
You will have to create such a function in your database and call it within Function Alias rule in Pega. You can reference any function in Embed-UserFunction class.
BNY Mellon
IN
Thanks Praneeth,
I have created the SQL Function (Screenshot attached) but I am not able to call it from the Function Alias created in Embed-UserFunction. I tried few JSP code but no luck. Do you have a sample code that call SQL Function from Function Alias?
Thanks
Janardhan
Pegasystems Inc.
US
Hi Janardhan
Please review this troubleshooting video and the referenced link to see if this can help you.
https://community.pega.com/knowledgebase/articles/how-create-custom-sql-functions-reporting
BNY Mellon
IN
HI,
I have tried SaveAs of pxDifferenceinDays Function Alias as "CalDifferenceInDays" and referred in Report Definition without changing anything, but I got the below error. Am I missing anything here?
Please check the screen shot attached. Let me know how pxDifferenceinDays works fine and when SavedAs CalDifferenceInDays and referred it in RD it throws error in spite of having the same config and source.
An error occured on executing the query for the report definition - There was a problem getting a list:
code: 904 SQLState: 42000 Message: ORA-00904: "DAYS": invalid identifier DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException:
ORA-00904: "DAYS": invalid identifier | SQL Code: 904 | SQL State: 42000
Thanks
Janardhan
Accepted Solution
Pegasystems Inc.
CA
You must have saved-as the base version of pxDifferenceInDays. You need to check the circumstanced version specific to Oracle if Oracle is your database.
CNO Services LLC
IN
Hi Janardhan,
I have a same requirement for my project. Would you please let me know what code changes you modified inorder to calculate days based on Business calendar in the pxDifferenceInDays Function Alias? Or please share the Source source for the same. Appreciate your help.
Pegasystems Inc.
US
If you want to account for holidays as well as weekends in your count, you will have to maintain a table in your database of the business holidays for your locale. You can then create an auxiliary function alias like "IsBusinessDay" which which returns 0 or if the input date is a Saturday, Sunday or in the holiday table, or 1 if it is in neither. You can then call this from the main function alias to count the business days between 2 dates.
This is the only way I know of to do this.
CNO Services LLC
IN
Thanks much for the prompt response. As i am not familiar in writing function, Would you please help share the Source code for implementing the same in FunctionAlias? Your help is much appreciated.
CNO Services LLC
IN
Thanks Janardhan for the prompt response. We are also trying to exclude only the weekends from the count, as of now. But not sure of the code to include the same in the Function Alias rule. Can you please share us the Source Code for the same? Your help is much appreciated.
BNY Mellon
IN
((ROUND({2} - {1}))-(((NEXT_DAY ( {2} - 7, 'SUNDAY') - NEXT_DAY ( {1} - 1, 'SUNDAY'))+(NEXT_DAY ( {2} - 7, 'SATURDAY') - NEXT_DAY ( {1} - 1, 'SATURDAY')))/7)-2)
-------------------------------------------------------
Where{1} - Earlier Date Value
Where {2} - Later Date Value
I have done this as single statement.
CNO Services LLC
IN
Thank you Janardhan for the code. I have added the same in the FunctionAlias but getting the below error while executing the Report definition. Looks like it is not finding the ROUND function. Did you add this function in DB side explicitly or it was exists already?
Error:
An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: 42883 Message: ERROR: function round(interval) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 539 DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: function round(interval) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 539 | SQL Code: 0 | SQL State: 42883
CNO Services LLC
IN
Can you please confirm the input format for these 2 parameters?
I believe the functions ROUND() is of input NUMBER. But you mentioned the parameters are in Date value.
In this case, ROUND({2} - {1}) will be accepted?