Adding Filter Parameters into SQL Query of SSRS

Zhen Yuwang, 26 May 2012

In general speaking, the user will not need all data in a report every time. And we want to only fetch the part of data needed. So in this blog, I will discuss how to narrow your reports by specify several values on fields.

Assume that we have 3 cases record in original report, which belongs to 3 different Companies. What we want is putting a criterion to only show active cases with selected Organisation.

In the beginning, add a new Dataset “Companies” using this query:

SELECT DISTINCT mag_organisationname
FROM            Filteredmag_case
WHERE        (statecode = 0)

DISTINCT make sure only retrieve unique organisation name and “statecode = 0” is for bringing back active cases. 

Then add a new parameter “Company”, set its “Available Values” and “Default Values” to be “Get values from a query” using value field in above dataset.

Next, we can put this parameter into where statements of SQL query string.

…
FROM Filteredmag_case c
…
AND c.statecode = 0       
AND c.mag_organisationname IN (@Company)
… 

Save this report and upload it into dynamic CRM 2011 using “Existing File” Report Type.

Go to the cases area and deactivate the case under company 3.

Run the report, you will see all distinct organisation names in active cases with default Select All.

Select “Company 2” only and the results are as expected.

Using filter parameters can decrease the response and running time of reports, reduce the burden of server from unnecessary work and fulfil customers’ demands better.