Edition: Business, Enterprise
User-level: All
Previous steps: Creating a Custom Report
The Problem
A common error that is seen when using expressions is the 'divide by zero' error. This error is caused when there is a division function where the denominator is zero.
Because an expression resolves all rows in the report before the report is displayed, if any row in your expression denominator has a zero, then none of the rows will resolve.
For example, a profit percentage expression on a Transaction report:
([Transactions|RevenueTotalExTax] - [Transactions|ActualCostTotal]) / [Transactions|RevenueTotalExTax] * 100
This expression takes the difference between your Revenue Total and your Actual cost and divides it by the total revenue, giving you the difference as a percentage of the total revenue.
However, if any of the transactions in your report have not been invoiced yet, they will not have a revenue value, and so their revenue will be 0. Try to divide by zero, and you'll get the error for the whole column:
There are two possible ways of getting around this; using filters, or using IIF functions
Solution 1 - Filters
Using a filter is the simplest way. If you filter the report by the Transaction Revenue Total and set it to show only transactions with a revenue greater than 0, then none of the zero rows will be in your report, and your expression will work successfully.
The issue with this is that those rows with 0 revenue total are removed completely from the report, not just the expression column. So if there are other columns that you need those rows to contribute to, then this is not the solution for you.
Solution 2 - IIF functions
An IIF function allows you to set conditions for your expression. If a condition is met, it does one thing. If it's not met, then it does another. This is commonly expressed as "IF THIS THEN THAT". The syntax used for an IIF function is IIF ( expr , truepart , falsepart ). Using the above example, these parts are:
expris what we're checking. So we would say [Transactions|RevenueTotalExTax] = 0truepartis the thing you want to happen if theexpris true. So if the revenue is 0, we want it to just show 0.falsepartis the thing you want to happen ifexpris false. In this example, that's where we'd put our profit calculation.
To put that all together, our example would look like this:
IIF ([Transactions|RevenueTotalExTax] = 0, 0, (([Transactions|RevenueTotalExTax] - [Transactions|ActualCostTotal]) / [Transactions|RevenueTotalExTax] * 100))
Now when the report is run, any rows that have a Transaction revenue of 0, the row simply displays 0. If the row does not have a Transaction revenue of 0, then it does the profit calculation.
Tips!
For more details on the IIF function syntax, see this Microsoft document here: https://learn.microsoft.com/en-us/dotnet/fundamentals/runtime-libraries/system-data-datacolumn-expression#iif
Where to next?
What other features does this relate to?