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
