Menu
I have a dataset (Volume) looks like this:
In my report, what I want to get is this:
The Lookup expression: =Lookup (Fields!Id.Value, Fields!Id.Value, Fields!Volume.Value,"Volume") can only lookup on ID. Is there any way I can do lookup on ID first, and then lookup on Sub_Group to get the correct Volume? Thank you.
348
To retrieve multiple values for a single name or key field where there is a 1-to-many relationship, use LookupSet Function (Report Builder and SSRS). To call Lookup for a set of values, use Multilookup Function (Report Builder and SSRS).
MultiLookup is the equivalent of calling Lookup for a set of names or keys. For example, for a multivalue parameter that is based on primary key identifiers, you can use Multilookup in an expression in a text box in a table to retrieve associated values from a dataset that is not bound to the parameter or to the table.
The ReportItems built-in collection is the set of text boxes from report items such as rows of a data region or text boxes on the paginated report design surface. The ReportItems collection includes text boxes that are in the current scope of a page header, page footer, or report body.
You can Lookup using multiple fields if you concatenate all the necessary fields in the first and second Lookup parameter.
=Lookup(Fields!ID.Value & Fields!SUB_GROUP.Value, Fields!ID.Value & Fields!SUB_GROUP.Value, Fields!VOLUME.Value, "Volume")
Alternatively, you can concatenate all the necessary fields in your dataset and use that for your Lookup.
137
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With