T-SQL - Report Builder Evaluating Nothing or Null from two data sources IIF, nested IIF, and LOOKUP

Question

I am trying to create an expression in a report that brings data from two different datasets that were created from different data sources. The common fields between the two datasets are Name01 for Dataset01 and Term01 for Dataset02. Once the datasets are synced it takes the field Protect which is a bit data type and evaluates it. If it is 0 then the text output should be "Do Not Protect". If it is 1 then the text generated is "Protect". On another part of the report, there are a couple of fields that will generate text based on an integer, 0 = "Not Installed", 1 = "Installed", 2 = "Pending", and NULL returns "Not Present".

The problem that I'm running into is when the record from Dataset01 does not have a matching record in Dataset02, I want to generate the text "Not Present". Also, I have some records that are in Dataset02 where the Protect field set to 0 or 1, but there is no corresponding record in Dataset01. In this case, want to have the evaluation of the Protect field to show "Do Not Protect" or "Protect" while "Not Present" is output for Dataset01.

I have tried two approaches to this problem:

An IIF statement is run and if the LOOKUP expression yields nothing (i.e. no match) the statement is evaluated as TRUE and outputs the text "Not Present". If it evaluates to FALSE, another IIF statement evaluates the Protect field from the LOOKUP expression and if it is 0 the statement is TRUE and the text "Do Not Protect" is output. If the statement evaluates to FALSE (i.e. Protect = 1) then the text "Protect" is output.

=IIF((LOOKUP(First(Fields!Name01.Value, "Dataset01"), Fields!Term01.Value, Fields!Protect.Value, "Dataset02") IS NOTHING), “Not Present”, IIF((LOOKUP(First(Fields!Name01.Value, "Dataset01"), Fields!Term01.Value, Fields!Protect.Value, "Dataset02")=0),“Do Not Protect”, "Protect"))

The second approach has a similar structure. An IIF statement that evaluates to TRUE if the Protect field equals 0. If FALSE, (i.e. Protect = 1 or is NULL) then a second IIF statement is evaluated. If the Protect field equals 1 then the statement evaluates as TRUE. If Protect is neither 0 nor 1, then it is NULL or Nothing and evaluates to FALSE and the text "Not Present" is output.

=IIF((LOOKUP(First(Fields!Name01.Value, "Dataset01"), Fields!Term01.Value, Fields!Protect.Value, "Dataset02")=0), “Do Not Protect”, IIF((LOOKUP(First(Fields!Name01.Value, "Dataset01"), Fields!Term01.Value, Fields!Protect.Value, "Dataset02")=1),“Protect”, "Not Present"))

I'm a novice at T-SQL and I am working on this using Report Builder. If Report Builder has limitations and I need to do something more advanced to make this work using Visual Studio, then I can give that a shot. Any advice or direction that someone can give me will be appreciated.

score 0 · Answer 1 · answered Apr 29 '15 at 20:29

It sounds like you are not using T-SQL but are instead trying to evaluate an expression in the report itself. In T-SQL the requirement is simple to meet using a LEFT JOIN, CASE and the ISNULL() function like this:

SELECT
    Dataset01.*,
    CASE ISNULL(Dataset02.Protect, 0)
        WHEN 0 THEN 'Do Not Protect'
        ELSE 'Protect'
    END AS SimpleCase,

    CASE 
        WHEN Dataset02.OtherField = 0 THEN 'Not Installed'
        WHEN Dataset02.OtherField = 1 THEN 'Installed'
        WHEN Dataset02.OtherField = 2 THEN 'Pending'
        WHEN Dataset02.OtherField IS NULL THEN 'Not Present'
    END AS SearchedCase

FROM
    Dataset01

        LEFT JOIN
    Dataset02
        ON Dataset01.Name01 = Dataset02.Term01

This nicely demonstrates the two forms of the CASE statement in TSQL. In the first of these I've used ISNULL to check for nulls and replace it with a value. The second just directly checks for null.

This TSQL statement needs to be the Datasource for the report and the Dataset01/02 need to be replaced with the tables in the database you are querying.

Rather than doing lookups in the report it is generally faster to do joins in TSQL, as it is much better at dealing with large sets of data without having to evaluate it row by agonising row (RBAR). The report is a good place to bring related data together to present, i.e. Invoice header to Invoice lines.

T-SQL - Report Builder Evaluating Nothing or Null from two data sources IIF, nested IIF, and LOOKUP

1 Answers1