Jump to content
mojahidm

Report Resolved Count by Priority and Within Fix

Recommended Posts

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:

image.png.7102bb93df8afe9ab980532ea667d81c.png

image.thumb.png.f8e2330a3b745570e931762225fa133e.png

image.thumb.png.8a35f0f45adca36661a631bb80ea71f7.png

 

FILTERS:

1. REQUEST TYPE WILL BE INCIDENT:

image.thumb.png.1de966891e8d6b73dc27979c718b44fd.png

2. DATE PROMPT FOR RESOLVED DATE

image.thumb.png.8e0f60a885bc23ccffba67d6a3af221d.png

3. ORGANIZATION PROMPT

image.thumb.png.523cd2551f4254092eb64ae3a78d0434.png

SELECT ORDERING

image.thumb.png.fbcbced9196c2e637646cd3dde108742.png

DATA PREVIEW

image.png.bde5b4994da8c30123f291d1d4212f22.png

image.thumb.png.1038328a9d90da8b177c8d80c6d8bd3d.png

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'

image.png.1015fc64e49deecc58f403b492b27a9c.png

Any help would be much appreciated

Kind regards,

Mojahid

Share this post


Link to post
Share on other sites

@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?

Share this post


Link to post
Share on other sites

@mojahidm can you please try this in your report:

1. For Tables section use this instead:

image.png

2. In Filters section, replace current company filter with this:

image.png

*just remember to select the relevant list from the drop down box.

 

Save these changes and let me know how it works...

Share this post


Link to post
Share on other sites

@mojahidm that's because the report has an invalid filter....

image.png

 

To remove it do the following:

1. Click on the + sign to gain access to the filter. It will display like this:

image.png

2. Click on the "new" filter to select it, then click "Delete Selected Filter". It will display like this:

image.png

 

The end result should be something like this: 

image.png

Note the last "AND" operator is now gone. Save the changes and run the report.

 

Share this post


Link to post
Share on other sites

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...

image.thumb.png.ad20f2db1383f508eff6174b5a247b86.png

I was expecting to see a priority 3 that breached:

image.thumb.png.10ebfa83e8a5402be53efb7a2b186f1c.png

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

Share this post


Link to post
Share on other sites
1 minute ago, mojahidm said:

I'm still struggling with the Within Resolve/Fix column...

@mojahidm - I need more details :) 

Share this post


Link to post
Share on other sites

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:

image.thumb.png.a855d4c69b6df1bbac16d4c0ed3b4493.png

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

Hi @Victor

image.thumb.png.197cb8abce7c903b84f3ee5a95695e03.png

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...