UPDATE:
Chi and I were able to determine the actual queries being sent. Adding one of fields as a column to return in step 2 above modifies the WHERE clause in such a way that it only matches event status associated with active tickets.
For example, after adding one of the columns and then searching for active requests, this is the WHERE clause:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'Yes')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%') AND (SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13)
and changing this to searching for inactive requests results in:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'No')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%') AND (SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13)
However, the possible event status for inactive tickets are 0 ( Cancelled ) or 1 ( Closed) and these are not included. This explains why no results are returned.
Note that this is not something that can be changed by the user because the application is adding the WHERE clause when the column(s) are added and is NOT part of the filter as defined by the user. However, when we changed this in SQL Management Studio as a new query to:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'No')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%') AND (SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 0 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 1 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13)
or when we eliminated the check for event status altogether:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'No')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%')
Then this returns the correct results and matches the ones from SDM.
Chi is working on duplicating this in his environments to document the case for the vendor to verify but I think we have the cause. Now we need a solution.
As a workaround, I can create a new report in Report Designer using SQL on behalf of the user but this is not the expectation of how the product should work.
J.W.