Average Functionality issue

Hi All,

In my Report using SSRS 2005, I am displaying data using table and this table has table footer in which I am calculating "average" using Avg() function.The values in table rows can be either floating numbers (for eg; -1,-99,5.005) or "N/A". I use the following function to calculate Average
=Avg(Iif(Fields!Channel1.Value="N/A",CDbl("0"),CDbl(Fields!Channel1.Value)),"MainDS")

where "Fields!Channel1.Value" are values in table rows.

Everything works fine if all the table rows are floating point numbers and not "N/A", but as soon as table rows values are "N/A" I encounter "#Error" in the table footer.

Can anyone figure out how to get this code working. Thanks in advance.

Regards,
abhi_viking

the best way is add another field in the dataset. Populate this field with the same value after converting to float and replace those "N/A" with 0. Now, you should be able to use the "Avg" function on this new field which is of float data type.

|||Hi,

Thanks for replying. I am currently doing the same thing using "Iif" in Avg()

=Avg(Iif(Fields!Channel1.Value="N/A",CDbl("0"),CDbl(Fields!Channel1.Value)),"MainDS")

If the value is "N/A", then value is 0, else the value is converted to Double ie; CDbl(Fields!Channel1.Value)

Any Idea how to get this code working.

Regards,
abhi_viking
|||

Pls try,

=avg(iif(IsNumeric(Fields!Channel1.Value),cdbl(Fields!Channel.Value),cdbl(0)))

Priyank

|||I tried using IsNumeric(), but didnt work for me, I got the same error that I used to get
Is there any other way to get this code working ?

Regards,
abhi_viking

|||

First create an new expression to just convert the "N/A" to 0. Then create another epression and do the average of the first expression. I think this should work, if not could you give te details of the error?

|||

The problem is that the IIF statement evaluates both the true and false results (http://msdn2.microsoft.com/en-us/library/27ydhh0d(VS.80).aspx) and DBbl("N/A") fails to evaluate. Perhaps you could try:

Code Snippet

=Avg(

CDbl(

IIF(

Fields!=Channel1.Value="N/A",

0,

Fields!=Channel1.Value

)

)

)

If you still have problems, create a small custom function and pass the value into it and let it return the double. Within the code fragment, you can use a real IF and avoid the problems imposed by IIF.

Larry

|||

Thank you guys for replying back, I will try your suggestions soon and will keep you updated on this topic.

Regards,

abhi_viking

|||Thanks Larry, your code snippet works for me