Repeating values in SSRS Cascading parameter

Question

I have 4 parameters in my report and my requirement is user should be able to select all values from the parameter list.

So I have created cascading parameters but one of it shows multiple values corresponding to other parameters

eg: A A A B B B B C C

Ideal: A
B C

I tried by unchecking allow multiple values in Parameter properties.

Param3-> taking values from Dataset3(Col3)

       Main Dataset:

            SELECT Col1, Col2, Start_Date, End_Date, Col3
            FROM  Table
            Start_Date IS NULL OR
  Start_Date >= @StartDate) 
    AND (End_Date <= @EndDate) 
    AND (Col3 IN (@Param3))

        Dataset 1:

        SELECT DISTINCT Col1
        FROM  Table

        Dataset 2:

        SELECT DISTINCT Col2
        FROM            Table
        WHERE        (Col1IN (@Param1))
        ORDER BY Col2

        Dataset 3:

        SELECT DISTINCT Col1, Col2, Col3
        FROM  Table
        WHERE       
        (Col1 IN (@Param1)) 
        AND (Col2 IN (@Param2))

Any inputs/ideas/suggestions if I can get only Distinct values in my parameter list instead of repeating values?

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-01-21T12:58:18

Your problem seems Dataset 3 is returning repeated values for Col3since DISTINCT clause is applied across every column you select in the query.

This is a valid return of your dataset 3

Col1 Col2 Col3
 A    A    E
 B    D    E
 C    C    E

Note every row is different but Col3 has repeated values.

To get different values in your parameter you can create an additional dataset to populate Parameter3.

SELECT DISTINCT Col3
FROM  Table
WHERE       
(Col1 IN (@Param1)) 
AND (Col2 IN (@Param2))

Let me know if this helps you.

Repeating values in SSRS Cascading parameter

1 Answers