Cascading multi-value parameter issue in SSRS 2008

Question

Basically I have built a report using SSRS 2008, the report has cascading parameters, the independent one is a multi-value parameter (work units) populated with values from a database query, the dependent parameter (job positions contained in respective work units) also gets values from a query with a where clause as follows:

WHERE position.unitId IN (@units)

@units being the multi-value parameter. The default value for units is the query itself - all of which the user has access to. So upon opening the report, all available units are selected and all respective job positions are retrieved - works fine. But a aser can also have no access to any units, which makes the dependent query fail, cause no units were retrieved hence @units contains no values. I would have thought the query would not fire until @units has a value present.. anyways I have tried to check the contents of @units before querying for job positions in various ways:

*replacing @units parameter with the following expression: =IIF(Parameters!units.Count = 0, "00000000-0000-0000-0000-000000000000", Parameters!units.Value)

*having another parameter containing a comma seperated string of the @units values and checking if the lenght of that is greater than 0 before executing the dependent dataset, etc.

But now, when I open up the report, the drop down list of job position is empty, disabled and remains so until the values of units are changed or the report is run. After that they refresh alright it seems. So my question is, what may be the cause of the control being disabled (units are retrieved, so why does an expression as a parameter value for job positions messes it up?) and how to deal with this the right way, can`t seem to find something really of the same nature online.

Any help will be greatly appreciated.

Christopher Brown Christopher Brown · Accepted Answer · 2014-01-27T23:02:06

I've had similar issues occur. To be clear, this isn't an issue from the SSRS perspective. SSRS sees this as the correct course of action. How I've fixed the issue is to do the following:

You need to modify the query that pre-populates your @Unit parameter. In the code below, where I say "@Unit Script" I'm referencing the current SQL script that runs to pre-populate the @Unit parameter.

With Unit_CTE as (
    --Insert @Unit Script here
)

, Count_of_Units as (
Select Count(1) as Unit_Count

From Unit_CTE
)

--Begin a query that will only return a value when Unit_Count returns 0
Select null as 1st_Field --this will be a placeholder for your first field in the @Unit Script
    --To union two queries together, they must have the same number of fields, each with compatible output types.
    --Therefore, you will need to create an empty or null placeholder field for each field in your @Unit script
    , '00000000-0000-0000-0000-000000000000' as Unit_ID
    , 'No Units Available' as Unit_Name --or whatever else you're calling the Unit name field

From Count_of_Units

Where Unit_Count = 0

Union All

--Insert your @Unit Script here

Now what you have is a query that will return two outputs:

If there are Units the end-user can see, those units will show up as normal with no additions to the list.
If there are no Units the end-user can see, then a single value/option will show up in the @Unit dropdown list that will read "No Units Available". Then, your Position_Title parameter dropdown will not find any positions where the UnitID equals "00000000-0000-0000-0000-000000000000".

Now, if you have any other parameters which are dependent on the Position parameter, you may run into a similar issue, where your following dependent parameters are not able to find a value because there is no value available for the Position parameter. If this is the case, repeat the logic above for the Position parameter (thus creating an option that only shows up when there are no Positions found that match @Unit).

Hope this makes sense. Comment if you have any questions or concerns. I know this may seem overly complicated (and there may very well be a simpler solution out there somewhere) but I have used this time and time again and it works well for me, as well as limiting the number of SSRS errors and weird behavior the end-user sees.

Cascading multi-value parameter issue in SSRS 2008

1 Answers