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.

How this relates to Powerbi? I do not understand the tools you are using here.. — Aldert
You'll probably need to use LOOKUPSET. Take a look here docs.microsoft.com/en-us/sql/reporting-services/report-design/… — Alan Schofield
@Aldert I'm using Power BI Report Builder. Our datasets are housed in the PBI Service. — Milo Ulver
@AlanSchofield I'm not sure how this will work - I'm actually trying to count the rows that match the conditions from dsDefects not pull up a value? — Milo Ulver
I'll post a sample that should give you an idea of hoe to use it. I can't give an exact answer but I'll try — Alan Schofield

Alan Schofield Alan Schofield · Accepted Answer · 2020-10-19T14:36:47

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.

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

1 Answers