Sql Server 2008 Reporting Services: Using two dataset error

Question

Im building a report that's using two datasets. when I preview I find these types of errors...

Error 19
[rsFieldReferenceAmbiguous]
The Value expression for the text box ‘Textbox3’ refers directly to the field ‘PerZipCode’ 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.

What aggregate function is needed and where is there an option to set this?

score 8 · Answer 1 · edited Nov 21 '12 at 18:02

8

If you are adding multiple datasets to a report, the above may not fix your problem. You may just get the following error when you aggregate it:

[rsMissingAggregateScope] The Value expression for the text box ‘textbox6’ 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.

What you may need is something like :

First(Fields!MyField.Value, "DATASETNAME")

Which you can get by using the Expression Builder, rather than the drag and drop of fields from the dataset.

edited Nov 21 '12 at 18:02

bfavaretto

71,580
16
111
150

answered Nov 21 '12 at 17:39

xeis

81
1
1

This is the solution also for `The Value expression for the text box uses an aggregate expression without a scope.` error. – Jun 02 '16 at 13:07

gbn · Accepted Answer · 2011-06-15T17:42:50.117

7

Min or Max or Avg etc: most of these

The aggregate is needed to reduce the other DataSet to one value (max of values etc) because you are using something not in the local scope (eg the DataSet bound to the Data Region). There is no way to match rows in the other DataSet with the local scope DataSet.

If your text box is standalone (not in a Data region), the same applies: the aggregate is needed to tell SSRS which row to take (Max etc) or what calculation to do on the dataset (Avg etc)

edited Jun 15 '11 at 17:42

answered Jun 15 '11 at 17:37

gbn

422,506
82
585
676

As always gbn you answer my question and explain the "why". Thanks again! – 2boolORNOT2bool Jun 15 '11 at 17:43
One last question, I have another report I have already developed that uses three datasets. It worked fine with no errors. The only real deferrence between the two is I used expressions instead of pulling the field from the UI. Is this because im explicitly referring to the dataset as in =First(Fields!RegistrationTime.Value, "DataSet1") – 2boolORNOT2bool Jun 15 '11 at 19:52
1

First is an aggregate and DataSet 1 is your scope. So it as MSDN says you should be – gbn Jun 15 '11 at 19:57
Ok, I'm starting to understand a bit better. I didn't know "First" was an aggregate but now that I think about it, this makes perfect sense. Thanks again for your patience! – 2boolORNOT2bool Jun 15 '11 at 20:00
For people who stumble upon this question: I've also found options in the table to set all values used in said table to a certain dataset. This way you don't have to worry about those pesky aggregate functions. At least not for that particular table. Also be careful with "First", especially when you want a set of records because naturally it only gives you the "First" data row. – 2boolORNOT2bool Aug 01 '11 at 17:52
@2boolORNOT2bool where is this setting? – Herrozerro Jul 18 '13 at 21:13

score 0 · Answer 3 · answered Feb 08 '14 at 10:44

To use multiple dataset value on SSRS report we need to use below code.

First(Fields!MyField.Value, "Datasetname").

If by typing this you are still getting the same problem then right click on textbox & select expressions then in expressions click on Dataset. Select you dataset & then double click on required column. After this click on ok.

If you have multiple controls then follow the same for all of them & verify the same by executing the report.

score 0 · Answer 4 · answered Apr 15 '16 at 17:32

If you already have an aggregate and are having this error, it is probably because the fields of the report aren't up to date with the dataset(s). You can fix the issue by refreshing the fields of the report.

To populate the field collection, use the Refresh Fields button on the Dataset Properties dialog box. The field collection does not appear in the Report Data pane until the Dataset Properties dialog box closes.

To refresh the fields for a specific dataset In the Report Data pane, right-click the dataset, and then click Dataset Properties.

Note : If the Report Data pane is not visible, on the View menu, click Report Data. If the pane opens as a floating window, you can dock it. For more information, see How to: Dock the Report Data Pane.

In the Query pane, type the query. Alternatively, you can use the Import button to import your query from another .rdl file. Click Refresh Fields. Click OK. In the Report Data pane, expand the dataset node to view the currently defined field collection.

Sql Server 2008 Reporting Services: Using two dataset error

4 Answers4