x

how to pass parameter to ssrs main report and sub report?

Hello there,
I have a main report and a sub report in SSRS 2008 R2 that both get same set of parameters from their datasets and their datasets are both stored procedure. Say I have Main.rdl with @param 1, @param2, @ param3.Those parameter belong to the stored procedure that I used as dataset for my Mainrdl. I have Sub.rdl that also uses sproc as dataset with the same parameter belong to sproc (parameters are all the same and get the same value but values are not statice). because those parameters value will be provided at run time I need to know is there any way that I can pass value to both Main and sub at the same time?

Thanks
more ▼

asked Jul 07 '12 at 03:47 PM in Default

Naz gravatar image

Naz
0 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Since the Main Report Parameters and Sub-report parameters have the same name, you just need to map the sub-report parameters with the respective Main report parameters values. For e.g. for first parameter @param1

Right Click the Sub-Report -> Click Sub-Report Properties -> Click Parameters -> Click Add -> Put param1 in the Name column and put [@param1] (I guess) in the Value column.

Same needs to be done for the rest of the parameters. For more details, here is the link to BOL, where the key part of having the same parameter names is already done by you.

How to: Add a Subreport and Parameters (Report Builder 3.0 and SSRS)

more ▼

answered Jul 09 '12 at 11:33 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thank you much for your respond, I forgot to say those parameters are the parameters of which I used in Sproc to return the result set (Fields that I have in my report) therefore If I had not provide these values before any execution at any mean I had not have any result set to use in report and that is my problem for I think both main and sub will be generated at the same time. So when I used the method you mentioned my report returns "error occurred" and does not run. I don't know if there is a solution to pass the value to Main report and Sub report parameters at the same time and before they executed?

Thanks
Jul 10 '12 at 02:58 AM Naz

The way @Usmann described really works. You are probably missing the parameter mappints in the queries, or have a wrong order of parameters in report or sub report. If some parameter depends on other parameter (eg. a query is used to fill a parameter values and for that query another parameter is used than such parameter has to be in higher position in the parameters list.

Check proper orders and mappintgs of all params. Also in the SSRS log yo uwill find more information why the report doesn't run and probably more detailed error message.
Jul 10 '12 at 05:26 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x112
x72

asked: Jul 07 '12 at 03:47 PM

Seen: 4072 times

Last Updated: Jul 10 '12 at 05:26 AM