Answered by:

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?

Answers

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.

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:

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

Then, we will get following value: Col1,Col2,Col3,Col4,Col5 in the dataset.

Create a parameter (MainParameter) get available values from DataSet1.

All replies

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.

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:

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

Then, we will get following value: Col1,Col2,Col3,Col4,Col5 in the dataset.

Create a parameter (MainParameter) get available values from DataSet1.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.