Reporting on Date and Time field

[lomixture]

Good morning,

Is there a way of writing a report, to show you the date and time chosen as a question within the body of a request?

An example - we have people request the booking of pool cars, but we need to be able to report on how many are requested for each day of the week NOT what date the request was logged.

Thanks

[lomixture]

Please could I ask for an answer on this :)?

[Hornbill Staff DR]

Hi Lauren,
 thanks for your post.

When it comes to information gathered using custom forms/fields during progressive capture (which are ultimately displayed in the "Questions" section of a request), this information is stored in a table called "h_itsm_questions". The attached image shows the table description that can be found in the entity viewer (https://wiki.hornbill.com/index.php/Application_Entity_Viewer) - a very useful tool when it comes to building reports or measures.

I'll start with a rather crude approach which looks directly at h_itsm_questions, and aim for a count of records grouped by date i.e. the number of bookings for each unique date found in the database. In this case, I'm assuming that you've got a date picker in your progressive capture which will lead to a date/time stamp (YYYY-mm-dd 00:00:00) being stored in the database. If you're using a date/time picker, then this approach won't work and we'll have to go into more detail. Either way, the date-time stamp will be found in the column h_answer. 

To isolate this information, it will be necessary to focus on this specific question. You can use "h_question_id" or "h_question" to do this. "h_question_id" will contain the field id of the field in progressive capture, "h_question" will contain the label that you specified for the custom field:
 

In terms of the method we use to get the information out of this table, assuming we just want to focus purely on this piece of data we could utilise a Chart Widget using the "SQL Group By" data type and set the data source as shown. As I said, It's quite a crude method and I have made an assumption about how the progressive capture is set up and thus how the data is stored. If you're using a date-time control rather than a date control then that means there's going to be many unique timestamps and this method won't be suitable . 

 

Let me know if that helps at all, I've got some alternatives which I will follow up with shortly.

Dan

[Hornbill Staff DR]

To expand a little more on progressive capture custom forms, if you have a key piece of information being captured by custom questions, it may be prudent to use field mapping to ensure the data is stored in a more accessible way.

Field mapping (https://wiki.hornbill.com/index.php/Mapping_Fields_from_Customised_Forms), allows us to map the information being captured via custom forms to a column in the main request table, h_itsm_requests.

The benefits are as follows:

• the data will exist alongside all the standard request data allowing you to focus on a single table in your report.
• the data can be mapped to a field of an appropriate type i.e. when capturing date-time stamps this can be mapped to a field that specifically expects a date-time stamp.

If we were to apply field mapping to the above example, so the booking date being captured is mapped to a specific date-time field (say h_itsm_requests.h_custom_21) we could use the Advanced Analytics module to build a measure to show when the bookings were requested. In this example, setting h_custom_21 as the "Date Ranging Column" would enable daily, weekly, or even monthly trends to be captured.

 

Once we're capturing the information in a measure, this would mean we can use the other chart widget data-types such as "Measured Samples", "Measure Group By", and "Measured Samples Group By". Of course, a measure would only be useful when looking at past data.

Dan