none
ssrs 2008 r2 use 'combined' value parameter

    Question

  • In an existing SSRS 2008 R2 dashboard application, I need to pass the same 5 parameter values between the existing 48 to 62 sub reports that can be called. I am trying to determine the best way to accomplish this goal.

    Listed below are the options I am thinking about:

    1. Cascading parameters would not work because all 5 parameter values are not sub values.

    2. I could pass the 5 parameter values individually to each sub report; however I am thinking there should be an easier option. There would be a high chance of maintenance problems due to the ability of each sub report being able to call each other and the number of parameters that can be passed.

    3. I am thinking that I could pass all 5 parameter values together as 1 combined parameter.

    When I was actually accessing each of the 5 parameter values in each applicable sub report, I would use a 'mid' function call to parse out the 5 values I need. Would this option work? If so how would you setup the code? Would I use some kind of 'custom function'? If so how would you setup the code for the 'custom function'?

    Do you have any other ideas on how I could accomplish my goal? If so, can you tell me what you are thinking of and show me some code or point me to a URL that will accomplish this goal?

    Tuesday, December 24, 2013 9:37 PM

Answers

  • Hi Wendy,

    If I understand correctly, you have five parameters in main report and subreports. You want combine these five parameters as one parameter pass the value to the subreports. As per my understanding, it is not support in SSRS.

    In SSRS, we can configure parameters with default values. When we run the report, it can display these corresponding data in the report. Then, we can according to our requirement to change the corresponding parameters’ values to filter data.

    If these are any misunderstanding, please elaborate the issue for further investigation.

    Regards,
    Alisa Tang

    If you have any feedback on our support, please click here.


    Alisa Tang
    TechNet Community Support

    Wednesday, December 25, 2013 10:27 AM
  • Hi wendy,

    Based on my test, we can use Split().GetValue() function to pass one parameter value to the corresponding parameters in the subreport. Suppose we have 5 parameters in the subreport: Parameter1, Parameter2, Parameter3, Parameter4, Parameter5. Please refer to the following steps to design main report:

    1. Create a dataset (Dataset1) to combine 5 column fields as one column, please refer to the following query code:
      Select Col1+’,’+Col2+’,’+Col3+’,’+Col4+’,’+Col5
      From Table
    2. Then, we will get following value: Col1,Col2,Col3,Col4,Col5 in the dataset.
    3. Create a parameter (MainParameter) get available values from DataSet1.
    4. Add subreport in the report body.
    5. Right click the subreport, select the specify report.
    6. Click Parameters in the left pane, click Add icon to add five parameter, and please refer to the following values:
      Name: Parameter1
      Value: =Split(Parameters! MainParameter.Value,",").GetValue(0)
      Name: Parameter2
      Value: =Split(Parameters! MainParameter.Value,",").GetValue(1)
      Etc.

    If you have any questions, please feel free to let me know.

    Regards,
    Alisa Tang

    If you have any feedback on our support, please click here.


    Alisa Tang
    TechNet Community Support


    Monday, December 30, 2013 9:30 AM

All replies

  • Hi Wendy,

    If I understand correctly, you have five parameters in main report and subreports. You want combine these five parameters as one parameter pass the value to the subreports. As per my understanding, it is not support in SSRS.

    In SSRS, we can configure parameters with default values. When we run the report, it can display these corresponding data in the report. Then, we can according to our requirement to change the corresponding parameters’ values to filter data.

    If these are any misunderstanding, please elaborate the issue for further investigation.

    Regards,
    Alisa Tang

    If you have any feedback on our support, please click here.


    Alisa Tang
    TechNet Community Support

    Wednesday, December 25, 2013 10:27 AM
  • Here is a link that I thought might work to accomplish my goal: http://geekswithblogs.net/GruffCode/archive/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services.aspx.

    Can you tell me if you agree?

    Wednesday, December 25, 2013 7:05 PM
  • Hi wendy,

    Based on my test, we can use Split().GetValue() function to pass one parameter value to the corresponding parameters in the subreport. Suppose we have 5 parameters in the subreport: Parameter1, Parameter2, Parameter3, Parameter4, Parameter5. Please refer to the following steps to design main report:

    1. Create a dataset (Dataset1) to combine 5 column fields as one column, please refer to the following query code:
      Select Col1+’,’+Col2+’,’+Col3+’,’+Col4+’,’+Col5
      From Table
    2. Then, we will get following value: Col1,Col2,Col3,Col4,Col5 in the dataset.
    3. Create a parameter (MainParameter) get available values from DataSet1.
    4. Add subreport in the report body.
    5. Right click the subreport, select the specify report.
    6. Click Parameters in the left pane, click Add icon to add five parameter, and please refer to the following values:
      Name: Parameter1
      Value: =Split(Parameters! MainParameter.Value,",").GetValue(0)
      Name: Parameter2
      Value: =Split(Parameters! MainParameter.Value,",").GetValue(1)
      Etc.

    If you have any questions, please feel free to let me know.

    Regards,
    Alisa Tang

    If you have any feedback on our support, please click here.


    Alisa Tang
    TechNet Community Support


    Monday, December 30, 2013 9:30 AM
  • So for each subreport I have in the dashboard application, those reports would also need to have  a subreport?
    Monday, December 30, 2013 2:50 PM