CODEWITHSUNDEEP
sqlreporting-servicesnulliif
user1171915
user1171915

Reputation: 89

How to check for NULL values when using the SUM function in SSRS

By default, the SUM function in SSRS excludes NULLs. I want to be able to check for ANY NULL values in the details group and throw an error in the summary group. In the details view I use this to check for NULLS:

=IIF(IsNothing(Fields!EquityPrice.Value)) ,"#Error", Fields!EquityPrice.Value*Fields!EquityShares.Value)

This works as desired.

When I use this in my summary section, it ignores the NULLS and returns the SUM of the non-null values. I want to return "#Error" instead:

=IIF(IsNothing(SUM(Fields!EquityPrice.Value))) ,"#Error", SUM(Fields!EquityPrice.Value*Fields!EquityShares.Value))

I have tried eliminating the SUM in the "IsNothing" expression but to no avail. Any help would be appreciated. Thanks in advance!

Upvotes: 1

Views: 13434

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

So just to confirm, if there is at least one NULL value in a group, #Error should be displayed?

You can use the following for the Summary expression:

=IIf(Sum(IIf(IsNothing(Fields!EquityPrice.Value),1,0)) > 0
  , "#Error"
  , Sum(Fields!EquityPrice.Value * Fields!EquityShares.Value))

This creates a count of NULL values - if that count is greater than zero, return #Error.

I made a simple report to test:

enter image description here

enter image description here

This uses your expression at the detail level and mine at the summary. Errors for the group with one NULL value as required:

enter image description here

Upvotes: 2

Related Questions