How to Summarize a Detailed Debtors Aging Report

Dear All,

I have written a Query for a Detailed Debtors Aging Report - i.e a report which lists ALL unpaid invoices. It works smoothly.

I use SAP B1 8.8.

Now, I want to create a Summarized Debtors Aging Report which groups all the unpaid invoices for a particular debtor and displays only 1 row for each debtor.

I use the following commands: SUM(), GROUP BY to modify the original query.

A very simplified version of my Detailed report is in Screen 1.

The modifications I made to it to convert it to a Summarized report are in Screen 2. It does not work!

I have traced the error to the following:

SUM ((SELECT T0.BalDueDeb - T0.BalDueCred WHERE DateDiff(mm, T0.TaxDate,@taxdt) = 1))
AS '1 Mth Ago'

This is the code I use to put the amount due in the appropriate Age Bracket.

The SQL Error Message is:

Cannot perform an aggregate function on an expression containing an aggregate or a sub query

Could you please help me rewrite this so that it works?

Thanks

Leon Lai

-

---

-

---

Screen 1 : The Original DETAILED report

declare @taxdt datetime
set @taxdt
/*select 1 from jdt1 t0 where t0.TaxDate*/ = [%1]

SELECT
CASE

             WHEN T0.Account = 1220101 THEN 'Prim Cust'
             WHEN T0.Account = 1220102 THEN 'Fgn Cust'
             WHEN T0.Account = 1220103 THEN 'Local Cust'
             WHEN T0.Account = 1220104 THEN 'Staff Loan'  
             WHEN T0.Account = 1220105 THEN 'Dep with TP'
             WHEN T0.Account = 1220106 THEN 'Adv to Cust'
             WHEN T0.Account = 1220108 THEN 'Sund Drs'
             ELSE 'Error ! ! !'
END AS 'Control A/c',
T1.CardCode AS 'BP Code',
T2.Notes2 AS 'BP Name',
(T0.Debit - T0.Credit) AS 'Orig. Rs',
(T0.BalDueDeb - T0.BalDueCred) AS 'Bal. Rs',
---------------------------------------------------------------------------------
(SELECT T0.BalDueDeb - T0.BalDueCred WHERE DateDiff(mm, T0.TaxDate, @taxdt) = 1)
AS '1 Mth Ago'
--------------------------------------------------------------------------------
FROM JDT1 T0 
INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
LEFT OUTER JOIN OCPR T2 ON T1.CardCode = T2.Cardcode
LEFT OUTER JOIN OJDT T3 ON T0.TransID = T3.TransID
LEFT OUTER JOIN OINV  T4 ON T3.TransID = T4.TransID
LEFT OUTER JOIN ORIN  T5 ON T3.TransID = T5.TransID
WHERE
T1.CardType = 'C'
and (Balance) != 0
and (T0.BalDueDeb - T0.BalDueCred) != 0

-

---

Screen 2 : The Modified SUMMARY report

declare @taxdt datetime
set @taxdt
/*select 1 from jdt1 t0 where t0.TaxDate*/ = [%1]

SELECT
CASE
             WHEN T0.Account = 1220101 THEN 'Prim Cust'
             WHEN T0.Account = 1220102 THEN 'Fgn Cust'
             WHEN T0.Account = 1220103 THEN 'Local Cust'
             WHEN T0.Account = 1220104 THEN 'Staff Loan'  
             WHEN T0.Account = 1220105 THEN 'Dep with TP'
             WHEN T0.Account = 1220106 THEN 'Adv to Cust'
             WHEN T0.Account = 1220108 THEN 'Sund Drs'
             ELSE 'Error ! ! !'
END AS 'Control A/c',
T1.CardCode AS 'BP Code',
T2.Notes2 AS 'BP Name',
SUM ((T0.Debit - T0.Credit)) AS 'Orig. Rs',                 /* Added SUM()*/
SUM ((T0.BalDueDeb - T0.BalDueCred)) AS 'Bal. Rs',         /*Added SUM()*/
-------------------------------------------------------------------------
SUM ((SELECT T0.BalDueDeb - T0.BalDueCred 
    WHERE DateDiff(mm, T0.TaxDate, @taxdt) = 1))     
    AS '1 Mth Ago'               /*Added SUM() PROBLEM IS HERE! */
---------------------------------------------------------------------------
FROM JDT1 T0
INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
LEFT OUTER JOIN OCPR T2 ON T1.CardCode = T2.Cardcode
LEFT OUTER JOIN OJDT T3 ON T0.TransID = T3.TransID
LEFT OUTER JOIN OINV  T4 ON T3.TransID = T4.TransID
LEFT OUTER JOIN ORIN  T5 ON T3.TransID = T5.TransID
WHERE
T1.CardType = 'C'
and (Balance) != 0
and (T0.BalDueDeb - T0.BalDueCred) != 0

GROUP BY T0.Account, T1.CardCode, T2.Notes2                   /*Added GROUP BY*/