How to Avoid Divide by Zero Error in Report Desk

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.

The function inside the red box below is an example of a Telerik function that is used in Group Footers and Report Footers in the 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.



Notice the first portion of the formula 
= Sum(Fields.ExtPrice) = 0 ? 0 : Sum(Fields.ExtPrice - Fields.ExtCost) * 100 / Sum(Fields.ExtPrice)

This acts as an if or a case statement. If Sum(Fields.ExtPrice) = 0 then the function will return zero. Otherwise, the function will perform the calculation.

Using this syntax will avoid a divide by zero error.

CLS




    • Related Articles

    • How to Investigate or Debug Report Desk Problem by Using Registry Keys

      Release Date: 01/25/2025 Version: 8.6 & Up Report Desk is based on the PSQL relational engine. It sends a SQL statement to PSQL engine to retrieve data. Sometimes, you don't get the result back that you expected, so what next? For example, when you ...

    • How to Investigate or Debug Report Desk Problem by Using SQL Statement

      Released Date: 1/25/2025 Version: 8.6 & Higher Report Desk is based on the PSQL relational engine. It sends a SQL statement to the PSQL engine to retrieve data. Sometimes, you don't get the result back as you expected, so what next? For example, when ...

    • Developing a New Elliott V8.6 Report Desk User Defined Report (UDR)

      'Release Date: 6/20/2018 Version: 8.6 & Up This document is written for licensed Elliott Software developers. It is not relevant for regular Elliott users. Overview With proper licensing, Elliott developers can follow these three steps to create a ...

    • Feature: Report Desk AR Statement

      Release date: 5/5/2023 Elliott Version: 8.6 Overview Beginning with Elliott version 8.6, there is a new ability to create more professional looking Accounts Receivable statements. The following standard features are included: Accounts are ...

    • Report Desk: Documentation Roadmap

      Release Date: 4/28/23 Version: 8.6 Setup and Configuration The following articles explain initial setup and ongoing maintenance of Report Desk: Report Desk: Setup and Configuration : Initial installation and ongoing maintenance. Elliott V8.6 ...