cynocyber February 2016

Compare dataset with another dataset for multiple Columns/Values SSRS

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

enter image description here

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.

Answers


alejandro zuleta February 2016

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. 

Post Status

Asked in February 2016
Viewed 2,764 times
Voted 12
Answered 1 times

Search




Leave an answer