I found myself recently trying to remember how a formula I wrote a few months ago in Report Builder. Doing the same thing twice, made me think I should put it on the blog, so that next time I won’t need to remember it…
A user complained a report in report builder didn’t run for her, as she divided two measures and the report would error when the denominator would equal zero.
The error message she got was:
Semantic query execution failed. Divide by zero error encountered.
Cannot read the next data row for the data set dataSet.
An error has occurred during report processing.
And a picture of that error would be:
So let’s assume you have two measures: A and B and you need a new measure of A/B. Only thing is, B can sometimes be equal to zero.
I don’t write
if(B=0, 0, A/B)
Because Report Builder evaluates A/B before checking if B=0, and so returns an error.
This is therefore the suggested formula:
if(B=0, 0, A / if(B=0, 1, B))
The beginning is pretty straight forward.
In the last part of the If function I check again if B=0. If it is, then I would hypothetically return A/1. We never actually return A/1 as if B=0, then the second case of zero is returned. The 1 in the second if function is just so we don’t get an error in the evaluation of the formula if B=0 (as is the case in the wrong way of writing the formula). If B is different from zero, then we get A/B.
And that the way I could promise my user that she wouldn’t be dividing by zero when she’d create a fraction as a calculated field.