Click here to Skip to main content
13,558,876 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


31 bookmarked
Posted 8 Apr 2006

Adding input parameters to the SQL Report

, 8 Apr 2006
Rate this:
Please Sign up or sign in to vote.
This article helps to add input paramters to the SQL Report
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->


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.


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.


Use Reports to pass parameters

Suppose you need to pass a value from Report 1 to Report 2, then in normal 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.


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.

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. 


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


About the Author

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.

You may also be interested in...

Comments and Discussions

QuestionThanks! Pin
protossrulus16-Feb-14 16:43
memberprotossrulus16-Feb-14 16:43 
GeneralFor me its not working!!!! Pin
Member 383455914-Dec-08 20:11
memberMember 383455914-Dec-08 20:11 
GeneralMy vote of 2 Pin
Member 383455914-Dec-08 20:10
memberMember 383455914-Dec-08 20:10 
Generalone to many relation Pin
T.Ashraf15-Aug-08 8:57
memberT.Ashraf15-Aug-08 8:57 
Questionsql reporting service pass criteria Pin
DANNYCH31-Jul-07 0:05
memberDANNYCH31-Jul-07 0:05 
QuestionCustom Parameter Input & Pin
LeandroTuch26-Apr-06 8:40
memberLeandroTuch26-Apr-06 8:40 
Hi! Congratulations, great article!

But I have two needs and I can get no success yet. Could you help me?

1) I need to pass the query parameters from a custom web form, already developed in an existing application standards. How can I get this?

2) I have some C# classes that already provide the report data into a DataTable. I would like to use this classes in my reports. This classes perfom some processing (my business layer) and is impossible (I think) to build a direct SQL query. How can I get this?

Best regards.
Thanks in advance.

Leandro Tuchtenhagen
AnswerRe: Custom Parameter Input & Pin
Soshan Fernandes3-May-06 8:22
memberSoshan 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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180515.1 | Last Updated 8 Apr 2006
Article Copyright 2006 by Soshan Fernandes
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid