Custom report on how long task has been opened

emma_hunt · ‎10-05-2018

We have a request process that the completion of the task needs to happen in 3 days. Our customer wants a dashboard to display a pie chart that shows all those tasks that have been opened for 1 day, 2 days, 3 days, and more than 3 days. They want each amount a different color (1 day being green, 2 being yellow, 3 being red, and more than 3 being grey).

I tried to group by the "Due Date" field on the catalog task table, but I got many different time options (< 2 months, 1-2 months, etc [see picture below]) but I need that paired down to just 4 options (what I listed above).

Does anyone have any thoughts?

LaurentChicoine · ‎10-10-2018

First thing, to understand you're requirement well, you say the task must be completed in 3 days and that you are planning on using the due date. Does that mean that you automatically set the due date to 3 business days in the future automatically by script?

Report ranges don't support grouping for days in the future (not really impressive indeed). So you could use the date of creation to fix this, however this does not solve the business duration thing.

I see 2 options for your requirements.

Option 1: Use a Gauge of type Counts

Count gauge allows you to set multiple queries as counts. That way you can have a query for each of your groups. You could create a group for 3 days left, 2 days, 1 day, overdue (or as many group as wou which).

For the query of each count gauge you would use a filter on the due date (or created date) that would use a javascript function script include that calculate the begin and end of each span. Let's say, for the 1 day query: Due date between now and [Date in 1 business day]. So your script include would calculate 1 day from now in business day, so that query will be dynamic. I could help you build the script include and condition (you need to update the query via script as you cannot use javascript function using the condition builder).

Gauges allows drilldown but are not as graphical as a bar or pie chart.

Option 2: Custom choice field updated by schedule job

Create a custom choice field with your required grouping and use a scheduled job to update that field at your required frequency. That way you can use a script to calculate if the task is overdue, due in 1 business day, due in 2 business days, etc.

Instead of a choice field, you could also create a custom duration field for the business duration left before due date (and create the report range associated with it). This will also require a scheduled job to update and will never be accurate. A calulated field is not really an option as from my knowledge it will use the value of the last record update for the grouping (as this is what is stored in the database). A function field using datediff() could have been a reliable option but you require to calculate business days.

Another option that does not fit the requirement of grouping would be to simply use a Column report (from Time series) and Trend the report by due date. That way you can see how many tasks are due for the upcoming days.

View solution in original post

Matthew Glenn · ‎10-05-2018

Look into creating some custom report ranges and chart colors.

This should get you started down the right path

LaurentChicoine · ‎10-05-2018

Matthew is right that you should look for report ranges. Only thing to be aware of is that report ranges are global, they will be applied to all reports on the configured field.

What we chose to do is to create a database view (not joining any table, just including the table we want to report on) whenever we need a custom report range. That way we can set report ranges and build reports on that database view without affecting other reports.

emma_hunt · ‎10-09-2018

Hi Laurent, I was able to create the database view and the report ranges for this (woot!) but now I have realized that the report ranges won't take "Business days" into account for the duration field, which is what that group requires. Also it seems like the report ranges that I have created only check dates in the past and not in the future (I am trying to report on due date that are 1, 2, 3 days in the future but it seems to want to do 1, 2, 3 days in the past).

Any thoughts?

LaurentChicoine · ‎10-10-2018

First thing, to understand you're requirement well, you say the task must be completed in 3 days and that you are planning on using the due date. Does that mean that you automatically set the due date to 3 business days in the future automatically by script?

Report ranges don't support grouping for days in the future (not really impressive indeed). So you could use the date of creation to fix this, however this does not solve the business duration thing.

I see 2 options for your requirements.

Option 1: Use a Gauge of type Counts

Count gauge allows you to set multiple queries as counts. That way you can have a query for each of your groups. You could create a group for 3 days left, 2 days, 1 day, overdue (or as many group as wou which).

For the query of each count gauge you would use a filter on the due date (or created date) that would use a javascript function script include that calculate the begin and end of each span. Let's say, for the 1 day query: Due date between now and [Date in 1 business day]. So your script include would calculate 1 day from now in business day, so that query will be dynamic. I could help you build the script include and condition (you need to update the query via script as you cannot use javascript function using the condition builder).

Gauges allows drilldown but are not as graphical as a bar or pie chart.

Option 2: Custom choice field updated by schedule job

Create a custom choice field with your required grouping and use a scheduled job to update that field at your required frequency. That way you can use a script to calculate if the task is overdue, due in 1 business day, due in 2 business days, etc.

Instead of a choice field, you could also create a custom duration field for the business duration left before due date (and create the report range associated with it). This will also require a scheduled job to update and will never be accurate. A calulated field is not really an option as from my knowledge it will use the value of the last record update for the grouping (as this is what is stored in the database). A function field using datediff() could have been a reliable option but you require to calculate business days.

Another option that does not fit the requirement of grouping would be to simply use a Column report (from Time series) and Trend the report by due date. That way you can see how many tasks are due for the upcoming days.