how-to Report request data for current day

[painstarmaximus]

Good morning,

I would like to create a SQL query in the Beta Reports to display the number of tickets raised or closed today.

The only way we can see doing this is using either sysdate or currentdate on h_date_logged, but we cannot seem to get it to work.

I know it is MSQL but could you kindly confirm if this can be done.

Thanks,

Lee

 

ADMIN EDIT: Hornbill uses MariaDb, not MS SQL

[Guest Chaz]

Hi Lee, I've raised this with development to see if we can get some more info for you on this.

If you just want a count, you might be interested in using Measures or Widgets which will allow you capture this information with a query.

[painstarmaximus]

Hi,

Thanks for the reply.

I have tried measures and widgets, as I would like to display the information visually. The problem I see is I can work out the total number of open tickets at the END of the day (I have already created this), but I cannot seem to run it to show the number OPENED on a particular day, and then trend this over a number of days.

I trust this makes sense. If there is a way of doing this, it will get around the challenge I am having.

Thanks,

Lee

[Guest Chaz]

No problem, hopefully we can help some more!

When you say on a particular day, based on your first post, you want that day to always be today? As the day rolls on you want to see the how the number grows and maybe see the trend for the day(s)?

If that's the case, then measures can only capture data at the end of the day/week/month but not on an hourly basis.

[painstarmaximus]

Hi,

I would like to see them on a daily basis. For example.

Mon 25 10 Opened 7 Closed

Tues 26 15 Opened 9 Closed

Weds 27 8 Opened 7 Closed

etc..

I would like to put this on a bar chart.

Hope this helps.

Lee

[Guest Chaz]

Hi Lee,

sounds like a combination of widgets and measures would be a good way forward for you.

If you were to set up two daily measures, one for requests opened and one for requests closed, you could capture this over a period of time, but you can just keep/display as many as you want, so say the last 7 days.

You can then use these two measures as part of a 'Measured Samples' widget. You can add multiple series and then there's even an option to stack the bar charts.

Hope that helps.