Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql Server 2008 Reporting Services: Using two dataset error

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?

like image 218
2boolORNOT2bool Avatar asked Jun 15 '11 17:06

2boolORNOT2bool


People also ask

Can Ssrs handle multiple result sets?

You can't use a single SSRS result set to call a proc that returns multiple result sets and access it as a collection of result sets the way you can in . net. Once you have your multiple result sets created, you can create multiple tablix on your report, and designate in each one which result set it uses for its data.

Can we update shared dataset in SSRS?

In the Properties tab, we can edit, delete or change the folder of shared data source. The Data Preview tab helps us to see a little part of data when we click Load Data. The Data sources tab allow us to change dataset of shared SSRS data source.

How do I add a dataset to RDL?

In the Report Data pane, right-click the name of the data source, and then click Add Dataset. The Query page of the Dataset Properties dialog box opens. In Name, type a name for the dataset or accept the default name.


2 Answers

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.

like image 174
xeis Avatar answered Nov 11 '22 17:11

xeis


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)

like image 45
gbn Avatar answered Nov 11 '22 17:11

gbn