How to remove multiple values from SSRS parameter drop down

Question

I have a report where I want to add a parameter as a drop down. The database has more than 13,000 records and the parameter I want to add is of the building names in the record. There are around 6 different building names. When I add the parameter by taking Values from a query and passing the building name as the value, the drop down repeats the 6 building names of all 13,000 records rather than just the 6 building names.

Is there a way where I can filter it to show the 6 building names and then show the records related to that building name?

SELECT  new_studentid, new_studentinformationid, new_firstname, new_lastname, new_building, new_busnumber, new_grade, new_primaryhomeroom,documentbody FROM  StudentInformation WHERE(new_building = @new_building) AND (new_primaryhomeroom = @new_primaryhomeroom)

Use SELECT DISTINCT in the query of the data source you are using instead of just SELECT. Also please show us the query so that we can help you. — Mahmoud Gamal
Do you want to make the parameter be filtered by another parameter value? I mean do you have two dropdown lists and you want to filter the second depending on the selected value of the first drop down list?? — Mahmoud Gamal
Yes, Tab Alleman has helped with halfway, now I want just what you said. — hkhan
For the first parameter properties, in the advanced section make the referesh data when the parameter changes, set it to always refresh. For the second parameter data source, just pass the value of the first parameter to the where clause in it. Then when the user selects a value for the first parameter it will referesh the screen and filters the second one. (Actually I don't try it before, but I think it should work this way) — Mahmoud Gamal

Tab Alleman Tab Alleman · Accepted Answer · 2014-09-04T12:52:49

You will need to make a query just for the parameter drop-down. It should probably be something like this:

SELECT  DISTINCT new_building
FROM  StudentInformation

Then for the HomeRoom parameter, use this query:

SELECT  DISTINCT new_primaryhomeroom
FROM  StudentInformation 
WHERE(new_building= @new_building)

How to remove multiple values from SSRS parameter drop down

1 Answers