Report Involving Dates

[Chibamba]

Hi All,

I need some help creating a report involving dates. Basically I need to produce a report which shows all resolved and closed  requests where the date in the 'last updated' column is greater (=later) than the date in the 'resolved on'/'date closed' column AND where the 'last updated by' column is 'System AutoResponder'. 

Can this be done in a report, I picked the column headers from a view I created but there is no way of comparing dates in views (i think). Would someone please help by typing the syntax in the report design menu.

Thanks and sorry for all the troubles am still finding my footing in hornbill.

Chibamba

 

 

[Bob Dickinson]

Hi @Chibamba

No problem, hope we can help. You can compare dates in a report - you simply need to know the dates you need to compare and you can use the Against Table Column Value operator in that part of the filter to select another column to perform your comparison against. 

It's a little complex to explain, so I have created this report for you to have a look at and you can amend as you please (e.g. you might want to add some more columns or filtering). 

To upload onto your instance, simply create a new report and click the green button in the top right corner

The report to download is: resolvedclosed-tickets-updated-by-autoresponder-since-closure.report.txt

Let me know if you have any issues

Kind Regards

Bob

[Chibamba]

Hi @Bob Dickinson

First of all, thank you very much for the help on report. It worked well and I was able to upload the template and tweak it to my liking. Apologies for not getting back to you sooner.

Secondly also related to the same report I created, I can now extract all the requests including those where the last update was by the System AutoResponder. However among these, there are some which where closed/resolved by an external email (In this case our former service desk) Is there way I can exclude these from the report because I cannot see where this external email is stored in the request table or any other table?. The report users can go to the ticket and manually remove these from the report but its taking a lot of time to do this. So in short I want to filter out the Last Email Update on Requests from the report.  Is it something you can help me with.

Thank you in advance

 

Chibamba

 

 

[Bob Dickinson]

Hi@Chibamba

Unfortunately it sounds like what you are refering to is trying to perform a filter from an update on the timeline. This is not something that is possible as it is extremely complex and may cause performance issues due to the fact the data you are interregating is not indexed. 

The only real way of doing something like this is to mark it somewhere on closure (for example as a closure category/profile, or a custom field) to say it's been resolved by an external email. Then you would have a value to perform filtering on as part of the reporting.

Sorry I can't be of more help here

Kind Regards

Bob

[Chibamba]

Hi @Bob Dickinson

 

Thanks for the response. You have been of great help. I think I will try the closure category/profile or the use of a custom field. This may just do what I am after. Thanks again for the suggestion.

Chibamba