SSRS multiple-value filter trap
Hi team!
There is a report based on SSRS. In the report, there is a filter allowing multiple values like checkboxes. And some rows are not included in the report.
SQL Server gets a comma-separated value list as a filter from the report. Then SQL Server split the list into the table and checks if a row is in the list (table).
WHERE (((ISNULL(dimensionValue.[Code],'NULL') IN (SELECT * FROM STRING_SPLIT(@ProductGroupFilter,','))) AND (@ProductGroupFilter IS NOT NULL)) OR
(@ProductGroupFilter IS NULL))If ProductGroupFilter is NULL then no filter applied. Otherwise, SQL script filters the rows according to the specified filter.
And some rows are filtered out but they have to present in the report.
The source for the parameter was at follows:
SELECT 'NULL' as 'Name'
UNION ALL
SELECT DISTINCT [Name]
FROM v_Dimension_Value
WHERE [Dimension Code] = 'PRODUCT GROUP'
The reason is the filter is based on the Dimension Value Name as in the report interface. If the Dimension Value Name contains a comma in it, so that value will be split into new values.
For example, I checked the following values in the filter: ‘VALUE1’, ‘VALUE2’, ‘VALUE3’, ‘VALUE4’. The report works correctly.
If I check new values: ‘VALUE1’, ‘VALUE2’,‘MY,VALUE2’, ‘VALUE3’, the report will include lines having dimensions ‘VALUE1’, ‘VALUE2’ and ‘VALUE3’. But lines having dimension ‘MY,VALUE2’ will be excluded due to split string:
‘MY,VALUE2’ transfer to two values ‘MY’ and ‘VALUE2’.
There will be a mismatch between real processed values.
Use different values in the interface (Label field) and in values to filter by (Value field) to avoid this trap.
In my case, I used the Dimension Value Name in the interface and the Dimension Value Code as value to filter by. Of course, the Dimension Value Code list must not contain commas.
SELECT 'NULL' as 'Name'
, 'NULL' as 'Code'
UNION ALL
SELECT DISTINCT [Name]
, [Code]
FROM v_Dimension_Value
WHERE [Dimension Code] = 'PRODUCT GROUP'
