Compare dataset with another dataset for multiple Columns/Values SSRS

Question

This is the image reference. Let me explain the scenario.

There are two datasets A and B as you can see, dataset B has actual tests values and dataset A has target values (more like ranges). code compares each test (BLK ...) value to the target test (BLK ...) value, code is shown below

=IIF(Len(Lookup("UR_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target")) <= 0,
(
IIF(Fields!BLK.Value > Lookup("UW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow",
        IIF(Fields!BLK.Value < Lookup("LW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow","Green"))
),

(
IIF(Fields!BLK.Value > Lookup("UR_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Red",
    IIF(Fields!BLK.Value > Lookup("UW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow",
        IIF(Fields!BLK.Value < Lookup("LR_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Red",
            IIF(Fields!BLK.Value < Lookup("LW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow","Green"))))
))

Now the problem I face is, that I cant compare each products test (from B) values to that specific products target values in dataset A. What ends up happening is that each test in B is compared by last target product values in dataset A.

Answer 1

Concatenating product and limit fields it is possible as mentioned in comments. The lookup function can use any string you pass to search for a value. Note the following example:

Lookup(Fields!Product.Value & "-" & "UR_Limit",...,"TestTargetDataSet")

It will look for 905200-UR_Limit and return the BLK value for upper limit from the Test Target Values. Of course you have to create a calculated field in the TestTarget Dataset and set it as the concatenation of product and limit fields.

I've recreated your scenario using the tables you provided. First I created the calculated field named PRLimit in the Test Target dataset and set it to this expression:

=Fields!Product.Value & "-" & Fields!Limits.Value

As you mentioned in comments you have multiple columns, you have to use a different expression for each column:

=Switch(
Fields!BLK.Value >
  Lookup(Fields!Product.Value & "-" & "UW_Limit",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Red",
Fields!BLK.Value >
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Yellow",
Fields!BLK.Value =
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Green",
Fields!BLK.Value <
  Lookup(Fields!Product.Value & "-" & "LW_Limit",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Red",
Fields!BLK.Value <
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Yellow"
)

---

=Switch(
Fields!BW.Value >
  Lookup(Fields!Product.Value & "-" & "UW_Limit",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Red",
Fields!BW.Value >
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Yellow",
Fields!BW.Value =
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Green",
Fields!BW.Value <
  Lookup(Fields!Product.Value & "-" & "LW_Limit",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Red",
Fields!BW.Value <
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Yellow"
)

The above expressions are used to get the right color for BLK and BW columns respectively. The logic used is:

• IF BLK value is greater than the UW BLK value: Color in Red
• ELSE IF BLK value is greater than the Target value: Color in Yellow
• ELSE IF BLK value is equal to Target value: Color in Green
• ELSE IF BLK value is less than the LW BLK value: Color in Red
• ELSE IF BLK value is less than the Target BLK value: Color in Yellow

This is the final result with the data provided in your question:

Let me know if this helps.