Need to calculate %
-
Hi All
I am a new user of reporting services and i am trying to calculate %.
i am doing this simply with the help of expression with this formula
=Fields!Margin.Value/Fields!Sales.Value
It is working well but sometime my Sale value is zero in that case it gives me "#Error " entry on my report.
How i can get rid of it?
Many Thanks.
-
Hey,
Try this
=IIF(Fields!Sales.Value0,Fields!Margin.Value)/ IIF(Max(Fields!Sales.Value)=0,1,Max(Fields!Sales.Value))," ")
Regards
Raj Deep.A
-
Many Thanks for reply
I tried that and got this error
Argument not specified for falsepart of public funcation IIF(Expression as boolean,True Part As Object,Falsepart as Object) as Object.
Regard's
Vandy
-
Hi,
IIF(Fields!Sales.Value>0,
Fields!Margin.Value)/ IIF(Max(Fields!Sales.Value),
0)
Try with this.
Thanks & Regards,
Kumar KP.
Thanks & Regards,
9989069383
Katakam. -
Hi Kumar KP
Many Thanks for response but having same error msg which i posted in the last post...........
Kind Regard's
-
Try:
=IIF(Fields!Sales.Value<>0, Fields!Margin.Value/Fields!Sales.Value ,"")
-
An iff statement will not work when checking for a zero value before division. .NET evaluates the true/false portion of the statement first and as such, will immediately throw an error. To get around this, use a custom function in your code using a try-catch.
-
Further to David's response:
1. On the Report Menu (in Layout veiw) choose Report Properties
2. Select the Code Tab and create a function, e.g.:
Function DivideMe (ByVal quotient As Double, ByVal divisor As Double) As Double
dim returnValue as Double = 0
If (divisor <> 0) Then
returnValue = quotient / divisor
End If
return returnValue
End Function
3. Back in the report, in the expression editor type
=Code.GetMargin(Fields!Margin.Value, Fields!Sales.Value)
-
Excellent .................
Many Thanks TomT, #Error is removed from my report now it's showing 0% instead of #error tag.
Once again thanks for solving my problem and for giving me new tip.......
Kind Regard's
-
You're welcome
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply