Friday, June 3, 2011

SSRS: Adding Parameters in Query Designer (When Using a DataModel as a Data Source)

This blog post gives an example of how to add a parameter to a report using Microsoft SQL Server 2008R2 and the Report Builder 3 tool.


After running Report Builder and opening your report you will see something like this;


Expand the "Datasets" node in the treeview on the left;


If you right-click the active Dataset (DataSet1 - highlighted above) and then select "Dataset Properties";


This screen is showing you a lot of technical details about the Query you have put together. Click "Query Designer ..." to get to something more meaningful. To use the "Dataset Properties" screen you will need  training.

The "Query Designer" screen appears and, if you used the wizard to generate your reports,  you'll remember it from them:


Click on the "Filter" icon in the bar at the top. This will display the "Filter Data" screen:


Scroll down the Field list (bottom left) until you find the field you're interested in adding as a parameter. For the sake of this example I'm going to add the "INVOICE DATE" field. When you find the field you're after you can either double-click it or drag/drop it into the "xxxx INVOICESs with:" area):


If you hover over the "equals" (next to INVOICE DATE) it will turn into a click-able link. Click it:


Select "On or After" in the list and then hover over the "INVOICE DATE" (which will also turn into a click-able link) and right-click it:


Select "Prompt" (this means the user will get a prompt).

Click "OK" to save the changes and close the dialog.

We are now back to the "Query Designer" main page and it looks like nothing has changed. Click "Run" button:


A dialog will now appear asking for a value for the "INVOICE DATE" parameter.


Click "OK" and the report will run and give you values for the specified date.

Click "OK" to save the changes to the Query and then click "OK" to save changes to the Dataset and return to the main editor.

Click "Run" at the top left:


This will switch to the report run viewer and, as there is a report, will prompt you at the top of the screen for a value:


Enter "20-APR-2010" and either hit return or click "View Report". The report will now run with the specified parameter.

Post a Comment