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.