Reports created with Report Builder are not filtering distinct results when the option is selected

Products

• IT Management Suite

Issue/Introduction

After creating a new report in Report Builder using the 'Distinct' filtering option, the resolved query and results tabs are correct and the query returns the expected results. However, when running the report, it is ignoring the 'Distinct' option and multiple rows of the same value are being returned.

Reports created with Report Builder may return duplicate records even when the Distinct option is selected, if the "Promote scoping information to the data source" setting is enabled under the Advanced tab.

Example:

Created report in Report Builder to find distinct models of VMware computers with an asset status of 'active':

Report Builder 'Results' tab returns the expected distinct value:

The resolved SQL query also returns the expected results when run in SQL Management Studio:

However, after running the actual report it is not filtering distinct results:

Environment

ITMS 8.7.3 and earlier versions

Report Builder

Cause

It is by current design that this issue still may exist if Scoping is enabled.

Implemented changes were done for clarifying usage (to workaround issue if applicable) but not changing logic.

The issue is due to the "scoping" functionality, which is adding the 'GUID' column to the resulting query, even if scoping is not used. The resolved query in designer mode does not count the special option, which is ON by default at the "Advanced" tab as seen here:

Resolution

NOTE: The following changes were made in the ITMS 8.8 Release:

• some changes were done to help customer understand it
• preview of the SQL will show 'scoping' wrapper code, even when running under admin
• some UI elements got description labels about it

Our 'snapshot' functionality require entity 'GUID' to be in data set, when scoping is active.
This is why we have to add 'GUID' to resulting data set, when scoping is effective.
Labels added to show description when appropriate:

 

Workaround: turn OFF scoping in report, including this "Promote scoping..." option in the 'Advanced' tab. Then, report data will contain only selected fields and distinct will work correctly.

Another workaround option is to 'Convert this query to SQL query' or use the raw SQL query. This will ensure the report will show only correct values.