Problem with Avg function in a Grouping field

[Scott Li]

Hi,

I have a text box in a group section and the value is Avg(fields.fieldsName), it works fine if at least one record in detail section in the group has not null value on this column, however, I have a group, none of its records has value on this field, and the textbox in the grouping section through the exception:

#ERROR# Exception has been thrown by the target of an invocation.

is there any alternative I could fix the problem? 

Thanks

[Kathy Colson]

Hello,

This worked for me:   IsNull(Expression, ReplacementValue)

So my value in the textbox is:
=Avg(IsNull(Fields.MyFieldName,0))

[Scott Li]

Thanks for your reply, yes, your solution will not cause exception, however, it will affect the average result, I don't count records having null value into average result. 

[Steve]

Hi Scott Li,

This is expected as when your field contains only NULL or DbNull, there is no rational result we can return. If you break up the Avg function into Sum(fields.fieldsName)/Count(fields.fieldsName) you would see the actual error: "Attempted to divide by zero". We're not aware of your exact goals, but there are a few possible ways to handle this:

• correct your data on the database server
• disallow null for the field

If you have a suggestion on what we should return in such scenario - please let us know.
Best wishes,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[anis]

Hi Scott Li,

May i know if any solution found for this issue?

Coz i'm facing the same problem.

Thx.

[Stef]

Hello anis,

In recent versions the error is suppressed - if all values are null, the AVG=null. If there are non-null values, you will get the AVG calculated based on non-null values' sum and count.

You can also use a custom average based on sum and count, where you can use the ?: operator to prevent a zero division error.

Regards,
Stef
Telerik by Progress

[anis]

Hi Stef,

Thanks for your help.

I manage to get the solution already.

 

 

[anis]

Hi Stef,

I encounter with the same problem when I tried to find the total average for average A, B, and C. I am using ?: operator  to find the average. It seems like it attempt to divide by zero. How can I prevent this? 

[anis]

Hi Stef,

I encounter with the same problem when I tried to find the total average for average A, B, and C. I am using ?: operator  to find the average. It seems like it attempt to divide by zero. How can I prevent this? 

[Stef]

Hello anis,

In general, the ?: operator does not evaluate both possible results and zero-division cases should not appear, unless the division is in the condition part. Check also if you sre using the IIf function, where both possible results are evaluated.

If you need further help, please post the code for generating the report and point the expression that throws the error on processing the report.

Regards,
Stef
Telerik by Progress