Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.
In yesterday’s post “Adding Filter Parameters to Reports”, we explored how to add a filter to the report dataset. This had the advantage of being very fast when you wanted to look through different sets of data. The disadvantage to this method is that it brings back all possible rows for the report at once. Thus it can be very slow and database intensive and unnecessary when the user only wants to look at a subset of the data.
An alternative to Filters are Query Parameters. Query Parameters are applied at the SQL statement level, before the call to the SQL Server (or other database platform) is made. This limits the number of rows brought back from the server, increasing the speed and reducing the memory footprint required on the reporting server.
To begin, let’s create a base report. We’ll do the same as steps 1 to 10 in the “Adding Filter Parameters to Reports” lab, but in case you have not worked through it yet I will repeat them here.
Step 1. Add the report
As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.
Step 2. Set the data source.
Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.
Step 3. Setup the query.
In the query builder, we’ll be using one of our views. Enter this SQL statement:
SELECT [FiscalYear] , [ProductCategoryName] , [ProductSubcategory] , [Region] , [TotalAmount] FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
and click next.
Step 4. Set the format.
For the report type we’ll use the simple Tabular format, so just click Next.
Step 5. Determine field placement in the report.
To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.
Step 6. Select the formatting Style
Once again we’ll go with Corporate for the style and click Next.
Step 7. Name the report.
Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.
Step 8. Format report columns
To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)
Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.
Step 9. Add a new dataset to act as a source for the filter parameter
There are several choices we can use for creating filters. We could allow the user to simply key a value to use into a text box. It is also possible to hard code a list of values, for example “Yes” and “No”. In most cases though, you will want to create a filter based on a set of values in the database, which is what we’ll do in this lab.
Here we’ll apply a filter for the Region. To do so we’ll first need to create a dataset to supply this list from the database. In the Report Data window (if it’s not visible pick View->Report Data from the main BIDS (aka Visual Studio) menu) right click on Contoso and pick “Add Dataset”.
Give the new data source a good name. Here we can use Region.
We have several choices for a data source; here take the default of Text (which means we’ll just enter a query).
In the Query area we have many choices. Even though there is a designer built in, the best way is still to use SQL Server Management Studio to create and test your query, then paste it in here.
SELECT [RegionCountryName] FROM [ContosoRetailDW].[Report].[V_Regions] ORDER BY [RegionCountryName]
Once you’ve entered the above, click OK.
Step 10. Add the Region parameter
Now that we have a new dataset, we need to add a parameter to apply to our filter. Still in the Report Data window, right click on the Parameters and pick “Add Parameter”.
Give the parameter a good name. This is the variable name you’ll use elsewhere to refer to this item. Remember it, as you’ll need it later! Make sure it has no spaces and follows other typical guidelines for naming variables. For this example we can use the word Region.
Next you want to supply a prompt. This is the message shown on the report beside the parameter selection control. For our example let’s use “Select a Region to work with:”
You should now indicate the data type for the parameter. There are only a few you can pick from, for this though the default of Text will do fine.
Users often want to see multiple items on a report, but not all, so we’ll allow them to pick more than one by checking on the “Allow multiple values” check box. Your parameter window should now look like:
Next we need to tell the report where to get the data from. On the Available Views area, select the “Get values from a query” option. Then pick the new dataset we created, the Region one.
Below this you will see the Value field and Label field options. Frequently when dealing with data we have primary key data, such as an INT, that is needed to link data together. But we also need a human readable value, something that the users can see and understand.
A good example is the classic Employee table. You have an EmployeeID and an EmployeeName. For the value field, you’d pick the EmployeeID, but for the Label field you use the EmployeeName.
In this particular case, we are using the same field for both, and it’s perfectly valid to do so. Just pick “RegionCountryName” for both Value and Label drop downs.
For the rest of this example, know that we won’t do anything with Default Values or the Advanced Area. In the Default Values we can pick or set a value to be the default, and the Advanced lets us determine how often we need to refresh the source data for our parameter.
Step 11. Adding the Region Parameter to the report dataset
Next we’ll need to do two things to add the parameter to the dataset. First, right click on the main reports dataset and go to Dataset Properties.
Since we want to apply this parameter at the database level, we’ll need to add it as a parameter to our SQL query. In the query area of the Dataset Properties window enter:
SELECT [FiscalYear] , [ProductCategoryName] , [ProductSubcategory] , [Region] , [TotalAmount] FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear] WHERE [Region] IN (@pRegion)
Next, we need to tell the query where the @pRegion parameter comes from. Go to the Parameters area of the Dataset Properties dialog. If Visual Studio / BIDS has not already done so, click add to add the @pRegion. Then in the drop down pick the @Report parameter object. Click OK to save the changes.
Return to the Preview tab and view the report several times, using various items in the list. Everything should update fine.
Except… having the Select All can cause some issues, which are covered in the next post.
15 thoughts on “Adding Query Parameters to SQL Server 2008 Reporting Services Reports”
Hi, I have a report in SSRS 2008 and followed all the the steps from this post. When I use a single parameter value the report works fine. The strange things start when I check the multi value parameter box. I get a message like this:
An error occurred during client rendering.
Exception of type ‘Microsoft.ReportingServices.ReportProcessing.ReportProcessingException’ was thrown. (rsProcessingError)
No information is available about this particular error on the web. I just stumbled upon this issue and can’t get it resolved.
Excellent!!! Microsoft should have people like you writing their how-to library. you save my date.
You can also incorporate parameters into your query when using the query designer (rather than manually writing sql code) in the applied filters section.
It works great for strings. You say . It’s not working for Integer parameters though and it is driving me crazy! If anyone has any tips, it would be greatly appreciated.
I figured this out after all. If you are ticking the ‘Parameter’ option when entering your query filters in the query designer, you must leave Value blank. I was putting the parameter name in the Value field for the filter and it was complaining because the field I was filtering on was an int.
Can you please show me what you did… i ma also having the same trouble passing integer to query for composite through ODBC.
having error : ERROR [HY000] Unable to parse query text: unexpected char: ‘@’. On line 10, column 18. [parser-2904201]
Cause: unexpected char: ‘@’
here is my Query:
INNER JOIN others.subfund sf ON f.fund_id = sf.fund_id
AND f.fund_id= (@pr_fund_id)
Query parameter Defined:
@pr_fund_id, param value[@pr_fund_id]
The article is so gud that i found it correct for my requirnment.
I followed the steps.
While adding the parameter, i am nt able to see the parameter properties window.
can you please guide me on how to get the parameter properties window visible.
thank you i got it dne..:)
This has really helped me out.
very nice… clear and simple to understand
For my parameter called DateStart, I have set the Data Type as “Date/Time”, I setup the Default Value as “specify values”, then created the expression:
=iif(Parameters!DateRangeType.Value = 2,dateadd(“m”,-4,Datestring),dateadd(“d”,-1*Weekday(Datestring)-6,Datestring))
My default for the Parameter DateRangeType is “1”, and the correct default shows up. What I really want is that when I change the value of DateRangeType to “2”, I want the default contents of DateStart to be refreshed.
It does not do this. Do you know of any way that this can work for me?
any idea how to get openquery to take parameters, but not declare and set variables, which report wizard doesn’t like?
Must the query parameter be listed in the result set i.e. the select list?
It’s interesting. Thanks.