Question

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***

Group Tags

Correct Answer
December 10, 2019 - 6:38am

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.

Comments

Keep up to date on this post and subscribe to comments

December 2, 2019 - 2:59am

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.

December 2, 2019 - 4:02am

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

Pega
December 4, 2019 - 1:00pm

Hi Janardhan

Can you try to use the BusinessCalendar. differenceBetweenDays function to achieve what you want?

 

December 4, 2019 - 11:25pm
Response to mahab_GCS

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

December 5, 2019 - 12:26am
Response to JanardhanK0965

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.

December 5, 2019 - 1:19am
Response to PraneethPurighalla

Thanks Praneeth.

Do you have a sample or a reference that I can use to create the custom sql function.

Thanks

Jana

December 5, 2019 - 4:37am
Response to JanardhanK0965

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.

December 5, 2019 - 9:39am
Response to PraneethPurighalla

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

Pega
December 5, 2019 - 1:03pm

December 10, 2019 - 5:46am

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

December 10, 2019 - 6:38am
Response to JanardhanK0965

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.

Pega
December 10, 2019 - 9:37am

As Praneeth stated already, this is what you should be using for your DB (see screenshot). 

 

 

December 17, 2019 - 11:47pm

Thank You Mahab and Praneeth, I was able to write new Alias Function for this. Thanks again for you timely help.