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.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

One Response to Conditionally summing up values in a dataset in SSRS

  1. Davos says:

    SSRS doesn’t have a BIGINT datatype so it’s not so much that you were lazy to find the equivalent data type, there just isn’t one. Other sites I have seen suggest to use the Text datatype but that’s obviously not going to work if you are summing up the values. I think rather than using float type that the Decimal type is a much better choice. It would be worth trying CDec(0) instead of NOTHING in your example to see if it works.

    Anyway, this is a pretty good trick, I suppose like NULL, the NOTHING is easily converted to any data type. SSRS apparently has a Variant data type that it will use for CLR functions by default, but there is no CVar() and such a function would not make sense anyway.

    Useful post Suresh, thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s