reporting-servicesparametersreportingmultivalue

SSRS: How to use custom IDs instead of index values in multi-value parameters?

I'm trying to pass the custom Id in the multivalue parameter. Basically by default multivalue parameter accepts only indexes like 0,1 and its returns data on the base of that index but my current SSRS requirement that use the custom ID in the multivalue parameter.

I have a dataset that uses the XML Datasource and the dataset returns two fields textId and textTranslation.DataSet

First I have created a new multivalued parameter. The multivalued Report Parameter have the following characteristics:

Hidden = True

Allow Multiple Values = True

Available Values tab:

Get Values from a Query and Chose the desired dataset and Select the searchable id as Value id, and the field you want to expose as Label Field in my case searchable id is textId and Label Field is textTranslation.Available values tab

Default Values Tab.

Get Values from a Query and Choose the same Dataset as chosen in the Available Values Tab. Value Field is the same as you choose for value id in my case that is textId.Default Values tab

Now I want to get the value of the Label(textTranslation) in my expression on the base of textId not by the index(0,1)

This is the default expression of mulitvalue paramter to get value of lable.

= Parameters!multivalue.Label(0)

I tried this, but it doesn't work:

= Parameters!multivalue.Label(textId)

Solution

  • You cannot reference parameters like that unfortunately, however you can simply use LOOKUP() to achieve the same thing.

    Try

    =LOOKUP(Fields!textId.Value, Fields!textId.Value, Fields!textTranslation.Value, "XMLAPIDataSet")

    You did not say how you where trying to supply textId to your original expression so you might have to edit the first argument above to get the correct value.

    In case you are not sure what is happening here LOOKUP() works like this...

    =LOOKUP(FindThisValue, LookInThisField, ReturnThisField, LookInThisDataSet)

    If FindThisValue is a field reference (as in my exmaple) then it takes the current context into account, so if the expression is in a table, the Field.Value will be whatever the value of the field is for the table row.