Multivalue parameter as vbscript function input in SSRS

Question

I'm trying to modify following code, which gives value for query in report file. Old one works if there is finite number of values, modified should be universal for any number of values.

<Value>=Switch(Parameters!TagHum.Count-20=1, Parameters!TagHum.Value(0)
,Parameters!TagHum.Count-20=2, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=3, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=4, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=5, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=6, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=7, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=8, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=9, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=10, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=11, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=12, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=13, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=14, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=15, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(14) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=16, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(15) & ";" & Parameters!TagHum.Value(14) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=17, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(16) & ";" & Parameters!TagHum.Value(15) & ";" & Parameters!TagHum.Value(14) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=18, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(17) & ";" & Parameters!TagHum.Value(16) & ";" & Parameters!TagHum.Value(15) & ";" & Parameters!TagHum.Value(14) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=19, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(18) & ";" & Parameters!TagHum.Value(17) & ";" & Parameters!TagHum.Value(16) & ";" & Parameters!TagHum.Value(15) & ";" & Parameters!TagHum.Value(14) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
,Parameters!TagHum.Count-20=20, Parameters!TagHum.Value(0) & ";" & Parameters!TagHum.Value(19) & ";" & Parameters!TagHum.Value(18) & ";" & Parameters!TagHum.Value(17) & ";" & Parameters!TagHum.Value(16) & ";" & Parameters!TagHum.Value(15) & ";" & Parameters!TagHum.Value(14) & ";" & Parameters!TagHum.Value(13) & ";" & Parameters!TagHum.Value(12) & ";" & Parameters!TagHum.Value(11) & ";" & Parameters!TagHum.Value(10) & ";" & Parameters!TagHum.Value(9) & ";" & Parameters!TagHum.Value(8) & ";" & Parameters!TagHum.Value(7) & ";" & Parameters!TagHum.Value(6) & ";" & Parameters!TagHum.Value(5) & ";" & Parameters!TagHum.Value(4) & ";" & Parameters!TagHum.Value(3) & ";" & Parameters!TagHum.Value(2) & ";" & Parameters!TagHum.Value(1)
) </Value>

It could be solved by for cycle, but SSRS dont support cycles, so i think i could include vbs file with function like this:

    Public Shared Function TagTempHum_parse(Param_count,Param_values)
    Dim arr1()
    Dim result
    Dim TagCount = Param_count - 20
    Dim i = 0
    For i = 1 To TagCount
        result = result & Param_values(i) & ";"
    Next
    result = Left(result, Len(result) - 1)
    TagTempHum_parse = result
    End Function

and in SSRS call this function:

<Value>
=Code.TagTempHum_parse(Parameters!TagHum.Count,Parameters!TagHum.Value)
</Value>

My first question is: Is this method even possible? Second problem: I need to pass all Parameters!TagHumValue values to function, how can I do this when I dont know how many are there?

Sorry if my questions are dumb, Im new in SSRS. Will be glad for any help. Thanks.

Hannover Fist Hannover Fist · Accepted Answer · 2016-10-04T23:59:42

Are you trying to put your parameter selections in a single string?

You can use JOIN to put them into a single text line:

=Join(Parameters!TagHum.Value, ";")

https://msdn.microsoft.com/en-us/library/aa337292(SQL.90).aspx

Multivalue parameter as vbscript function input in SSRS

1 Answers