cancel
Showing results for 
Search instead for 
Did you mean: 

How to Summarize a Detailed Debtors Aging Report

leon_laikan
Participant
0 Kudos

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*/

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Try:

declare @taxdt datetime
set @taxdt
/*select 1 from jdt1 t0 where t0.TaxDate*/ = [%1]
 
SELECT
CASE	T0.Account
   	WHEN 1220101 THEN 'Prim Cust'
        WHEN 1220102 THEN 'Fgn Cust'
        WHEN 1220103 THEN 'Local Cust'
        WHEN 1220104 THEN 'Staff Loan'  
        WHEN 1220105 THEN 'Dep with TP'
        WHEN 1220106 THEN 'Adv to Cust'
        WHEN 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()*/
-------------------------------------------------------------------------
(SELECT SUM (T6.BalDueDeb - T6.BalDueCred) FROM JDT1 T6
    WHERE DateDiff(mm, T6.TaxDate, @taxdt) = 1 AND T6.TransID=T1.TransID)    
    AS '1 Mth Ago'               /*Added SUM() PROBLEM IS HERE! */
---------------------------------------------------------------------------
FROM JDT1 T0
INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
LEFT JOIN OCPR T2 ON T1.CardCode = T2.Cardcode
LEFT JOIN OJDT T3 ON T0.TransID = T3.TransID
LEFT JOIN OINV  T4 ON T3.TransID = T4.TransID
LEFT 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*/

Thanks,

Gordon

leon_laikan
Participant
0 Kudos

Dear Gordon,

Thanks for your answer.

On my way home, it suddenly dawned on me that my question cannot be answered!

How can we generate an Ageing when the data is summarized?

Detailed Aging OK!

But Summary Aging? Impossible! How can we say that the total amount due is 3 months old,

when it's made up of several invoices? I would be surprised if your suggestion works.

SQL was being more clever than me!

I hastened home to close my silly question before anybody replies.

Sincere apologies, and thank you for your generous support, without which I would struggling in vain.

Leon Lai

Former Member
0 Kudos

I also have doubt what you are upto. The answer is purely for showing it is doable by SQL. Somehow, it may be useful for management to know the total debt for each customer.

leon_laikan
Participant
0 Kudos

Dear Gordon,

On second thoughts, I think I had jumped too fast to conclusions. Confusion and tiredness...

You are right: It is perfectly possible to generate a Summary Aged Analysis

We simply have to analyse EACH invoice to an appropriate AGE BUCKET and them SUM each of these Age Bucket columns.

What I erroneously thought was:

We must first sum all invoices for a particular BP. Then analyse this total to a particular Age Bucket.

This of course is meaningless and impossible.

I shall try your suggestion when I return to office, and will let you know.

If it does not work, I'll post a repeat thread as this one is closed.

Apologies again, and thanks

Leon Lai