Click here to Skip to main content
15,860,972 members
Articles / .NET
Article

Adding input parameters to the SQL Report

Rate me:
Please Sign up or sign in to vote.
3.13/5 (8 votes)
8 Apr 20063 min read 143.4K   31   7
This article helps to add input paramters 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:

Add param at Criteria

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.  

Report paramter

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

Report parameter

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.

Preview1

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.

Report parameter

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

Report Preview

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

Preview2

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.

Select column  

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

Right click on textbox

Select the textbox properties and select the navigation tab.

navigation

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

navigation

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.

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.

Parameter value

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. 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
India India
Soshan is a software engineer, currently working in variant projects. She does her programming in stuff like Assembler, C++, MFC and lots of web- and database stuff and now uses ASP.NET and C# extensively, too.

In her free time, she works in animation stuffs with extensive usage of Adobe Photoshop and CorelDraw. Driving racer cars is her current craze.

Whenever she gets bored, her interests goes on to networking and hacking-ethical. Has designed an ip-filter for firewall with a group of friends.

Comments and Discussions

 
QuestionThanks! Pin
protossrulus16-Feb-14 16:43
protossrulus16-Feb-14 16:43 
GeneralFor me its not working!!!! Pin
Member 383455914-Dec-08 20:11
Member 383455914-Dec-08 20:11 
GeneralMy vote of 2 Pin
Member 383455914-Dec-08 20:10
Member 383455914-Dec-08 20:10 
Generalone to many relation Pin
T.Ashraf15-Aug-08 8:57
T.Ashraf15-Aug-08 8:57 
Questionsql reporting service pass criteria Pin
DANNYCH31-Jul-07 0:05
DANNYCH31-Jul-07 0:05 
QuestionCustom Parameter Input & Pin
LeandroTuch26-Apr-06 8:40
LeandroTuch26-Apr-06 8:40 
AnswerRe: Custom Parameter Input & Pin
Soshan Fernandes3-May-06 8:22
Soshan Fernandes3-May-06 8:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.