Tuesday, May 20, 2008

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.
    SelectReportParameter
  3. When you click on Report Parameters Tab the following Screen appears. From this we can add as many parameters as we want.
    EnterReportParameter

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:

AddFilter

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

SelectReportFilterLabel

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:

SelectReportFilterValue_Expression

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

SelectReportFilterValue_ParameterExpression

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:

Preview_ParameterizedReport

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.

11 comments:

Pete said...

Thanks. Just what I was looking for!

Sunil Shrikhande said...

select * from Branches as ObjProdOff
where (ObjProdOff.BranchID IN (@BranchID)OR (select count(*) from Branches where BranchID IN (@BranchID)) = 0)
it will work..:)

Anonymous said...

any idea why do u get an error Default value or value provided for the report parameter 'ProcessNm' is not a valid value

that too only when i select all during execution

siri said...

Hello,
I have a doubt in creating SSRS reports,can we create a report have different tab values for example, 3 different states in tabs like in excel and show there values in one report.Your help would be great!!

Unknown said...

Thanks, Could you please explain about the Stored procedures with parameters in [SSRS]

Nilesh Upadhyay said...

Thank you guys for your comments..

For martinpaulhere: HI Martin..Thanks for reading my article it may be helpful to you.

Default value error might be come if your parameter type is different than your provided input.
For e.g. ProcessNm may take integer data type of not null and if you passed the record of either null / empty or non integer value then it may give u error.

For actual reason of error need to see your code than can we find out what is wrong..

bkarle said...

I have set an internal parameter (boolean) and I am checking whether it is true or not based on certain conditions. I want to be able to prevent a report execution on clicking of "View Reports" - but for some reason, even when the "internal" parameter is invalid, it still executed some long running code. if I can consult with you about this, it would be great.

Is there a "processing" decision made by SSRS based on validity of Internal paramater? How about Hiddent paramater? how about a Hidden + Internal parameter?

veerla said...

great. thanks alot. i got what i want. very use ful.

Sanal said...

thanks a lot ...really helpful

Anonymous said...

Nice blog. The content of your blog is exactly wonderful, and your blog template is Simple generous. So good.
Hire magento designer

chicha24 said...

Do you know another way of sending parameters to SSRS report from an asp.net page other than using URL?