In Report Builder 2.0, parameters are used to specify the data to use in a report, connect related reports together, and vary report presentation. To design a report that uses parameters effectively, you must understand how parameters and dataset queries work together, how parameters and expressions work together, how parameters can be managed on the report server for a published report, and what questions a report is designed to answer. What you choose to parameterize can influence the report design and layout.
This article will not cover all the details of reporting parameters usage. If you would like more information on using report parameters, please visit http://msdn.micorosft.com.
In this post we will present how to create a parameter for both single value, query values and mutiple selection. We are using the report "SalesByFiscalYear" which we had created previously in the Introduction to Report Builder 3.0 Part 2 - Creating a Report post.
Parameter Default Settings
Report parameters that are automatically created use the following defaults:
- Data type Text
- Prompt set to the name of the parameter
- No default values
- No available values
Creating a Parameter
1) Right click on the dataset from under your data source. This could be labeled DataSource1 or a custom name you provided.
you should see the following
2) From the upper left corner of the Query Designer window, click Edit As Text to switch to the visual designer. Click OK if prompted to move to the visual designer.
you should see something similar to below
3) Edit the Query Designer window so it matches the image above. The details are below.
- Expand the Sales node and then the Views node.
- Check the vSalesPersonSalesByFiscalYear.
- Click Run Query from the top toolbar to ensure your query works. You should see something similar to below.
4) From the middle pane of the Query Designer window you will see Applied Filters. Click the Filter Icon on the toolbar. Doing so will automatically add a parameter in the grid, with a default operator and [none] value.
5) Now we will create the first parameter. Follow the steps below:
- Change the 'Field name' value from SalesPersonID to FullName.
- Make sure the operator value is set to "is any of".
- Leave the Value field set to (blank).
- Check the box Parameter.
you should now have the same setting as the image below.
5. Click OK from the bottom right corner of the Query Designer window.
6) From the upper right corner of Report Bulider, click Run. You should see the following:
7) Enter "Michael G Blythe" in the Full Name field and click View Report on the far right. You see similar results:
You have completed the first step in creating a parameter. The following approach wouldn't support a real production release, due the possibility of entering names incorrectly. So we would need to create a better approach in limiting end-user error. We will now modify the report parameter to accomodate query values.
Query Value Parameters
This section will walk you though modifying the FullName parameter so that it provides a dropdown list of possible values.
1) You should be back in the Design mode of Report Builder, if not already.
2) Right click you Data Source and select Add Data Set
you should see the Dataset Properties window open
3) Enter a name for your new dataset (e.g., SalesPerson).
4) Pass the following T-SQL script to the Query textbox:
RTRIM(Sales.vSalesPersonSalesByFiscalYears.FullName) AS 'FullName'
5) Click OK to close and save your changes.
you should see a similar Query Design layout
6) From under the Parameter node, right click on the @FullName parameter and select Parameter Properties. The Report Parameter Properties window will open.
7) From the left pane of the Report Parameter Properties window, select Available Values.
8) Select the "Get values from query" option
9) Modify the properties as shown in the image below:
- Dataset = SalesPerson
- Value Field = FullName
- Label Field = FullName
Allow for Multiple Values
10) From the left pane, select General.
11) Under the Data Type section, check "Allow multiple values". Your screen should look similar:
12) Click OK to save your change and close the window.
13) From the top left of the Ribbon, click Run.
14) Select one or more names from the FullName multi-select dropdown list.
15) Click View Report
You she see something similar after running your report.
That is it. Save your report again or to a new location.
In the next post, we'll discuss how to build cascading parameters.