I ran a trace on the SQL server and caught the SQL statement Service Desk is sending:
exec sp_executesql N'SELECT TOP 15 T1.pm_colour,T2.im_id,T1.pm_title,T3.usr_guid,T3.usr_locations,T3.usr_deleted,T4.im_guid,T4.im_title,T4.im_deleted,T5.lc_guid,T5.lc_name,T5.lc_title,T1.pm_creation_date,T1.pm_raise_user_guid,T2.usr_enduser,T1.pm_guid,T1.pm_lifecycle_guid,T2.im_has_mismatch,T2.im_clock_stopped,T2.im_current_assignment_guid FROM pm_process T1 INNER JOIN im_incident T2 ON T2.pm_guid=T1.pm_guid LEFT OUTER JOIN usr_locations T3 ON (T2.usr_locations=T3.usr_guid) LEFT OUTER JOIN im_incident_category T4 ON (T2.im_category_guid=T4.im_guid) INNER JOIN lc_status T5 ON (T1.pm_status_guid=T5.lc_guid) WHERE (T1.pm_raise_user_guid=@0 AND T2.usr_enduser=@1) ORDER BY T1.pm_guid',N'@0 uniqueidentifier,@1 uniqueidentifier',@0='DE058170-BE53-4506-AFC9-ECDB7D7DCED1',@1='DE058170-BE53-4506-AFC9-ECDB7D7DCED1'
Note the AND in the WHERE statement: "WHERE (T1.pm_raise_user_guid=@0 AND T2.usr_enduser=@1)" That's proof that it's not using the OR set on the query screen. Is there some trick to using OR that I'm missing?
Are you saving the query and running it for real or using the "Test Query" option within Query Designer? There is a known issue with OR criteria being treated as AND when you do the tests which has been logged as problem 3831, but running the query for real should be fine.
It looks like you have a similar bug in Web Access. I first noticed the problem when end users were seeing closed Incidents in their list of open Incidents. The query there might have been based on one created in Query Designer, but it would have been modified later in Web Access. Please don't tell me you're not going to fix it until the next release, or unless enough people complain about it. That's what I heard about my last Web Access bug.
(I saved in Query Designer and, surprise, the query worked correctly.)
I was wondering if having two relationships between User and Incident was part of the problem. I haven't noticed any other queries failing.
I'm glad that worked. You're using version 7.4 so there shouldn't be any issues with OR criteria in Web Access. However when you use a combination of OR and AND criteria you need to be careful at how they are organised.
For example if you create the query in Console (based loosely off your screenshot):
1. Creation User = me
2. OR Raise User = me
3. AND Status = Open
This will run as "1 OR 2 AND 3" but the grouping is open to interpretation. Without any further configuration this example will actually group as "1 OR (2 AND 3)" which means you will get some results back at the wrong status. For this to work as you'd expect you need to use the advanced criteria feature which allows you to write the logic to use, in this case "(1 OR 2) AND 3". This is enabled at the bottom of the Criteria page in Query Designer.
This feature has not been directly ported to Web Access but there is a direct equivalent. When you look at a Web Access query you can right-click on an existing filter create a sub-filter which is how the grouping is designed. The filters for the same example as above would look like this:
| AND Status = Open
| | ..... Creation User = me
| | OR Raise User = me
For this one you would create the Status filter first, then right-click it and create the first sub-filter, then right-click that and create another (not sub) filter.
I hope that clears things up. If I've totally missed the mark please could you post some screenshots of how the filters look at the top of your query in Web Access?