mojahidm Posted June 6, 2018 Posted June 6, 2018 Hi team, I'm trying to create a report where I will enter in a date range and a list of orgnizations (from a previously made simple list). From this, I would want the organization name, the priority name and the within fix I have named the report: Sandbox Official 2.Incidents Resolved Count Ver5 - Date and Org with Priority/Fix The issue I have is that no data is being returned. Please see following report construction: FILTERS: 1. REQUEST TYPE WILL BE INCIDENT: 2. DATE PROMPT FOR RESOLVED DATE 3. ORGANIZATION PROMPT SELECT ORDERING DATA PREVIEW I have run the following code in the database direct and can see that there should be data here: SELECT * FROM h_itsm_requests AS his JOIN h_sys_organizations AS hso ON his.h_org_id = hso.h_organization_id WHERE hso.h_organization_name = 'UK_Review' Any help would be much appreciated Kind regards, Mojahid
Victor Posted June 7, 2018 Posted June 7, 2018 @mojahidm the SQL query you ran does not filter on request type to return only incidents as the report does ... can you confirm the records returned by the query which have a resolved date are incidents?
mojahidm Posted June 12, 2018 Author Posted June 12, 2018 Hi @Victor I noticed the same thing when I checked out the result list - the last two are SRs kind regards, Mojahid
Victor Posted June 12, 2018 Posted June 12, 2018 @mojahidm can you please PM me the report definition file?
Victor Posted June 19, 2018 Posted June 19, 2018 @mojahidm can you please try this in your report: 1. For Tables section use this instead: 2. In Filters section, replace current company filter with this: *just remember to select the relevant list from the drop down box. Save these changes and let me know how it works...
mojahidm Posted June 19, 2018 Author Posted June 19, 2018 Hi @Victor Unfortunately didn't work: I got the above error Kind regards, Mojahid
Victor Posted June 20, 2018 Posted June 20, 2018 @mojahidm that's because the report has an invalid filter.... To remove it do the following: 1. Click on the + sign to gain access to the filter. It will display like this: 2. Click on the "new" filter to select it, then click "Delete Selected Filter". It will display like this: The end result should be something like this: Note the last "AND" operator is now gone. Save the changes and run the report.
mojahidm Posted June 21, 2018 Author Posted June 21, 2018 Hi @Victor Thank you for the help. The report appears to work . I've just been going through the data to make sure it's what I expect. I'm still struggling with the Within Resolve/Fix column... I was expecting to see a priority 3 that breached: The sql I used to get this result was: SELECT * FROM h_itsm_requests AS hir JOIN h_sys_contact AS hsc ON hir.h_fk_user_id = hsc.h_pk_id AND hsc.h_company = 'UK_Review' AND hir.h_requesttype = 'Incident' AND hir.h_datelogged >= '18/03/01' Kind regards, Mojahid
Victor Posted June 21, 2018 Posted June 21, 2018 1 minute ago, mojahidm said: I'm still struggling with the Within Resolve/Fix column... @mojahidm - I need more details
mojahidm Posted June 22, 2018 Author Posted June 22, 2018 Hi @Victor, what details were you after? Would it be the definition file? Or clarification of the issue...? With the report - I needed to be able to view the priority and how many incidents met the SLA and how many didn't. From the SQL I ran, I thought there would be another row for Priority 3 that stated 'Breached' or a '0' as it has a '0' in the within fix column. Second row in the screenshot below: The count is correct and I have four showing for priority 3 and two showing for priority 2, but it's not displaying the within fix as I expected. kind regards, Mojahid
Victor Posted June 22, 2018 Posted June 22, 2018 7 minutes ago, mojahidm said: I needed to be able to view the priority and how many incidents met the SLA and how many didn't. @mojahidm apologies, I was after more clarification. The above So the SQL you ran has this filter: h_datelogged >= '18/03/01' The report however filters on h_dateresolved. Any chance that the priority 3 breached incident is not returned in the report because of the different date filter used in report vs. SQL query?
mojahidm Posted June 22, 2018 Author Posted June 22, 2018 Hi @Victor Looks like the culprit was the 'Select Ordering' tab. I needed to select 'Within Resolve Time' instead of 'Priority Name' Thank you for all your help I've no doubt I'll be back posting another thread when I try to do my next report Mojahid
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now