I have 4 columns in a table

Continent   Country     State        District
APJ         India       punjab       Tavi
APJ         India       punjab       xyz
APJ         India       punjab       abc
AMS         US          Washington   efg
AMS         US          Washington   yyy
AMS         US          Columbia     bbb

I need to create four parameters which are dependent on each other in any order.

when the report runs, following values should be selected in the parameters by default without any duplicates

parameter1 parameter2   parameter3   parameter4
APJ        India        Punjab       Tavi
AMS        US           Washington   xyz
                        Columbia     abc
                                     efg
                                     yyy
                                     bbb
                    
                    

Requirement in SSRS with examples

1. If I select only Punjab in parameter 3 then parameter1 should show only APJ , parameter2 should show only India and parameter4 should show only Tavi,xyz and abc.

2. If I select yyy and bbb in parameter 4 then parameter1 should show only AMS , parameter2 should show only US and parameter3 should show only Washington and Columbia

3. Similarly if I select a value in parameter2 then parameter1, parameter3 and parameter4 values should be dependent on parameter2

4. Similarly if I select a value in parameter1 then parameter2,parameter3 and parameter4 values should be dependent on parameter1

CodePudding user response：

You can't do this easily, SSRS does not allow forward or circular dependencies. So if parameter 2 relied on parameter 1 then parameter 1 cannot rely on parameter two. Imagine the scenario with just two parameters, say country and province.

Dataset query for province might be SELECT * FROM tblProvince WHERE Country = @pCountry Dataset query for country would be SELECT * FROM tblCountry WHERE Province = @pProvince

but @pProvince cannot be evaluated without running the associated query, which relies on @pCountry which cannot be evaluated without running the associated query which relies on @pProvince and round and round we go.

And now a possible answer... There may well be an easier way of doing this, however this should work...

In the following code TestTable1 is the data you supplied in the question.

The basic idea is that we create a pair of datasets for each parameter, one which contains a list of available columns and the other a list of values from that column. I've only done two in this example but the principle is the same, each query will just have more parts to the WHERE clause.

The first dataset will be called 'dsP1Fields'. This is just a list of the 4 columns in our table so the following query will suffice (you could reference system objects to get the column names but it's overkill for this simple scenario)

DECLARE @t TABLE(colName varchar(50))
INSERT INTO @t VALUES ('Continent'), ('Country'), ('State'), ('District')
SELECT * FROM @t

Now add a parameter called p1Field and set its available values to the dsP1Fields query.

We can test the report at this point to make sure we see the 4 items in the drop down... all good so move on...

Create a new dataset called dsP1Values and set its dataset query to be

DECLARE @sql varchar(1000) = CONCAT('SELECT DISTINCT ', @p1Field, ' as p1Values FROM dbo.TestTable1')

exec (@sql)

Note that we have to alias the returned column as SSRS always expects the same dataset structure to be returned, so whatever you choose will be called p1Values.

Now create a new parameter called p1Value and set its available value to the dsP1Values dataset.

If you test now, you'll see that the p1Values dynamically selects the correct values based on the field you chose.

OK, next up we do the 2nd parameter, this is very slightly harder as we have to accommodate choices made in parameter 1.

So, create a new dataset called dsP2Fields with the following query.

DECLARE @t TABLE(colName varchar(50))
INSERT INTO @t VALUES ('Continent'), ('Country'), ('State'), ('District')
SELECT * FROM @t WHERE colName NOT IN(@p1Field)

Note that we omit anything selected in the first parameter so you cannot choose the same column twice.

Now create a new parameter called p2Field and set available values to dsP2Fields.

Next, a new dataset called dsP2Values with the following query

DECLARE @sql varchar(1000) 
    = CONCAT('SELECT DISTINCT [', @p2Field, '] as p2Values FROM dbo.TestTable1 ', 
             'WHERE ', @p1Field, ' = ''', @p1Value, '''')

exec (@sql)

Next a new parameter called p2Value with available values pointing to dsP2Values

If you test now you'll see that the selection is totally dynamic. You can continue with the other 2 parameters just remember to extend the query each time to accommodate the parameters that have already been set, just as I did for parameter 2.

Here's a short GIF to show this in action.