Question
Classify the records/list in the Report Definition based on business days
Hi All,
I need to select the records in the report definition with falls on business days. The logic to display the records is developed in Report definition and all the records that is displayed should be in business days. Could you please let me know whether any OOTB functionalities are available in report definition to classify the records based on the business days or any function that could support the scenario.
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Northbridge
CA
Hi,
No OOTB functionality helps achieve your requirement.
You'll have to create a function alias in "Embed-UserFunction" which should contain the SQL code to modify the specific date column to be classified as a business day/non-business day.
The SQL code will be specific to the DataBase server you're using. For suppose in Postgre, the below code helped.
(CASE
WHEN to_char({1}, 'day') = 'Saturday' THEN 'Not A Business Day'
WHEN to_char({1}, 'day') = 'Sunday' THEN 'Not A Business Day'
ELSE 'Business Day'
END)
Just in case for MS SQL, Datename(weekday, date) is the function which returns the weekday.
And this function alias, after saved properly will be seen in the drop-down associated with fx, seen beside the column source of report-definition.
This source code in function-alias just replaces the specific column of the DB query that gets fired when the Report-Definition is run.
For the above code, the associated SQL query will be formed like
Hi,
No OOTB functionality helps achieve your requirement.
You'll have to create a function alias in "Embed-UserFunction" which should contain the SQL code to modify the specific date column to be classified as a business day/non-business day.
The SQL code will be specific to the DataBase server you're using. For suppose in Postgre, the below code helped.
(CASE
WHEN to_char({1}, 'day') = 'Saturday' THEN 'Not A Business Day'
WHEN to_char({1}, 'day') = 'Sunday' THEN 'Not A Business Day'
ELSE 'Business Day'
END)
Just in case for MS SQL, Datename(weekday, date) is the function which returns the weekday.
And this function alias, after saved properly will be seen in the drop-down associated with fx, seen beside the column source of report-definition.
This source code in function-alias just replaces the specific column of the DB query that gets fired when the Report-Definition is run.
For the above code, the associated SQL query will be formed like
SELECT (CASE WHEN to_char("PC0"."pxcreatedatetime", 'day') = 'Sunday' THEN '1' WHEN to_char("PC0"."pxcreatedatetime", 'day') = 'Saturday ' THEN '2' ELSE 'Business Day' END) AS "pyTextValue(1)" , "PC0"."pzinskey" AS "pzInsKey" FROM data.pc_O5GI0L_test1_Work "PC0" WHERE "PC0"."pxobjclass" = ?
Tata Consultancy Services Ltd
AU
Thank you Shruthi Mukka for the quick response. I was looking to filter pxCreateDateTime based on the business days and holidays for the display purpose. In addition, I am working to get the logic to find the difference between the DateTime attributes (inclusive of the Business Days and Holidays). I did not get the direct functions (OOTB) in the Function Alias for the highlighted scenarios.