EZ Writer Data Source

Answered

Pieter Du Plooy

April 02, 2026 06:49

Hi

I'm not a SQL expert, but this works in SQL for me. Any idea on how to clean this up to use in EZ Writer?

- 1. Declare the variables
DECLARE @ActivePeriod VARCHAR(10);
DECLARE @ORGANIZATION VARCHAR(10) = '10'; -- Set your default or input value here

-- 2. Fetch the active period using your base query
SELECT @ActivePeriod = MM012_FYP
FROM MM012_FISCAL_PERIODS
WHERE MM012_MODULE = 90 
  AND MM012_PERIOD_STS = 'A';

-- 3. Run the calculation using the variables
WITH FinancialSummary AS (
    SELECT 
        @ORGANIZATION AS GL001_ORG_CODE,
        
        -- Debtors Subquery
        (SELECT SUM(GL010_YTD) 
         FROM GL010_BALANCES 
         WHERE GL010_ACCOUNT = '99998552' 
           AND GL010_FYP = @ActivePeriod 
           AND GL010_ORG_CODE = @ORGANIZATION) AS Debtors,
           
        -- Revenue Subquery (Multiplied by -1 to reverse sign)
        (SELECT SUM(bal.GL010_YTD) * -1 
         FROM GL010_BALANCES bal
         JOIN GL001_ACCOUNT_MASTER mast 
           ON mast.GL001_ACCOUNT = bal.GL010_ACCOUNT
         WHERE mast.GL001_ACCT_TYPE = '50' 
           AND bal.GL010_FYP = @ActivePeriod
           AND bal.GL010_ORG_CODE = @ORGANIZATION) AS Rev
)
SELECT 
    GL001_ORG_CODE,
    Debtors,
    Rev,
    -- Safely handle division by zero and force float math
    (Debtors / NULLIF(Rev * 1.0, 0)) * 365 AS DebtorDays
FROM FinancialSummary;

• 

  Seth Halvaksz

  April 06, 2026 13:09

  Pieter Du Plooy can you clarify what the customer is trying to accomplish by putting this in EZ Writer, and how they will take action based on this information?
  I'm also told that EZ Writer is typically handled by tech services.  Has your team been trained on EZ Writer? If not can you reach out to tech services.
• 

  Pieter Du Plooy

  April 06, 2026 22:26

  Hi, They need to calculate debtor days. It seems using EZ Writer was the only way to get this in a dashboard without the need of another Crystal report. The customer did not write this, I did.

  In future, will we be able to do this in Analytics? I can do this in Qlik, but not familiar with Sigma. How open will Sigma be and how much can clients configure/build custom views and dashboards?
• 

  Seth Halvaksz

  April 07, 2026 00:57

  Yes anything you could do in Qlik, you can do in Sigma. It sounds like Analytics may be a better solution here.

Please sign in to leave a comment.