How to “specify a dataset aggregate” in this SSRS Expression

aggregatesbidsrdlreporting-servicesvbscript

I need a row value in my SSRS report that is a calculated one based on a couple of fields that are already being used in the report. I want it to display in the textbox named 'textboxPercentageValue'. In semi-plain English, the expression/formula is:

If the value of the "Week" field is "WK1", display the value of the Variance field divided by the value of the Price field; otherwise, just display the value from the Variance field.

In VB script gobbledygook, the expression/formula I've add to textboxPercentageValue's Value propert is:

=IIF((Fields!Week.Value="WK1"), Fields!Variance.Value / Fields!Price.Value, Fields!Variance.Value)

Yet, when I try to upload the .rdl file to SQL Server Reporting Services, I get:

"The Value expression for the text box ‘textboxPercentageValue’ refers directly to the field ‘Week’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope. (rsFieldReferenceAmbiguous) Get Online HelpThe Value expression for the text box ‘textboxPercentageValue’ refers directly to the field ‘Variance’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope. (rsFieldReferenceAmbiguous) Get Online Help
The Value expression for the text box ‘textboxPercentageValue’ refers directly to the field ‘Price’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope. (rsFieldReferenceAmbiguous) Get Online Help
The Value expression for the text box ‘textboxPercentageValue’ refers directly to the field ‘Variance’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope. (rsFieldReferenceAmbiguous)"

So what do I need to do to make this expression/formula unambiguous to SQL Server Reporting Services or the VBScript parser or "whoever" is complaining about it?

Best Answer

It sounds like your Textbox has been added to an area of the report that isn't a "data region" (e.g. a table or list). A data region will have a reference to a particular report dataset as one of it's properties, so Reporting Services knows all field references inside that data region refer to that dataset. What this error is trying to say (in a rather verbose way) is:

  • if you reference a field outside of a data region, that reference needs to be inside an aggregate expression such as Sum() - this is because the dataset may contain multiple rows for the field but outside of a data region a textbox can only display a single value.
  • this aggregate expression must also include a reference to the report dataset that the field is coming from

So if field "WK1" was from dataset "MyDataset1", the expression to reference that field would look like:

=Sum(Fields!WK1, "MyDataset1")

See also: Using Dataset Fields Collection References in Expressions

Related Topic