none
Parameter passing from master to subreport

    Question

  • 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
    Friday, December 09, 2005 9:45 PM

Answers

  • 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.Value
    The 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

    Saturday, December 10, 2005 2:11 AM

All replies

  • 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.Value
    The 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

    Saturday, December 10, 2005 2:11 AM
  • Thank you for the help, it worked out.

    Wayne
    Tuesday, December 13, 2005 4:08 PM
  • 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.
    Monday, January 22, 2007 11:16 PM
  • 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

    Tuesday, January 23, 2007 2:32 AM
  • 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.

     

     

    Tuesday, January 23, 2007 6:33 PM
  • Rob

    Just a quick question- Is Champion your middle name?

    Coz that solved an issue which I was stuck with for last 2 days.

    Thanks Heaps
    Wednesday, March 03, 2010 10:30 PM
  • Hello cvonline,

    I appreciate your feedback and I'm glad it resolve your issue.  My real middle name is "Michael" though :)

    Thanks,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 10, 2010 9:51 PM
  • 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!

     

    Shane


    Novice Software Developer
    Thursday, July 22, 2010 7:18 PM
  • 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?

     

    Thursday, October 21, 2010 11:08 PM
  • 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.

    Wednesday, September 26, 2012 11:12 AM