locked
How to pass a multi valued parameter from master report to sub report RRS feed

  • Question

  • Hi 

    I am trying pass a parameter from the main report to sub report but the thing is the parameters are getting passed but the values not getting fetched. based on the parameter.

    my parameter name in both the reports are same and I am using the following query

    Split(Join(Parameters!paramname.value,","), ",")

    please suggest me what to do.

    SQL Server Reporting Srevice 2012

    Thanks

    Subrat Kumar




    • Edited by KumarSu Wednesday, September 17, 2014 10:26 AM
    Wednesday, September 17, 2014 10:15 AM

Answers

  • Try to display the incoming parameter values on the subreport to check if that part is working as expected.  Can be done by putting the following in a textbox:

    =Join(Parameters!paramname.Value, ", ")


    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence

    Wednesday, September 17, 2014 12:28 PM
  • Hi Vranken,

    Its showing an error when I am trying to write

    Parameters!paramname.Value

     but its displaying the perfect value if at all I am writting 

    =Join(Parameters!paramname.Value, ", ")


    • Edited by KumarSu Friday, September 19, 2014 5:15 AM
    • Marked as answer by Katherine Xiong Friday, September 19, 2014 5:53 AM
    Friday, September 19, 2014 5:13 AM

All replies

  • In the Parameter settings of the subreport you can just pass the value like this:

    =Parameters!paramname.Value

    So make sure to remove the (0) which is added by default if you use the GUI.  The parameter in the subreport itself needs also to be set up as multi-valued for this to work as expected.


    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence


    • Edited by Valentino Vranken Wednesday, September 17, 2014 12:28 PM I wrongly typed "Fields" instead of "Parameters", corrected
    Wednesday, September 17, 2014 11:02 AM
  • Thank for the reply Vranken.

    The same thing I have done but its not working.

    Thanks,

    Subrat Kumar

    Wednesday, September 17, 2014 11:50 AM
  • How did you implement the query in the subreport?  Did you use IN?

    WHERE SomeField IN (@paramname)


    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence

    Wednesday, September 17, 2014 11:55 AM
  • Yah I am using IN operator and in the same way you mentioned

    the thing is for one value it is working for when I am entering multiple values its not working

    Wednesday, September 17, 2014 12:10 PM
  • Are you getting an error? If yes, which one?

    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence

    Wednesday, September 17, 2014 12:17 PM
  • I am not getting any errors but the records are also not visible

    Thanks 

    Subrat Kumar

    Wednesday, September 17, 2014 12:21 PM
  • Try to display the incoming parameter values on the subreport to check if that part is working as expected.  Can be done by putting the following in a textbox:

    =Join(Parameters!paramname.Value, ", ")


    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence

    Wednesday, September 17, 2014 12:28 PM
  • Hi Subrat,

    After testing the issue in my environment, it works very well. To verify that the setting your have configured correctly, please refer to the steps:

    1. Drag a Subreport control from toolbox to design surface.
    2. Right-click the Subreport to open the Subreport Properties dialog box, then select the correct report as subreport.
    3. Click Parameters in the left pane.
    4. Add parameters as below (Parameter1 is a single-value parameter, Parameter2 is a multi-value parameter in subreport ):
      Name: Parameter1              Value: [@ReportParameter1]
      Name: Parameter2              Value: =Split(join(Parameters!ReportParameter2.Value,","),",")
    5. Make sure the subreport works well with those parameter values. We can directly select or type the same values in subreport to check this.

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Thursday, September 18, 2014 11:30 AM
  • Hi Vranken,

    Its showing an error when I am trying to write

    Parameters!paramname.Value

     but its displaying the perfect value if at all I am writting 

    =Join(Parameters!paramname.Value, ", ")


    • Edited by KumarSu Friday, September 19, 2014 5:15 AM
    • Marked as answer by Katherine Xiong Friday, September 19, 2014 5:53 AM
    Friday, September 19, 2014 5:13 AM
  • Hi Katherine,

    Thanks a lot for your reply.

    I have done this way as well, but still no fruitful result

    Thanks,

    Subrat Kumar


    • Edited by KumarSu Friday, September 19, 2014 5:14 AM
    Friday, September 19, 2014 5:14 AM