Search code examples
reporting-servicesssrs-2008reporting

How to join the values of two parameters? (Visual Studio Reports 2008)

I have a report in Visual Studio Reports 2008. My report contains the data source 2, and each data source has parameter.

How to join the values of two parameters?

Example: The values of the parameter 1, parameter values 2

Group1,Class1

Group2, Class1

Group3, Class1

Group4, Class2

Group5, Class2

Group6, Class2

Group7, Class2

Group8, Class2

Group9, Class3

Group10, Class3

Group11, Class3

Group12, Class3

Group13, Class3

I created 3 a parameter added the following function (In Parameter Properties - Available Values - Select Specify values - added function):

=IIf(Parameters!ReportParameter1.Value = " Group1"," Group2"," Group3" THEN Parameters!ReportParameter2.Value = " Class1")

OR

IIf(Parameters!ReportParameter1.Value = " Group4"," Group5"," Group6"," Group7"," Group8" THEN Parameters!ReportParameter2.Value = " Class2")

OR

IIf(Parameters!ReportParameter1.Value = " Group9"," Group10"," Group11"," Group12"," Group13" THEN Parameters!ReportParameter2.Value = " Class3")
Solution

  • You can use expressions to set a determined value for parameter 2 based on parameter 1.

    If your parameter 1 allows multiple value selections, you can use IndexOf function. In Default Values tab of the Parameter 2 properties, select Specify values and add a new expression, use the below: 

    =Switch(
Array.IndexOf(Parameters!MyParam1.Value,"Group1")>-1,"Class1",
Array.IndexOf(Parameters!MyParam1.Value,"Group2")>-1,"Class1",
Array.IndexOf(Parameters!MyParam1.Value,"Group3")>-1,"Class1",
Array.IndexOf(Parameters!MyParam1.Value,"Group4")>-1,"Class2",
Array.IndexOf(Parameters!MyParam1.Value,"Group5")>-1,"Class2",
Array.IndexOf(Parameters!MyParam1.Value,"Group6")>-1,"Class2",
Array.IndexOf(Parameters!MyParam1.Value,"Group7")>-1,"Class2",
Array.IndexOf(Parameters!MyParam1.Value,"Group8")>-1,"Class2",
Array.IndexOf(Parameters!MyParam1.Value,"Group9")>-1,"Class3",
Array.IndexOf(Parameters!MyParam1.Value,"Group10")>-1,"Class3",
Array.IndexOf(Parameters!MyParam1.Value,"Group11")>-1,"Class3",
Array.IndexOf(Parameters!MyParam1.Value,"Group12")>-1,"Class3",
Array.IndexOf(Parameters!MyParam1.Value,"Group13")>-1,"Class3"
)

    Otherwise if your parameter doesn't allow multiple values you can simply use:

    =Switch(
Parameters!MyParam1.Value = "Group1","Class1",
Parameters!MyParam1.Value = "Group2","Class1",
Parameters!MyParam1.Value = "Group3"1,"Class1",
Parameters!MyParam1.Value = "Group4","Class2",
Parameters!MyParam1.Value = "Group5","Class2",
Parameters!MyParam1.Value = "Group6","Class2",
Parameters!MyParam1.Value = "Group7","Class2",
Parameters!MyParam1.Value = "Group8","Class2",
Parameters!MyParam1.Value = "Group9","Class3",
Parameters!MyParam1.Value = "Group10","Class3",
Parameters!MyParam1.Value = "Group11","Class3",
Parameters!MyParam1.Value = "Group12","Class3",
Parameters!MyParam1.Value = "Group13","Class3"
)

    I recommend you set the Parameter 2 to be hidden in order to avoid user change manually the value of the parameter.

    Let me know if this helps.