Skip to Content
0

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

Nov 24, 2016 at 07:47 AM

74

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Vitaly Izmaylov
Nov 25, 2016 at 05:41 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Gonzalo Gomez Nov 29, 2016 at 04:50 PM
0

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


captura.jpg (97.5 kB)
captura.jpg (74.6 kB)
captura.jpg (59.1 kB)
Share
10 |10000 characters needed characters left characters exceeded
Malik Tauqeer Gul Dec 01, 2016 at 11:32 AM
0

thanks for replying....

Share
10 |10000 characters needed characters left characters exceeded