Compartilhar via

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

  • Artigo

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

Comments

  • Anonymous
    August 25, 2010
    Thank you sooooo much! Your article was a great help for me!!! :-)

  • Anonymous
    November 11, 2010
    Thanks alot man, that's great since i have not defined where the error came from.

  • Anonymous
    December 28, 2010
    Finally, someone that can shed some light on this issue...lol...THANKS!

  • Anonymous
    February 09, 2011
    Thanks a lot John !! This error may look simple. But I spent more time. After found the error msg from Output Window. Your blog helped me.

  • Anonymous
    April 05, 2011
    Thank you, Thank you,Thank you!!!!!   I have been searching for a solution for hours.  You explained the solution perfectly!  :)

  • Anonymous
    January 18, 2012
    Wow, saved me lots of time! Thanks!

  • Anonymous
    December 11, 2013
    Thanks a ton buddy. Keep the good work.

  • Anonymous
    March 18, 2015
    Knowledge is power, thanks for sharing.