Side by Side bar chart

[Deshan1]

Hi Guys,

I want to generate a report which shows the number of tickets created and closed on each day.

Something like this:

NOTE: The graph should show each day in the x-axis regardless of whether a ticket was created or not. So it is simply a trend of the open/closed ticket count over the calendar days.

Will someone be kind enough to show me how to generate this report please?

Thank you so much for the kind support.
Kindest Regards,
Deshan

[Deshan1]

Hi Tushar,

Will this capture the trend for past data as well?

Because I think this will only capture the data for that day only ya?

Thanks,
Kind Regards,
Deshan

[Tsura Andreeva]

Have you tried creating a dataset on main report trend by created day and on the dataset trend by closed. The sample is for current month

[Deshan1]

Hi Tsura,

Thanks a lot for the reply. I already tried this. But the managers seems to want to see all the dates of the month (like 03-28-2020 etc.) for the whole month.

This approach seems to be grouping the dates homogenously and presenting in the x-axis.

Appreciate if u can help me with achieving something similar. Perhaps in PA if not in mainstream reporting.

Thank you for the help,
Kindest Regards,
Deshan 

[Tsura Andreeva]

I get it, so if there are no records your report will not show that day but your manager still wants to see the report as calendar month and that day no matter if it has records or not. Currently we do not use PA on my end so I will not be much help giving ideas there. 

[NikEng1 · Accepted solution]

To show both opened and closed tickets, you'd use a report with two datasets as others have mentioned.

Reporting wont show a date for which no records are opened or closed. Logically, that date does not exists in the tables you are reporting on.

For this requirement, I would recommend using Performance Analytics. Since PA collects a score daily, there will be a score of 0 for those dates when no tickets were opened or closed. Simply set up two indicators, one for opened and one for closed, and show them in a time widget. Dates with a score of 0 will be shown in the x axis.

If you really want to do this without using PA, you could use database views. You'd need to create a custom table with one row for each date, and probably a scheduled job that runs at midnight and adds a new date row to the table. Then join your task to that table twice, once for closed_at matching the date and once for opened_at matching the date.
If you use left joins for the task tables, you'd still end up with a row for dates with no task records matching. Then you could use two data sets grouped by the date field of your custom table's date field from the DB view, each with a count of the closed and opened task sys_id columns grouped by date. This could work in theory, but it's not something I'd recommend. PA would be much better.