Nulls when summing a group in an expression

Hi guys,

I know this has been discussed before, but not in a way that solves this particular problem. I have some data in a table that is grouped by rows, but when i sum the fields in the group i am getting an error because one of the grouped rows contains a null.

Now when i have a one to one relationship between a dataset field and a table cell, checking for a null is easy. But how do you do it when you have several fields that are being summed for that table cell?

In this particular case, i have a work around - there are only two data rows per row group (paid revenue, and unpaid revenue), so i can check them individually for nulls by using First(Fields!Revenue.Value) and Last(Fields!Revenue.Value). This is the expression i used:

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( First(Fields!Revenue.Value)),

First(Fields!Revenue.Value),

0

)

+

IIf(

Not IsNothing( Last(Fields!Revenue.Value)),

Last(Fields!Revenue.Value),

0

),

0,

true,

false,

true

),

"-"

)

The first expression in the outer IIf is worthless, because using Fields!Revenue.Value only checks the first row in the group, and in my case it is always the second row that contains the null.

What is the best way to account for the null values?* It seems i can't access the .Value field like an array (i.e. Fields!Revenue.Value(1)). Should i be passing Fields!Revenue.Value through to a piece of custom code to sum it?

Thanks!

sluggy

*The data i am working with is sourced from a cube with an mdx query. The query uses a COALESCEEMPTY(<measure>, 0) function, but it seems the data provider being used is changing the fields that were coalesced back into nulls. Which is ok - at least i am getting the rows, prior to using the coalesce the rows were being dropped. I'd rather have nulls than have the row missing from the dataset.

Not sure if I understand your question completely, but you can use IIF inside of an aggregate function if you want to skip null values. For example, =Sum(IIF(IsNothing(Fields!Revenue.Value), 0, Fields!Revenue.Value)).

|||

Hi

can you try this out and check whether it is working or not.

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( Cint(First(Fields!Revenue.Value))),

Cint(First(Fields!Revenue.Value)),

0

)

+

IIf(

Not IsNothing( Cint(Last(Fields!Revenue.Value))),

Cint(Last(Fields!Revenue.Value)),

0

),

0,

true,

false,

true

),

"-"

)

Thanks,

Srinivas Reddy.

|||

Srinivas, Fang,

thanks for your answers, but neither work. In this case casting the fields has no effect, as they are already being returned as ints, and you can't typecast a field that is already null.

I have noticed something further, and consequently some of my original information was incorrect. What i have noticed is that nulls are NOT being returned - zeros are. So if i code this expression:

=First(Fields!Revenue.Value) + Last(Fields!Revenue.Value)

it gets evaluated no problem - i get the revenue from the first row plus zero from the second. But if i do any sort of aggregation type function on it, like Sum or Avg etc, then i get an error.Can anyone explain what is happening here, or suggest how i can determine exactly what error is occurring?

Thanks,

sluggy

|||

If you are designing and previewing the report in the report designer, you can check the error message in the task bar.

What's the data type of the Revenue field? One possible reason could be you are summing mixed types. Aggregate functions require all the values passed in are of the same type.

|||

Thanks Fang, i will check into that.

sluggy