Parameters allows the users to control the report data, it filters the report dataset based on input value provided to parameter using a text box.
You can also create a multi-value parameter which allows you to pass either one or more than the input value to filter the report data.
Lets, see the implementation of multi-value parameter step by step.
First, we create a Embedded DataSource for report.
Just right click on Data Sources folder, a DataSource Properties window opens.
Now, provides a details for DataSource connection string as shown below.
If you do not know how to create a Data source you can refer post Create an Embedded Data Source.
After that click on OK button.
Now, we will create a Embedded Data Set for report.
If you do not know how to create an embedded data set then you can refer post create an embedded data set in ssrs.
Lets, filter the report data for Class.
To creating a multi-value parameter which takes multiple values as an input from user and filter the report data.
We create a query which contains a variable that is defined as @ParamClass as shown in below screenshot.
This parameter will be used to supply input for the report and based on that report data will be filtered for Class.
You can use any condition in Where clause, but the main thing is that you must define a variable in query otherwise you will not be able to filter the query result for user specified input values.
After that just, click on OK button.
Once, you click on OK button. You will see a parameter named @ParamClass has been created automatically inside a Parameters folder.
Now, we need to associate it to the Class column values of the Product table.
For this, lets create a new dataset and associate the returning values to @ParamClass so that we can filter the DataSet1 query according to these values.
We will add a new dataset named DataSet2, which returns a distinct class values as shown below.
Once, you click on OK button, you can see now we have two datasets named DataSet1, and Dataset2, and one parameter named @ParamClass.
Lets create a simple table report that uses the dataset1 fields as shown below.
Now, right click on parameter @ParamClass, and select Parameters Properties from context menu.
You will see, a Report Parameter Properties window opens.
In General tab, you can see following options:
- Name: specify the valid Parameter name, by default it is PramClass, that you can change else go with default name.
- Prompt: specify a short text that will display as a label before the text box, by default it is Pram Class, that you can change else go with default text.
- Data Type: as class name is a Text data type so keep data type as it is, set to default Text.
Just check Allow multiple values, as we need a parameter that takes a multiple values.
After that just click on Available Values tab, now we will associate a relation between dataset query result values and parameters.
Select the Get values from a query option, so that we can create a connection between dataset, and parameter.
In Dataset, will choose a values for parameter, so select Dataset2, as it returns a distinct values for class, so
After that, we can specify the value field and label field for the parameter which are as follows:
- Value field :specifies the values which are passed to the queries, select class.
- Label field :specifies the values which are displayed in the report, select class.
Now, if you like to provide a default value for parameter, just click on Default Values Tab.
Click on Ok button, after that go to preview page to see the report output.
You can see, by default report filters the data for Class value L.
Now, when you click on Param Class drop down you will see it takes multiple values that means it is multi-value parameter.
Lets filters the report data for class values H and M, first select values H and M from Param Class drop down then click on View report button.
You can see, it filters the report data for class values H and M.
Also Read..
Create a SSRS Tabular Report / Detail Report
Keep row header visible while scrolling down, or on each page
Add alternate row background color
