July 8, 2020 at 3:27 pm
Good day Gents,
I have an SSRS report that I'm working on, and one of my expression is returning a "NaN" when dividing by zero. Can you guys provide some insight on a workaround to the "NaN"?
Below is my expression:
=(Sum(Fields!Reciept.Value) + Sum(Fields!Balance.Value)) / Count(Fields!VisitID.Value)
July 8, 2020 at 3:38 pm
A quick Google search brought me here, which explains how to work round this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2020 at 4:02 pm
I did try to research workaround but to no avail. Some of the examples offered that I tried returned errors.
Hence why I posted here.
OK, please post an example of an expression which you tried, which includes trapping of the NaN and we can help show you where you went wrong.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2020 at 4:33 pm
I tried both examples in attempt to exclude NaN:
(1)
=Val(replace(Sum(Fields!Reciept.Value) + Sum(Fields!Balance.Value)) / Count(Fields!VisitID.Value) ,"NaN","0"))
(2)
=IIF(Sum(Fields!Reciept.Value)>0,
Sum(Fields!Balance.Value)/Count(Fields!VisitID.Value)+Double.Epsilon),
nothing)
July 8, 2020 at 5:29 pm
Phil got you most of the way there and your attempts were close.
In the first one, your REPLACE call is missing the values that should be replaced. If you count your brackets and ONLY take brackets related to the replace you get:
replace(Sum(Fields!Reciept.Value) + Sum(Fields!Balance.Value))
you want your REPLACE to cover the division and to actually replace a value.
In your second one, you are checking if one value is greater than 0 and then not using that value at all in your math. You might as well have written "IF 1>0 then X/Y". You need to check if your denominator is greater than 0. X/Y where Y = 0 will give you NAN. So make sure that Y is > 0 OR:
=IIF(Count(Fields!VisitID.Value)>0,
Sum(Fields!Balance.Value)/Count(Fields!VisitID.Value),
nothing)
You also had an extra bracket in that second one. What I find helps is to do a new line after each bracket so you can see which things are related. So, taking your number 2 for an example, I would write it originally as:
=IIF(
Sum(
Fields!Reciept.Value
)>0,
Sum(
Fields!Balance.Value
)/Count(
Fields!VisitID.Value
)+Double.Epsilon
),
nothing) <-- ERROR too many )'s
You can see there are 1 too many )'s. So my next step may be to put it back in a single line and figure out where the problem is. I know that IIF takes 3 arguments, so lets remove the last line (the error line) and count the number of commas inside the IF. There is 1, there should be 2. So the error is with a bracket inside either the first argument to IIF or the second. Look at the first one first as it is shorter and we can see it calls SUM which takes 1 argument and has 1 argument and no extra or missing brackets or commas. Onto argument 2 and we have a SUM (1 argument), a COUNT (1 argument) and addition (is this needed?) and an extra ). Remove that and the syntax error is now gone. Still MAY get NAN's due to your COUNT(Fields!VisitID.Value) potentially being 0.
Now, lets use the same logic for option 1. How many arguments should Val have? you have 3 based on your brackets. Is that number correct? Onto the next function - Replace. Based on your brackets, you have 1 argument, how many should it have? Hint - it is more than 1. Next you have SUM with 1 argument (yay!), and SUM with 1 argument (another yay!) and so on. Here is how I would break up number 1:
bracket count
=Val(
replace(
Sum(
Fields!Reciept.Value
) + Sum(
Fields!Balance.Value
)
) / Count(
Fields!VisitID.Value
) ,"NaN","0"
)
) <-- too many brackets
Argument count
=Val(replace(Sum(Fields!Reciept.Value) + Sum(Fields!Balance.Value)) / Count( Fields!VisitID.Value)
,"NaN"
,"0"))
Quick look at that, we can see that we have too many brackets somewhere and we can see that VAL is taking 3 arguments and everything else is taking 1 and we know that REPLACE MUST have more than 1 argument because you need your string, the value to search for and the value to replace.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply