Adding input parameters to the SQL Report

Introduction

We can specify input parameters to the SQL Report, based on which the report can display data. If we design a report to display employee information, and if we need to provide a parameter to the report, stating which employees need to be displayed. In normal programming languages, we will create a text-box to input the data from the user and on the click of a button, say 'Display', the report will get generated. In SQL server reporting services, we need not follow this entire procedure. Let us see how.

Use Textbox to pass parameter

I had created a report to display product and order details for a manufacturing firm. In this, I needed to supply a product ID as an input to the report, thereafter the report would display all details about the order for the specified product ID.

In order to implement this, we just need to defne a parameter in the criteria column of the Product ID at the Data tab of the report designer. The snapshot is shown below:

In the Menu, go to Report - > report parameters and here you will see that the parameter defined in 'Where' clause of the Select statement is already present in the Report parameters list.  

You can change the string that will be used to prompt the product ID using 'Prompt'.

Now click on the preview tab and you will see a textbox as an input to be entered for the report. Along with this the 'View Report' button needs to be clicked on entering a valid value.

Use Dropdown to pass parameters

Now, if we need to allow the user to select only a set of parameters, simply open the Report Parameter dialog box. In the available values, select From Query and provide the dataset, value field and the label field from where the values need to be provided.

Turn on the Preview window, and you will see the dropdown containing the set of values.

On click of View Report, the report will be displayed with the filtered values from the dropdown.

Use Reports to pass parameters

Suppose you need to pass a value from Report 1 to Report 2, then in normal asp.net applications, we either use context or query strings. Lets see how we can do this too.

To demonstrate this, I've created 2 reports Parent.rdl and Child.rdl. The child report has in input parameter named ChildProdID. Select any column from the Parent report as shown below.

  

Right click on this field and you will see the following parameters

Select the textbox properties and select the navigation tab.

Select the 'jump to report' tab, and set the report to which you want to jump i.e Child report.

This means that this column will be treated as a hyperlink and on click of this hyperlink, we will jump to Child report. However, we have a parameter that needs to be provided to Child report in order to be executed. Hence, we click on the Parameter button situated at the Navigation tab. On click of this, we get another tab - Parameters.

In the dropdown we can select the parameter name for that report. For the parameter value, select the field that needs to be evaluated to the parameter name for the report to be executed.

Run the Parent report and click on any of the product ID to navigate to the Child report.

Please intimate me, incase there are any problems in these parameter passing strategies.