CODEWITHSUNDEEP
sqlt-sqlreporting-servicesssrs-2008
Anthony Sims
Anthony Sims

Reputation: 171

SSRS report won't follow parameter rules

Having a bit of trouble here, and I'm sure I have to be overlooking something.

1st query:

    SELECT Column1, Column2, Column3 
    FROM Table1

2nd query (Will be used to create a drop-down parameter):

    SELECT DISTINCT 
    Column1
    FROM Table1
    ORDER BY Column1

After putting this into SSRS, and creating a drop down parameter for the contents of Column1, I select the desired value and click View. However, instead of returning the specified value, it returns all values in Column1. I don't really want that.

What might I be missing?

Upvotes: 1

Views: 65

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

You haven't used the parameter to filter your results.

There are two ways to do this - either use the parameter in your query and link it to your parameter on the Parameters tab of the Dataset.

You would add a where clause to your 1st Query to filter the results. I will assume that you're using a multi-value parameter.

SELECT Column1, Column2, Column3 
FROM Table1 
WHERE Column1 IN (@COLUMNS)

enter image description here

Or you could use the FILTER tab of the Dataset.

enter image description here

It's usually easier to do it in the FILTER tab but the report will usually run faster when using the WHERE clause. Filtering through the FILTER tab requires the query to bring back all results to the Report Server and then filter the results while using the WHERE clause lets SQL Server do the work of filtering and only returns the records needed.

Upvotes: 1

Related Questions