I understood from a friend that last month people were told in the BI User Group that it would be mostly Q&A for our guests today. I would like to correct that impression as an email from the BIUG mailing list states that the meeting today will be:
17:00 -< 17:30 Networking and welcome
17:30 -< 18:45 Eran Megiddo - MS- developing BI @ MS across Office and SQL.
Future of BI (Non NDA Issues).
18:45->19:00 break
19:00->20:30 Amir Netz & Ariel Netz -MS - The Future of SSAS
I think it's going be a great session so I hope to see you there.
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.