6

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?

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862

3 Answers3

21

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

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
2

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.

Clark Vera
  • 183
  • 1
  • 7
1

A text box can get knocked out of its context and still appear to be in it. I came by this post looking for the same error message and it was one textbox in a series of several and it had worked just moments before I had readjusted formatting. After getting the info in the first answer, I went back and poked around and found the textbox throwing the error was actually be the list box but appeared to be on the same levels as the others unless I was trying to move or resize the list box. Moving the textbox out and dropping it back in fixed things. As someone coming upto speed on SSRS from a programmer prospective, the insight in the first answer solved a frustration that has come up repeatedly!

Jimbus
  • 47
  • 8