Create Report on sctask table for 10 business days before created date

[vivek72]

Hi,

I need to prepare below 2 reports and then calculate percentage using a formula indicator:

I have a catalog item with 2 variables : joining date and target date

1. First report should show all SCTASKS created 10 business days (excluding Sat, sun and holidays etc) before joining date.

2. second report should show all SCTASKS created 10 business days (excluding Sat, sun and holidays etc) before joining date and closed before target date.

Please suggest how shall I make such a report, I need to put these 2 reports in a formula indicator to calculate total %.

Thanks,

Vivek

[Its_Azar]

Hey there @vivek72 

I think you can start with creating First Report (SCTASKs Created 10 Business Days Before Joining Date
- Navigate to the Reports module in ServiceNow.
- Click on "New" to create a new report.
- Select the "Tabular" report type.
- Set the table to "SC Task [sctask]".
- Add a filter condition to include only records where the `created` date is within the last 10 business days before the `joining date`.
- Exclude weekends (Saturday and Sunday) and any holidays from the calculation of business days.
- Customize the columns to display relevant information such as task number, short description, created date, joining date, etc.

Now create Second Report (SCTASKs Closed Before Target Date)
Add filter conditions to include only records where the `closed` date is within the last 10 business days before the `joining date` and closed before the `target date`.
 Again, exclude weekends and holidays from the calculation of business days.

3. finally we can calculate Total Percentage Using Formula Indicator
- After creating both reports, you can use a formula indicator to calculate the total percentage based on the number of SCTASKs meeting the criteria in each report.
- Define the formula to calculate the percentage of SCTASKs closed before the target date out of the total SCTASKs created 10 business days before the joining date.
- Add this formula indicator to your dashboard or report to display the calculated percentage.

If tihs helps kindly accept the response trhanks much

Best regards,
Azar

[vivek72]

Hi @Its_Azar : Thanks for replying !

I have 3 questions here:

1. "How to Add a filter condition to include only records where the `created` date is within the last 10 business days before the `joining date`." as I don't see any option to put filter conditions using 2 fields/variables and also didn't find any option to check for business days and exclude weekends.

2. Formula indicator works for PA, but 2 reports which I am building here is not being done by PA, will formula indicator works on these 2 normal reports.

3. What about any historical jobs to collect data as PA requires daily and historical jobs also to collect data.

Thanks,

Vivek