Reporting Services Sql Server 2008: Using Two Dataset Errors

Question

Create a report using two data sets. when I browse, I find these types of errors ...

Error 19
[rsFieldReferenceAmbiguous]
The Value expression for the Textbox3 text field refers directly to the PerZipCode field without specifying the dataset aggregate. When a report contains multiple data sets, links to fields outside the data area must be contained in the aggregate functions that define the data set area.

What aggregate function is needed and where is it possible to install it?

Answer 1

Min or Max or Avg, etc .: most of these

An aggregate is needed to reduce another DataSet to one value (max values, etc.), because you are using something not in the local area (for example, a DataSet is bound to a data area). Cannot map rows in another DataSet to a local DataSet scope.

If your text box is autonomous (not in the data area), the same thing: the aggregate is needed to tell SSRS which line to take (Max, etc.), or what calculations to do in the data set (Avg, etc. )

Answer 2

If you add multiple datasets to the report, the above may not solve your problem. You can simply get the following error when combining it:

[rsMissingAggregateScope] Value expression for textbox textbox6 uses an aggregated expression without scope. An area is required for all aggregates used outside the data area, unless the report contains exactly one data set.

You might need something like:

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

What you can get using Builder Expression rather than dragging fields from a dataset.

Answer 3

To use multiple dataset values ​​in an SSRS report, we must use the code below.

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

If, having typed this, you still get the same problem, then right-click on the text field and select the expressions, and then click "Dataset" in the expressions. Select a dataset, and then double-click the desired column. After that, click OK.

If you have several controls, then everything is the same for them and check them by running a report.

Answer 4

If you already have an aggregate and have this error, this is probably due to the fact that the report fields are not updated using the dataset. You can fix the problem by updating the report fields .

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

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

Note: If the Report Data panel is not displayed, select Report Data from the View menu. If the panel opens as a floating window, you can dock it. For more information, see How to. Connecting the report data panel.

In the Request area, enter a request. Alternatively, you can use the Import button to import the request from another .rdl file. Click Refresh Fields. Click OK. In the Report Data pane, expand the node dataset to view the current set of fields.