Parameter passing from master to subreport
Is it possible to pass a multi-value parameter from a master report to one or more subreports? If so, how?Thanks for any help.Wayne E. Pfeffer
December 9th, 2005 4:45pm

Yes you can do this. I suggest the following: In your subreport make the report parameter a multi-value parameter. Test the subreport stand alone and make sure it works. Then drop the subreport into your main report, and map the subreport parameters to the main report parameters.Scenario 1 (multi value parameter pass through):The Main report has report parameter A which is a multi value parameter. You want to pass it to SubReport1 which has a report parameter B which is also defined as multi value parameter. In this case the mapping is just a simple parameter expression: =Parameters!A.ValueThe subreport RDL element would look like this: <Subreport Name="SubReport1"> ... <Parameters> <Parameter Name="B"> <Value>=Parameters!A.Value</Value> </Parameter> </Parameters> </Subreport> Scenario 2:The Main report has no report parameters. You have a SubReport1 with a report parameter B defined as multi value parameter. You want to pass e.g. three selected values "A", "B", "C" as parameter values to the subreport. You need to create a multidimensional object array on-the-fly, e.g. with the Split function, e.g.: =Split("A,B,C", ",")RDL example: <Subreport Name="SubReport1"> ... <Parameters> <Parameter Name="B"> <Value>=Split("A,B,C", ",")</Value> </Parameter> </Parameters> </Subreport> Scenario 3:The Main report has a multi-value parameter A. You want to pass only the first selected value from the main report's parameter to the subreport and the subreport's report parameter B is a single-value parameter. You can do this by using e.g. =Parameters!A.Value(0)RDL example: <Subreport Name="SubReport1"> ... <Parameters> <Parameter Name="B"> <Value>=Parameters!A.Value(0)</Value> </Parameter> </Parameters> </Subreport> -- Robert
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2005 9:11pm

Thank you for the help, it worked out.Wayne
December 13th, 2005 11:08am

Is there a limit on how many parameters you can pass if you are using Scenario1. I have 5 parameters and 1 subreport. I can pass in the first 4 (A,B, C,D) and the report works fine but when I try to pass in the last one (E), which is no different than the previous 4 parameters other than it might have a few more values than the others, I get an error saying that one or more parameters need has not been specified.
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2007 6:16pm

There is no limit on the number of parameters. Instead, it seems like the parameter values passed in violate constraints defined for the subreport parameters. For example, the subreport parameter's data type is integer and you pass string values. Or you pass a NULL value ('Nothing' in VB) as one of the parameter values - which is not allowed for multi value parameters, etc. -- Robert
January 22nd, 2007 9:32pm

Okay, I am sort of new to reporting services...where would I go to check the data type?..I don't believe anything is a null but where would I go to check that? The subreport works fine on its own...The master report will be/is populated by stored procedures so at first I thought something may be off with the stored procedures but all the other parameters work just fine when hooked up to the subreport's parameters. Currently, the subreport is running off of data from the cube however, it has been suggested that I change the subreport to pull from the same stored procedures as the master report and it will work fine...which I don't understand because again the first four parameters work fine with out using the stored procedures. Any further assistance would be greatly appreciated. Thanks.
Free Windows Admin Tool Kit Click here and download it now
January 23rd, 2007 1:33pm

RobJust a quick question- Is Champion your middle name?Coz that solved an issue which I was stuck with for last 2 days.Thanks Heaps
March 3rd, 2010 5:30pm

Hello cvonline,I appreciate your feedback and I'm glad it resolve your issue. My real middle name is "Michael" though :)Thanks,RobertRobert Bruckner http://blogs.msdn.com/robertbruckner This posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
March 10th, 2010 4:51pm

Robert, I've seen you add posts all over the web, helping hacks like me. Thank you for taking the time. I used your solution(s) above and it resolved my issue I was banging my head on. Thank you, thank you! ShaneNovice Software Developer
July 22nd, 2010 3:18pm

One thing I think worth to emphasize, since it's a multi-valued parameter, the query in sub report has to use the "in" syntax: "WHERE entityId.... in...@paramName". Thanks! Another Story: At work I'm required to implement a report for "Account" entity, and it needs to pick up the currently selected account and run automatically if there is only one account selected, and when there are multiple selected accounts, it needs to show the parameter drop down, and let the user select first before running. Below is how I implemented it, please advise if there is a better way: 1) Create one "hidden" parameter" called "prefilteredAccountId", with its "Available Values" and "Default Values" both from query: SElECT CRMAF_FilteredAccount.accountid, CRMAF_FilteredAccount.name from FilteredAccount CRMAF_FilteredAccount order by name asc 2) And then add a second parameter with its "Available Values" from query: SElECT accountid, name from FilteredAccount where accountid in (@prefilteredAccountId) And its "Default Values " come from dataset/query: declare @count int SELECT @count = count(*) from FilteredAccount where accountid in (@prefilteredAccountId) IF @count < 10 SElECT accountid, name from FilteredAccount where accountid in (@prefilteredAccountId) ELSE SElECT top 0 accountid, name from FilteredAccount where accountid in (@prefilteredAccountId) In other discussion it was mentioned that prefiltering only works for the first dataset. When I read that, I didn't really get it, but now according to my test, yes, it has to be the "first dataset", literally ! In the source code of .rdl file (which is an XML file), I copied&pasted the definition of datasets so as to put the right one to be the first data defined, and then it appeared as the first dataset in the "Datasets" dropdown, and prefiltering starts to work...Can somebody confirm this? I didn't this mentioned in MS documents, this can be a big source of confusion right?
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 7:08pm

Hi Robert, what about scenario 4: The main report has a single parameter value. The sub report has a multi-value parameter. I want just the one from the main report passed in... I can't seem to get it to work at all. If I leave the parameter off it works (but runs as if all the parameters were selected, whereas if I add the parameter then I get the 'One or more parameters required to run the report have not been specified.' which is odd as at the point I have specified it!!! Can you help? Penny.
September 26th, 2012 7:17am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics