How can I “specify a dataset aggregate” in this SSRS Expression?

6
votes

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?

2
reporting-servicesvbscriptbidsrdlaggregates

2 Answers

16
votes

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

1
votes

This is an interesting problem, because if you have ONLY 1 data set in your report, you don't need a table, you can directly reference a field that exists in that table.

However, if you have more than 1 data set then you must create a table, which may give you multiple rows. Then you might have to group by this one field, and hide the extra columns and rows. However, you CANNOT display this data in a TEXTBOX, you can however create a rectangle which can hold the table and another textbox. Or display the table directly.