# How to Avoid Divide by Zero Error in Report Desk

When designing a custom report, you can also define a formula like "MarginPct." It has a value taken from a complex SQL column definition using numeric fields to compute the margin percent.

Normally, you will test the formula in the PSQL Control Center to make sure it works. For example:

SELECT CPPROMIT_ITEM_NO_ALT, ITEM_DESC1, CPPROMIT_BATCH_ALT, CPPROMBA_DESC, CPPROMBA_PRICE_APPLY, CPPROMBA_DISC_APPLY, CPPROMIT_START_DATE, CPPROMIT_END_DATE, CPPROMIT_PROJ_QTY, CPPROMIT_TTL_QTY, CPPROMIT_TTL_AMT, **Case When cppromit_price = 0 Then 0.00 when cppromit_ttl_qty = 0 Then 0.00 Else (((cppromit_price - (cppromit_ttl_cost / cppromit_ttl_qty)) / cppromit_price)) * 100 END as MarginPct****,** CPPROMIT_PRICE FROM CPPROMIT left join cppromba on cppromba.cppromba_batch = cppromit_batch_alt left join imitmfil on imitmfil.item_no = cppromit_item_no_alt ORDER BY CPPROMIT.CPPROMIT_ITEM_NO_ALT ASC , CPPROMIT.CPPROMIT_BATCH_ALT ASC

The highlighted area is the formula being tested.

Defining the formula simply as

**(((cppromit_price - (cppromit_ttl_cost / cppromit_ttl_qty)) / cppromit_price)) * 100**

will return a divide by zero error if the value of cppromit-price or cppromit_ttl_qty is zero. Using a case statement will resolve this error.

**Case **

**When cppromit_price = 0 Then 0.00 **

**When cppromit_ttl_qty = 0 Then 0.00 **

**Else (((cppromit_price - (cppromit_ttl_cost / cppromit_ttl_qty)) / cppromit_price)) * 100 **

**END **

Complex calculations can also be made using Telerik functions in the SQL Designer Template. In the example below, a simple aggregate function could not provide the value desired -- namely computing a value based on other aggregate values-- so a Telerik function was used.

*UDR Promotion History Report*. Note that it begins with the "=" character. That tells Report Desk that this function is a Telerik function, not a SQL function. This Gross Profit Percent function,

*GPPct*in the

*Name*column below, does a Sum() on the Extended Price minus the Extended Cost, then divides that amount by the sum of the Extended Cost, and multiplies it by 100 to display it as a percentage.