CODEWITHSUNDEEP
sqlsql-serverreporting-services
Fozzy767
Fozzy767

Reputation: 83

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

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?

Upvotes: 7

Views: 16862

Answers (1)

Ian Preston
Ian Preston

Reputation: 39596

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.

Upvotes: 16

Related Questions