SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Conditionally summing up values in a dataset in SSRS

Recently I had situation wherein I had to conditionally sum up a data set and display the value in a text box.

The value in the dataset looked like

In this case, for example, I had to sum up ”VwCount” for FYMonth  = 6. So I came up with an expression like

=Sum(IIF(Fields!FYMonth.Value = 6, Fields!VwCount.Value,0), “dataset1″)

When I tried to preview the report in BIDS, got an error like

[rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox10.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.

The error message surprised me but it took me sometime to recognize that the zero in the else part is not of the same data type as the field “vwCount”. Instead of converting the zero to bigint (I was bit lazy to find out the matching data type in VB.NET for SQL BIGINT), I came up with another expression like

=Sum(IIF(Fields!FYMonth.Value = 6, Fields!VwCount.Value,Fields!VwCount.Value-Fields!VwCount.Value), “dataset1″)

 This worked except when the “VwCount” was NULL. I had to modify the expression again to

=Sum(IIF(Fields!FYMonth.Value = 6, Fields!VwCount.Value,NOTHING), “dataset1″)

and it worked fine.


Posted by JohnCurrie on 27 December 2013

Great - Thanks very much!

Posted by Fahim Kanji on 2 April 2014

OMG - so obvious - yet so elusive!

Posted by hello.priyanka on 18 August 2014

Thank you very much.. it solved my problem..

Leave a Comment

Please register or log in to leave a comment.