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

    • 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 ...

    • Report Desk: Column Formatting

      Release Date: 4/28/23 Elliott Version 8.6 Changing Column Format When you are in the Report Desk designer and you drag a column one of the Line grids, a default column format will appear. You may change the format of the column to another type by ...

    • Report Desk: Running & Designing Reports Basics

      Release Date: 03/24/2021 Revised: 08/30/2021 Version: 8.6 & Up Report Desk is a new report-developing platform for Elliott Business Software. It is based on the PSQL relational engine accessing the Elliott database, which has the potential to make ...

    • Report Desk: Telerik Functions

      Release Date: 4/28/23 Elliott V8.6 Telerik Functions in Report Desk In addition to the Aggregate functions that are automatically mapped to Telerik functions in Group Headers and Footers and Report Footers, you may also use built-in Telerik functions ...

    • Report Desk: Subreports

      Release date: 4/28/23 Version 8.6 When to Use Subreports Sometimes there is information related to a detail line in a report that can be obtained from a previously-defined separate report -- a subreport that is intended to be embedded in other ...