cancel
Showing results for 
Search instead for 
Did you mean: 

Individual Ledger Reports in sql query

former_member229757
Participant
0 Kudos

Hi Experts.

               I need a query to check individual ledger reports please help me to write a query.

Regards

Vinoth

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

What you mean by individual ledger?

Thanks & Regards,

Nagarajan

former_member229757
Participant
0 Kudos

Hi

     Just a ledger report also enough. my client is asking me to take a individual ledger report. for different GL accounts in Revenue type

Regards

Vinoth

kothandaraman_nagarajan
Active Contributor
0 Kudos

Refer this thread

former_member229757
Participant
0 Kudos

Hi

     I need a Report like these its in journal entry how to fetch the data

Date           Particulars          costcenter(project)      vchtype     Vchno   Debit   Credit

kothandaraman_nagarajan
Active Contributor
0 Kudos

Where did you get vchtype   &  Vchno fields?

former_member229757
Participant
0 Kudos

Hi

     In Journal am fetching in REf1 and Ref2 field.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try:

SELECT T0.[RefDate], T0.[Memo], T1.[Project], T0.[Ref1], T0.[Ref2], T1.[Debit], T1.[Credit] FROM [dbo].[OJDT]  T0 INNER JOIN [dbo].[JDT1]  T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T2.[AcctName]   =[%0]

Thanks & Regards,

Nagarajan

former_member229757
Participant
0 Kudos

Hi,

     I need individual ledger report query that is individual ledger report means in COA selecting asset in that am selecting particular ledger.After selecting the ledger clicking the balance link.it will open particular ledger transactions

     In that TansNo link if i click it will open JE in that i need Account code, account name and amount

In your above query it showing as memo second column instead of that i need a ledger name

my query

Posting Date  AcctName(ledgerName)   Debit    credit

Regards

Vinoth

kothandaraman_nagarajan
Active Contributor
0 Kudos

Try:

SELECT T0.[RefDate], T2.[AcctName], T1.[Project], T0.[Ref1], T0.[Ref2], T1.[Debit], T1.[Credit] FROM [dbo].[OJDT]  T0 INNER JOIN [dbo].[JDT1]  T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T2.[AcctName]   =[%0]

former_member229757
Participant
0 Kudos

Hi

     Based on your query report shows only particular ledger alone fully..I need in that particular ledger what are the transactions to be made.

    

     I need a report like this guide me

Ledger Name:Cash In Bangalore

Openning Balance

Date                  AccountName(ledgerName)                              Debit                Credit

2-Nov-14              Computer sales                                             5000

2-nov-14               Keyboards Sales                                           1000

4-nov-14               mouse sales                                                  3000

6-nov-14               Other Accesories                                            7000

7-nov-14               Postage & courier expense                                                     100

8-Nov-14              Stationery & Xerox Exp.                                                          300

9-Nov-14              Indian Bank BangaloreA/c (40648678511)          2000

10-Nov-14             Repairs& Maintainance                                                          1500

Closing Balance

Regards

Vinoth

kothandaraman_nagarajan
Active Contributor
0 Kudos

Try:

SELECT T0.[RefDate], T2.[AcctName], T1.[Project], T0.[Ref1], T0.[Ref2], T1.[Debit], T1.[Credit] FROM [dbo].[OJDT]  T0 INNER JOIN [dbo].[JDT1]  T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T0.refdate between [%1] and [%2] and T2.[GroupMask] = 1

former_member229757
Participant
0 Kudos

Hi

     The above query is openning all the ledgers report.In my sample query i gave the condition as Ledger Name :cash In Bangalore what are all the accounts hitting in cash in hand bangalore that alone have to display.

Regards

Vinoth

kothandaraman_nagarajan
Active Contributor
0 Kudos

Try:

SELECT T0.[RefDate], T2.[AcctName], T1.[Project], T0.[Ref1], T0.[Ref2], T1.[Debit], T1.[Credit] FROM [dbo].[OJDT]  T0 INNER JOIN [dbo].[JDT1]  T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T0.refdate between [%1] and [%2] and T2.[GroupMask] = 1 and T2.[AcctName]   =[%0]

former_member229757
Participant
0 Kudos

Hi

     The above query also openning only particular ledger name alone in full report.

     In COA if i select the balance link list of transactions will be openning in another form linke that i need in query

     Here i attached the screen shot for reference.

     In that second screen shot it showing as offset account i need that account name in another column like that i need query.

former_member229757
Participant
0 Kudos

Hi

     Finally i got a query with expected output thanks for your continous support

SELECT t0.account[Acct Code], t1.AcctName[Acct Name], convert(varchar,t0.RefDate,103) as Date,

t0.ContraAct[Offset Acct],(select acctname from oact where acctcode=t0.contraAct) as AcctName,

(t0.debit), (t0.credit),t2.Ref2,t2.Ref3,t2.Project

FROM jdt1 t0 inner join oact t1 on

t1.acctcode = t0.account

inner join OJDT t2 on

t0.TransId=t2.TransId

where t1.AcctName='General Fund - Inland' and t0.refdate between {?FromDate} and {?ToDate}

order by t0.refdate

Thanks & Regards

     Vinoth

Answers (1)

Answers (1)

former_member209066
Active Contributor
0 Kudos

Hi,

Please check JDT1 Table.

Example :

Select * From JDT1 T0 Where T0.[Account]='[%1]'

Thanks,

Nithi