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.

You problem is different products is being compared, you want `905200` product in Daily Test Data be compared with `905200` product in Target Values, right? — alejandro zuleta, Feb 08 '16 at 20:47
I think there is way to achieve this by creating a calculated field and concatenate the Product and the Limit. I don't understand the limits for BLK in the Test Target Values, note the UW_limit is 141 and the UR_Limit is 110, isn't it supposed to be smaller?. — alejandro zuleta, Feb 09 '16 at 16:46
yes, there is some data wrongly entered. but you have the concept though. i tried concatenating product and limits, but not able to use it in lookup because you can only look for one string in lookup and there is no limit column in test data table (B). — cynocyber, Feb 09 '16 at 17:49

alejandro zuleta · Accepted Answer · 2016-02-09T22:42:43.447

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.

it works thanks, but there is still a little issue around. while using these lookup there is one issue of repetitive rows. for example `905200 - UR_Limit .......................... 905200 - UW_Limit ......................... 905200 - Target .............................. 905200 - LW_Limit .......................... 905200 - LW_Limit .......................... this row gets repoeated ` — cynocyber, Feb 12 '16 at 15:55
If they are repeated on the target table it will cause color be the same for every row of 905200 product in the test table. I don't understand what your problem is — alejandro zuleta, Feb 12 '16 at 16:07
think of target table only, and then i apply the above color logic in the font color section, if i use Switch(....UR_Limit) only it disappears from the table, if i use switch(...UW_Limit ) i gets repeated. let me know if you still dont get it — cynocyber, Feb 12 '16 at 16:38
`=IIF(Fields!BLK.Value = Lookup(Fields!Product.Value & "-" & "2 - Upper Warning", Fields!Product_Desc.Value,Fields!BLK.Value,"Test_Target"),"Tan","Green")` 2 - Upper Warning row disappears from the table, replaces by any row on the top — cynocyber, Feb 12 '16 at 19:37

Compare dataset with another dataset for multiple Columns/Values SSRS

1 Answers1