SSRS report multivalue report filtering and grouping

Question

Let's start with the basics. Here's the simplified structure of the data coming into the report:

ID | Tags
1    |A| 
2    |A|B|
3    |B|
4    |A|C|D|
5    |B|D|
6    |D|A|C| --I added this row to show that tags could be in any order

I have a parameter on the report where users can choose one or more tags from a list (A,B,C,D)

Here's the output I'd like to see on the report. It'll be exported into Excel so I'll be using that to describe the desired output.

Sample report output: (Tag parameter selection: A and D)

Worksheet 1 = displays all records => [1,2,3,4,5,6]

Worksheet 2 = displays records that match all tags selected (must have tags for both A AND D!) => [4,6]

Worksheet 3 = displays records that have tag A => [1,2,4,6]

Worksheet 4 = displays records that have tag D => [4,5,6]

**Note: Worksheets 3 and up will show each of the tags selected in a separate worksheet, there could be 1 to N sheets.

Currently in the report I have 3 tables ready to go:

Table 1: Just displays the full query (nice and easy!) and has a PageName="All records"

Table 2: Need to filter full query down to match Worksheet 2 above and will have a PageName="Filtered records" This is problem #1! Looking for ideas on a filter query!

Table 3: Need to group the full query by Tag, but also only displays groups where the tag is in the list of tags selected in the parameter. This is problem #2! Can't just take the filter from Table 2 and then group because records would be missing (such as number 5 for tag D)

Any and all help would be greatly appreciated!!

Additional notes:

Tag delimiter could be changed (I chose | because the data has commas)
Regardless of delimiter, tags can only come back in one column (delimited list) due to aggregation in other columns

Alan Schofield Alan Schofield · Accepted Answer · 2017-10-24T21:40:17

There are several questions asked here. I'll deal with the one that will make you queries simple first.

I'm not sure if I've met your criteria as I didn't understand some of what you said but anyway, it might point you in the right direction.

Create a split function in your database if you don't already have one If you don't have one, you can use this one I created years ago. It's not perfect but does the job for me.

CREATE FUNCTION [fnSplit](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
    @value varchar(8000),
    @bcontinue bit,
    @iStrike smallint,
    @iDelimlength tinyint
IF @sDelim = 'Space'
    BEGIN
    SET @sDelim = ' '
    END
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
    BEGIN
    WHILE @bcontinue = 1
        BEGIN
--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

        IF CHARINDEX(@sDelim, @sText)>0
            BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END

--Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

            END
        ELSE
            BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            --Exit the WHILE loop.
SET @bcontinue = 0
            END
        END
    END
ELSE
    BEGIN
    WHILE @bcontinue=1
        BEGIN
        --If the delimiter is an empty string, check for remaining text
        --instead of a delimiter. Insert the first character into the
        --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
        IF DATALENGTH(@sText)>1
            BEGIN
            SET @value = SUBSTRING(@sText,1,1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0  
            END
    END
END
RETURN
END

This function just splits your delimited strings into it's components as a table.

We can then use CROSS APPLY to give us a result set that should be easier to work with. As an example I recreated your sample data then used CROSS APPLY like this...

DECLARE @t table(ID int, Tags varchar(100))

INSERT INTO @t VALUES
(1,'|A|'),
(2,'|A|B|'),
(3,'|B|'),
(4,'|A|C|D|'),
(5,'|B|D|'),
(6,'|D|A|C|')

SELECT * FROM @t t
    CROSS APPLY fnSplit(Tags,'|') f
    WHERE f.Value != ''

This gives us this output

ID  Tags    idx value
1   |A|     1   A
2   |A|B|   1   A
2   |A|B|   2   B
3   |B|     1   B
4   |A|C|D| 1   A
4   |A|C|D| 2   C
4   |A|C|D| 3   D
5   |B|D|   1   B
5   |B|D|   2   D
6   |D|A|C| 1   D
6   |D|A|C| 2   A
6   |D|A|C| 3   C

To get all records just do

SELECT DISTINCT t.* FROM @t t
    CROSS APPLY fnSplit(Tags,'|') f
    WHERE f.Value != ''

To get the filtered records, assuming you have a parameter called @pTags then change the dataset statement to something like

SELECT DISTINCT t.ID, f.Value FROM @t t
    CROSS APPLY fnSplit(Tags,'|') f
    WHERE f.Value != ''
    and f.Value IN (@pTags)

As long as this is directly in your dataset query and the parameter is multi-value then this should filter correctly, use DISTINCT if required.

SSRS report multivalue report filtering and grouping

1 Answers