How to design parameterize SSRS report.

In my previous article we learned How to design simple SSRS Report. The sample report contains the list of Employees and their Details from “North wind” database.

Now we are moving to design specific reports. I mean to say that in real world we have requirements to make reports for some specific purpose.

Suppose we need to find the particular details of an employee. Report Details include, Full Name of an Employee, Designation, Address, City, Region, and Country and most important is Employment Date (Hire Date of an Employee).

Lets Continue with the previous example of my previous article Simple steps for developing SSRS Report as we are more familiar with it now. Lets modify our requirement instead of listing details of all employees; lets provide facility to display report for particular employee.

This is the time when we required Report Parameter. We have to design the report in such a way that provides us the details of any employee (of course from the available entries in database).

We already designed the Simple Report, which displays all the employees and their details. For more details on How to Create Simple Report please first read my previous article on Simple steps for developing SSRS Report .

To add Report Parameter:

1. For adding Parameters to Report move your cursor towards Menu and clicks on Report Menu.
2. Choose Report Parameters Tab.
   
3. When you click on Report Parameters Tab the following Screen appears. From this we can add as many parameters as we want.
   

Here we can see 2 sections:

1. Parameters (In Left Side)
2. Properties (In Right Side)

From Parameter section we can add or remove the Parameters.

To Add New Parameters click on Add Button. After adding parameter, we have to set the Parameter Properties from the Properties Section (in Right side).
Here we have requirement that from the employee id we have to display the details of an employee.

So for that we need to add new parameter called “EmployeeID” which can helps us to retrieve the details of particular Employee.

After adding the Report Parameter we need to set properties of the added parameter. The parameter has following properties, which we have to set.

Parameter Properties:

• Name - Shows the parameter name and its must be unique name.
• Data Type – Data Type of Report
• Prompt - Shows the Display Name for Parameter in Report Viewer Control

The prompt string defines the label that identifies the parameter in the input area. The prompt can be the name of the parameter or directions to the user, for example, "Name" or "Enter Name". If the prompt is left blank and a default parameter value is specified, the default value is used, and the input box for the parameter is not displayed when the user runs the report. If prompt is left blank, and no default parameter value is specified, the report cannot run. If you do not want to prompt the user for parameter values, mark a parameter as Hidden or Internal.

Here we need EmployeeId as a parameter, so we can set the above properties like this way:

• Name - EmployeeID
• Data Type– Integer (As EmplyeeID is having integer data type in north wind database)
• Prompt - Employee ID

We can have more options to set the parameter behavior. We have more options to set the more properties, which we find in Check Boxes as like following:

• Hidden
• Internal
• Multi Value
• Allow Null Value
• Allow Blank Value (Not for Integer Data Type)

Lets see what ways the above properties are useful for parameter while designing the report.

• Hidden
  By using Hidden Parameter, you can hide the parameter on the parameter input area of the published report, yet set values for it on a report URL or in a subscription definition.
• Internal
  If you set a parameter to Internal, it is not exposed in any way except in the report definition. In other words Internal Parameter is a parameter that cannot be changed at runtime. A consumer of a published report will never see this as a parameter.
• Multi Value
  If you want to display multiple selection then MultiValue parameter is used.
  
  For e.g. Suppose we want to display list of Employees into Drop Down List Box and allowed user to choose among of the listed, we can set the MultiValue Parameter.
  
  For this lets make a new DataSet (suppose dsEmployees) to bind the Listbox with Employees
  
  For e.g. SELECT EmployeeID, EmployeeName From the Employees
  
  And then Click on Available Values section and Choose From Query Section. You have to select the associated data set (here dsEmployees) and then you can assign Value Field =EmployeeID and Label Field = EmployeeName
  
  When you see the Report Preview, you can see the Drop Down List box having filled up with employee names. When you select the Employee Name the associated EmployeeID will pass as a report parameter.
• Allow NULL Value
  Allow Null Value indicates that the parameter can have null values.
• Allow Blank Value
  If we want to allow an empty string as a valid value then we have to set this parameter.

After all the required parameters are defined, press OK Button.

Now turns to Add filters on our report. User can add filter from the Properties Window like shown in following screen:

When you press ” …” Button near Filter Properties, the window will open to set the filter criteria(s). Like following:

In this screen you can see the 4 Columns.

Expression Column	To Set the Filter on which Field.
Operator Column	Which Operator (=, <, >, Like, >=, <=, etc.)
Value Column	To Set the Value of the Field, which we set in Expression Column
And/Or	The Relation with different Filter Criteria

To set the Value Field you can add an Expression like following:

Here we want to add the value field of the Parameter Value ( EmployeeID). So we can add Parameter in the Expression window like following:

Here we set all required things that need for adding parameters are finished now Press OK and run the Report in Preview mode.

When you press View Report Button without Entering the Employee ID the report viewer shows an error regarding the Employee ID Report Parameter cannot have null value.

We need to pass the Employee ID parameter of an Integer type. Lets insert Employee ID=1 into the Employee ID Text Box and click on View Report.

You can see the details of an Employee which has Employee ID =1 like following:

These are the steps by which we can design the attractive SSRS Reports with the help of different parameters.

Conclusion:
When you are using reports, you need to have parameters that narrow down your report for better analysis. Parameters allow for dynamism by adding user input to the report-rendering process. A number of parameter options as well as a number of ways SSRS prompts the user are available using familiar controls. Parameters can be data-driven, or they can have a static list of valid values.
Over the course of this article, you have learned what report parameters are and how they can be used. This includes their use in queries or in expressions that can be used throughout the report.