How to create report textbox aggregate expression in SQL server reporting with multiple datasets

Question

If have a SQL Server Reporting Services report that has several textboxes within a Report with variations of the following expression

=Sum(IiF(Fields!RegisterID.Value = 6000, 1, 0) 

and

Iif(Fields!PointID.Value = 500, 1, 0)) / Sum(Iif(Fields!PointID.Value = 500, 1, 0)) 

I see the following error when I try to preview the report:

The Value expression for the text box ‘Textbox2’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one dataset.

How do I fix This?

Answer 1

As per the error, when you are referencing a Dataset field in a textbox that isn't part of a table, you need to specify a Scope for expression, e.g. you want a field, but what Dataset should be used?

So something like:

=Sum(IiF(Fields!RegisterID.Value = 6000, 1, 0), "MyDataset")

Where MyDataset is the name of a Dataset in your report.

If you are doing this in an independent textbox, you must also use an aggregate function like First or Sum, as only one value can be displayed in the textbox and the Dataset might have multiple rows; using an aggregate makes sure only one values is returned, as required.