Sum of a SQL Analysis Services calculated measure within a Reporting Services report gives #Error

Summing a SQL Analysis Services calculated measure result within a Reporting Services report gives #Error but the same calculated measure output can be summed within Analysis Services perfectly fine.

Environment: SQL Server 2008 Analysis Services and Reporting Services + SP1, Report builder 2.0 sp1. This should be applicable to all SQL 2008 and 2005  Reporting Services versions prior as well.

 

If you look at  a report builder report, an expression of " =Sum(Fields!highsales.Value)" on a Analysis Services calculated member gives #Error. The zeros in column "highsales" are numeric values.

The report (see screen shot #1 below) is generated base on the following MDX:

 

with

member measures.[highsales] as

iif ([Measures].[Internet Sales Amount]> 20000000,

measures.[internet sales amount], 0)

member measures.[highsales2] as

iif ([Measures].[Internet Sales Amount]> 20000000,

measures.[internet sales amount], null)

select

{measures.highsales,measures.highsales2} on 0,

{[Product].[Category].[Category], [Product].[Category].[ssas total]} on 1

from [Adventure Works]

 

Screen shot #1

clip_image001

If we calculate the total within Analysis Services using MDX, it gives the correct result (screen shot #2).

Screen shot #2

clip_image002

Why does Reporting Services report return an error?

To find out more about the #Error, we can try the same report in BIDS and take a look at the warnings shown in the "Error List" pane.  It should contain a detailed error message indicating why the #Error occurred.  This information, unfortunately, is not currently surfaced in Report Builder 2.0. 

The warning we can see from BIDS

Warning 1 [rsAggregateOfMixedDataTypes] The Value expression for the textrun 'Textbox8.Paragraphs[0].TextRuns[0]' uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. C:\sumZeroGivesError.rdl               

The error message indicates Reporting Services is receiving different data types within the column "highsales" and Reporting Services cannot sum over incompatible types.

To see what are the actual data types seen by Reporting Services, we can add a new table to the report bound to the data set. In the detail group scope, put the following expression in a TextBox value: =Fields!highsales.Value.GetType().Name

The output (Screen shot #4) shows the data type for the 0 values here are Int16 (or DOUBLE if we switch the 0 to 0.0).

Screen shot #4

clip_image003

Although we tend to think 0 and 28318144.6507 are "sum-able", the sum function in Reporting Services can only sum single type. We have two different data types.  That's why an error is raised

Ref: See the footnote on the Type column of the Aggregate Functions table in the Report Definition Language Specification (https://www.microsoft.com/australia/servers/sql/technologies/reporting/rdlspec.mspx):

For all aggregates other than First, Last and Count, the data type of the aggregated expression is expected to be fixed. If values (other than null) are encountered of multiple data types, it is an error.

Solution Suggestion

In order to fix the problem, we can convert the field to a particular type before passing the value to sum.  For example, if we have all the numbers in integer, we can use "=Sum(CInt(Fields!Foo.Value))".   In the example here, we have the number in decimal, we use "=Sum(CDec(Fields!highsales.Value))" to resolve the issue (Screen shot #5).

Screens shot #5

clip_image004

 

C S John Lam | Premier Field Engineer - SQL Analysis Services