14

I have a dataset (Volume) looks like this:

enter image description here

In my report, what I want to get is this:

enter image description here

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.

C.C
  • 179
  • 1
  • 2
  • 13
  • Is Volume your only dataset, or are you using Lookup to match it to another dataset? – Pete Rennard-Cumming Feb 29 '16 at 12:44
  • Volume is not the only dataset. I am using it to get Volume in the report. The report is based on a different dataset :) – C.C Feb 29 '16 at 12:49
  • You have to post both datasets to determine how the lookup expression should be. Do you want to join both datasets in a matrix? – alejandro zuleta Feb 29 '16 at 12:54
  • thanks:)The main dataset has ID, that's the only key can link to Volume DS. I tried to avoid join the two datasets, but I think the best way is using LookUp... – C.C Feb 29 '16 at 13:54

1 Answers1

28

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.

Pete Rennard-Cumming
  • 1,588
  • 10
  • 19
  • Thank you, I understand the logic. But the Fields!SUB_GROUP.Value is not in the main dataset, which I am using for the report. Any other way around? – C.C Feb 29 '16 at 13:50
  • So I take it columns A, B and C are static columns in a tablix, rather than a matrix column group? If so, you could hard-code the subgroup into the expression in each column. `=Lookup(Fields!ID.Value & "A", Fields!ID.Value & Fields!SUB_GROUP.Value, Fields!VOLUME.Value, "Volume")` – Pete Rennard-Cumming Feb 29 '16 at 13:58
  • Yup, this is what I need. A, B and C are not static columns, but I can figure out a way by following the logic you suggested. Thank you very much. – C.C Feb 29 '16 at 14:13