Major Issues with measure results (Logged, Resolved, Open, etc.)

[dwalby]

Hi all,

Just returned back from the Christmas/New Year break and noticed that our daily/weekly/monthly performance metrics have been going haywire since the 20th December. See below:

It's entirely possible that this is as a result of the additional filter I've applied in bold in the below WHERE clause, however I'm not sure why:

(h_requesttype='Incident' OR h_requesttype='Service Request') AND h_status!='status.cancelled' AND (h_summary NOT LIKE '%New SfB telephony number is required%' OR '%New Skype for Business telephony number is required%' OR '%Surface Phase 3 Upgrade -%') AND (h_reopencount = 0) AND (h_catalog NOT IN ('Submit Feedback, Suggestions or Complaints', 'Order Refreshments', 'Surface Pro Rebuild', 'Surface Pro Upgrade', 'Surface Pro Upgrade/Re-Build') OR h_catalog IS NULL) AND  (h_fk_servicename != 'Data Protection') OR (h_fk_servicename != 'Test Service')

If I run a report of Incidents/Service Requests logged yesterday it shows 0 so it seems it's almost certainly a problem with the measure, not the system.

Any assistance on this would be appreciated

 

[Guest Chaz]

At a glance, it looks like you might just be a badly formatted OR statement. Two ways to fix that, one is to wrap both in a set of brackets:

AND ((h_fk_servicename != 'Data Protection') OR (h_fk_servicename != 'Test Service'))

or bring the conditions together:

AND (h_fk_servicename != 'Data Protection' OR h_fk_servicename != 'Test Service')

If the statement you have is correct then you can open up the definition for the measure and use the Save as option to create a copy. If you remove the new condition and create the sample, the results should tell you that either there's a problem with the condition or if they all go red then there may be something else at play here.

[dwalby]

Thanks @Chaz - I removed the 2nd servicename filter which corrected the measures, but I'll look to fix the filters as per your post above

[Lyonel]

@dwalby I confirm what @Chaz said. Your SQL is the problem here. You are indeed missing brackets. You should have:

AND (
  	h_fk_servicename != 'Data Protection'
  	OR
  	h_fk_servicename != 'Test Service'
)

or

AND h_fk_servicename NOT IN ('Data Protection', 'Test Service')