How to create report textbox aggregate expression in SQL server reporting with multiple datasets
reporting-services, sql, sql-server
Solution
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.
Problem
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?