Sql Server 2008 Reporting Services: Using two dataset error

aggregate-functionssql-server-2008ssrs-2008

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?

Best Answer

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.

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation

From Save (Not Permitted) Dialog Box on MSDN :

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

Adding a new column to the middle of the table

Dropping a column

Changing column nullability

Changing the order of the columns

Changing the data type of a column <<<<

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

See Also Colt Kwong Blog Entry:
Saving changes is not permitted in SQL 2008 Management Studio

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation

Related Topic