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.

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

This is a known issue currently being investigated by Broadcom developers. 

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

Resolution

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.