In an existing SSRS 2012 report, I have a requirement for a user to be able to select by multiple school(s) and/or multiple grade(s). This is fine except certain schools like elementary have grade levels of KG to 06, Middle school has grades of 06 to 09 and high school has grades of 10 to 12. Thus for example, if a user has initially selected grades 11 and 12 and then they select an elementary school that has grades KG through 06, the 'grade level' selection would need to change.

In other words,  I am thinking of initially having the available and default values for the parameter called 'Grade' set to KG through 12. However if a school is selected that only has specific grade levels like elementary school, how can I override the original grade level and only allow the user to select grades that the particular school contains?

Hi Wendy,

Per my understanding that you have two multiple value parameters "School" and "Grade", you have specify the available values and default values for these two parameters, now you want to value which displayed in the dropdown list of Grade depende on the selection in the School parameter, right?

I have tested on my local environment and you can create cascading parameter for Grade, for example, when you select "Elementary" in the School the grade dropdown list will display "1,2,3,4,5", when you select the "Middle", the grade will display "6,7,8,9", when you select the "High", the grade will display "10,11,12".

If this is case, please reference to the details information below to achieve this:

1. Create an new dataset2 and use the query below to get the values from this query for the parameter School:

select distinct a.School from
(SELECT   'Elementary' AS School, 1 AS grade
UNION
SELECT    'Elementary' AS School, 2 AS grade
UNION
SELECT    'Elementary' AS School, 3 AS grade
UNION
SELECT    'Elementary' AS School, 4 AS grade
UNION
SELECT    'Elementary' AS School, 5 AS grade
UNION
SELECT    'Middle' AS School, 6 AS grade
UNION
SELECT    'Middle' AS School, 7 AS grade
UNION
SELECT    'Middle' AS School, 8 AS grade
UNION
SELECT    'Middle' AS School, 9 AS grade
UNION
SELECT    'High' AS School, 10 AS grade
UNION
SELECT    'High' AS School, 11 AS grade
UNION
SELECT    'High' AS School, 12 AS grade)a

2. Create the dataset3 and use below query to get the values from this query for the parameter grade:

SELECT  a.grade
FROM  (
SELECT     'Elementary' AS school, 1 AS grade
 UNION
SELECT     'Elementary' AS school, 2 AS grade
 UNION
SELECT     'Elementary' AS school, 3 AS grade
UNION
SELECT     'Elementary' AS school, 4 AS grade
UNION
SELECT     'Elementary' AS school, 5 AS grade
UNION
SELECT     'Middle' AS school, 6 AS grade
UNION
SELECT     'Middle' AS school, 7 AS grade
UNION
SELECT     'Middle' AS school, 8 AS grade
UNION
SELECT     'Middle' AS school, 9 AS grade
UNION
SELECT     'High' AS school, 10 AS grade
UNION
SELECT     'High' AS school, 11 AS grade
UNION
SELECT     'High' AS school, 12 AS grade) AS a
where a.school in (@School)

3. Create two parameters "School" and "Grade" check the "Allow Multiple Values" and also set the "Availble values" and "Default values" by select the "Get values from a query" as below:

4. Preview as below:

More details information about add Cascading Parameters to a Report:
Add Cascading Parameters to a Report (Report Builder and SSRS)

If you still have any problem, please feel free to ask.

Regards,
Vic

You have a good example. However the user wants the option to select only a grade. They may not even care what the school is. Thus in this case, the grade is not dependent upon any school being selected.

Hi wendy,

Could you please provide more details information about your requirements?

What do you mean about user want to select only a grade and not care about the school?

Regards,
Vicky Liu