Getting Scope error on SUM(IIF()) of rows from other table

Question

Currently getting a scope error using the code below, what we're trying to do is count the number of rows that match the conditions we have:

=
SUM(
IIF(
    Fields!Defect_Category.Value = "Packaging"
    & Fields!Defect_Category.Value = "Major"
    & Fields!WorkOrderDisplayID.Value = Fields!Work_Order_Id.Value,
1, 0),
"dsDefects"
)

Work_Order_Id is the "key" of the dsGeneral dataset which is the current scope/dataset of the tablix where we're trying to implement this. Any way we can fix this?

My understanding is that the Scope parameter of SUM is referring to the dataset we're trying to get the sum of (or count of, in this case). When I specify "dsDefects" as the scope of SUM, I get the following error:

The Value expression for the text box 'Textbox101' refers to the field 'Work_Order_Id'. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

However, if I remove the scope parameter value, I'm getting the following error:

The Value expression for the text box 'Textbox101' refers to the field 'Defect_Category'. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

Answer 1

I think you will need something like this...

=
IIF(
    Fields!Defect_Category.Value = "Packaging"
    & Fields!Defect_Category.Value = "Major",
    LOOKUPSET(Fields!WorkOrderDisplayID.Value, Fields!WorkOrderDisplayID.Value, Fields!WorkOrderDisplayID.Value, "dsDefects").Length,
    0)

I'll give a simple example of counting matches from another dataset and that might help you put the two together to get an solution.

If I create two datasets with the following queries, called dsEmp and dsDev respectively

dsEmp

DECLARE @e table (empid int, empname varchar(10))
insert into @e values
    (1, 'Bob'), (2, 'Dave')
SELECT * FROM @e

dsDev

declare @d table(empid int, device varchar(10))
insert into @d VALUES
    (1, 'Phone'),
    (1, 'Laptop'),
    (1, 'Desktop'),
    (2, 'Phone'),
    (3, 'Tablet')
SELECT * FROM @d

Then in my report I add a table bound to dsEmp showing the empID and empName and then in the final column I use the following expression

=LookupSet(
        Fields!empid.Value,
        Fields!empid.Value,
        Fields!empid.Value, "dsDev"
        ).Length
     

I get this final output

As lookupset returns a collection, the collection's length is, in fact, the number of items contained in the collection.