Custom Reports

[Hiten]

Good afternoon Hornbill Community, 

We have had a customer ask to pull out some specific information from Hornbill, which would appear it is not possible from the Reporting options and we have been advised by @Andre de Waal to raise here.

Ideally the customer would like to be able to run the Reports themselves, but if this something we can setup and run that probably is suffice.

If anyone can help, it would be a thoroughly appreciated.

1. Aged calls since last update:

• Fields - Hornbill Ref, Summary, Category, Type (Incidents/Service Request), Date of Last Update, Number of Days since last update (to today's date), Status - with Sub-Status.
• Not to include non-comment updates, e.g. Sub-Status has been set to 'In Progress'.

 2. Number of calls in one of the Sub-Statuses, e.g. 'Awaiting 3rd Party Support', 'Awaiting Customer Response', Awaiting Release', 'In Progress', 'With Development'.

• What do the above Statuses mean.  Please note: this one may be for us to find a suitable place to provide the explanation.  But could this be done here - see below image?  If so, how and where would this be surfaced to the customer in Customer Portal and Reporting?

• Then broken down by Category, e.g. 'Incident', 'General Question, 'Technical Request', 'Service Request'.

We understand this may be a custom piece of work but have been advised to try this route first.

Thank you in anticipation of your help.  Have a great day!

Hiten

[James Ainsworth]

Hi @Hiten

I can start with the question around how does the customer get the report.  You are right that the customer does not have access to the area where reports are designed and run.  However, under the scheduling options for each report you can send the email to one or more recipients.  Is this the type of report delivery that you are looking for?

[Hiten]

15 hours ago, James Ainsworth said:

Hi @Hiten

I can start with the question around how does the customer get the report.  You are right that the customer does not have access to the area where reports are designed and run.  However, under the scheduling options for each report you can send the email to one or more recipients.  Is this the type of report delivery that you are looking for?

Hi James, 

Thank you for your response.  Currently, our customers do not access any 'Report' area (where we might make one available to them, either by scheduling one or placing there).  However, it would probably be the case that is what they want to do.  Or at least, we run and send.

Regards,

Hiten

[SamS]

Hi @Hiten,

Just for completeness, details on how to create and schedule reports (to be sent to any email address) can be found at the following locations:

https://wiki.hornbill.com/index.php?title=Reports

https://wiki.hornbill.com/index.php?title=Report_Scheduling

 

[SamS]

Hi @Hiten,

In addition to the above: Reports can also be stored to Document Manager and then associated to a customer specific service - this way you don't need to send out any emails, anyone (from the company) logging in via the portal will be able to see the document/report.

Regarding the sub-status data (your item #2), that is stored in the h_itsm_statuses-table.

As for your item (report) #2:

1. the "days since last update" calculation is NOT (currently) possible.
2. Filtering out system updates is not (easily) possible, but invariably those (system) updates are generated by someone actioning the request (and thus it was at least viewed by an analyst).

 

[Hiten]

Hello @SamS

Thank you for the response.

I have setup a Report based on the parameter you have provided.  However the report I have created uses a user prompt to pick the 'Customer' needed (this is something we do for monthly Service Reports we produce for customers and avoids have several duplicates of a single report with one change, i.e. a nominated 'customer'. 

Since we have used Document Manager before, my concern is that how that plays out if the report is published to Document Manager, then...

a) will the customer be able to scroll the list and effectively retrieve other customers' data?

b) do we need to publish to Document Manager (either manually or via a set schedule) and then share with appropriate privileges/user access to such a report?  In which case how the does system know to filter on a that specific customer we want?  It sounds like we will not be able to have one report for many customers, and instead make it specific to one customer only and share that to Document Manager?

Apologies for the question, but given that it potentially exposes data to other customers then I would rather find out for sure first!  

Kind regards, 
Hiten 

[SamS]

Hi @Hiten,

First and foremost: a report which requires a prompt to be filled in, will NOT function as a scheduled report - the report scheduler will wait unsuccessfully for someone (somehow) to provide the parameter.
You will need to hard-code multiple reports - filtered respectively for each customer.

The filtering, in the suggestion above, would be done based on Service - customers who have access to the service will be able to see documents associated to that service. You would need to set up a "Report Service" for each customer.

 

[Hiten]

Hi @SamS

Just looping back round to this, as we have been chased by our client. 

What is the possibility of:

a) being quoted for a custom piece of work - which will allow a report to be setup for this client (and potentially others) to have a scheduled report or one they can run themselves, that will enable them to see the difference in days from the last update to present.  Note: we have been able to use Database Direct to create a query that does this to some extent but this is not a useable option for us.  Refer to your comment from 15th June.

b) the length of time to give us an initial scoping of the work, approximate cost and when it could be done.  Just looking for rough estimate.

@Andre de Waal can you please help?

Regards,

Hiten

[SamS]

Hi @Hiten,

I appreciate that the calculation of "time since last update" can be done via Database Direct, but my 15/06 statement that it can NOT be done is refering to the regular Report interface - i.e. the one to be used for scheduling reports. It would require a new feature of calculated fields.

The BEST one can do is just a list of calls with the "last updated on" field visible among the other fields you wish to show.

[Hiten]

20 minutes ago, SamS said:

Hi @Hiten,

I appreciate that the calculation of "time since last update" can be done via Database Direct, but my 15/06 statement that it can NOT be done is refering to the regular Report interface - i.e. the one to be used for scheduling reports. It would require a new feature of calculated fields.

The BEST one can do is just a list of calls with the "last updated on" field visible among the other fields you wish to show.

Hi @SamS, 

Can you let me know how that is achievable? "The BEST one can do is just a list of calls with the "last updated on" field visible among the other fields you wish to show."

Regards,
Hiten

[SamS]

Hi @Hiten,

There is a h_datelastmodified field in the h_itsm_requests table. I believe it is "Requests->Data Modified" in the entities view.