Skip to Content
avatar image
Former Member

Need Query for Trial Balance Grouping Level 4 Accounts with drill down option for detail

I have following detail

need drill down to detail of each...

following query i am using...

SELECT SUM(T1.Debit) 'Debit', SUM(T1.Credit) 'Credit', T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit) AS 'Balance'

FROM OJDT T0

INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]

INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]

INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]

WHERE T0.TaxDate BETWEEN '2016-07-01' AND '2017-06-30'

GROUP BY T3.AcctCode, T3.AcctName

tn6i1.png (34.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 25, 2016 at 05:41 PM

    The report is already showing everything from the Query.

    I guess you need to modify the query like:

    SELECT T1.Debit , T1.Credit, T3.AcctCode, T3.AcctName

    and then Summarize everything within the report. In this case you should be able to drill-down summaries

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      yes actually we are working with 5 projects at a time...that need trial balance for each project separately.....therefore, need two parameters

      1. project code/name

      2. date from to

      and then of course drill down detail of each l5

  • Nov 29, 2016 at 04:50 PM

    perhaps if you select tables JDT1, OJDT, OACT

    and then make grouping in this way

    The result is like this...

    in every line a double click

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 01, 2016 at 11:32 AM

    thanks for replying....

    Add comment
    10|10000 characters needed characters exceeded