Query to make GL Transaction report

[Former Member]

Dear Experts,

I want to create GL Transaction report.

Can anybody please help me for this?

Thanks,

[kothandaraman_nagarajan]

Hi,

Have you tried with standard report under Financial report?

Thanks & Regards,

Nagarajan

[Former Member]

Hi Sir,

No i have not seen that report till now.

Can you please help to find that report?

Thanks,

[kothandaraman_nagarajan]

Follow Financials-->Financials reports-->Accounting-->General Ledger.

[Former Member]

Hi Sir,

Thank you for information,

As per clients requirements this report given a output.

But still they asking me to create query for this and save like other queries,

For easy to use.

Can you please help me to add commutative balance in this query please!!

SELECT T1.[Account], T2.[AcctName], T0.[RefDate], T0.[DueDate], T0.[TaxDate], T1.BaseRef 'Doc No',

(Case when t0.TransType = '15' then 'Delivery'

  when t0.TransType = '16' then 'Returns'

  when t0.TransType = '203' then 'A/R Down Payment'

  when t0.TransType = '13' then 'A/R Invoice'

  when t0.TransType = '165' then 'A/R Correction Invoice'

  when t0.TransType = '166' then 'A/R Correction Invoice Reversal'

  when t0.TransType = '14' then 'A/R Credit Memo'

  when t0.TransType = '132' then 'Correction Invoice'

  when t0.TransType = '20' then 'Goods Receipt PO'

  when t0.TransType = '21' then 'Goods Return'

  when t0.TransType = '204' then 'A/P Down Payment'

  when t0.TransType = '18' then 'A/P Invoice'

  when t0.TransType = '163' then 'A/P Correction Invoice'

  when t0.TransType = '164' then 'A/P Correction Invoice Reversal'

  when t0.TransType = '19' then 'A/P Credit Memo'

  when t0.TransType = '69' then 'Landed Costs'

  when t0.TransType = '24' then 'Incoming Payment'

  when t0.TransType = '25' then 'Deposit'

  when t0.TransType = '46' then 'Vendor Payment'

  when t0.TransType = '57' then 'Checks for Payment'

  when t0.TransType = '76' then 'Postdated Deposit'

  when t0.TransType = '182' then 'BoE Transaction'

  when t0.TransType = '-2' then 'Opening Balance'

  when t0.TransType = '-3' then 'Closing Balance'

  when t0.TransType = '321' then 'Internal Reconciliation'

  when t0.TransType = '30' then 'Journal Entry'

  when t0.TransType = '58' then 'Stock List'

  when t0.TransType = '59' then 'Goods Receipt'

  when t0.TransType = '60' then 'Goods Issue'

  when t0.TransType = '67' then 'Inventory Transfers'

  when t0.TransType = '68' then 'Work Instructions'

  when t0.TransType = '162' then 'Inventory Valuation'

  when t0.TransType = '202' then 'Production Order'

  when t0.TransType = '-1' then 'All Transactions'

end) as 'Document Type',

T0.Number,

T1.[TransId] 'Trans No',

T1.[ProfitCode],

T1.[FCDebit],

T0.[Memo] 'Remarks',

T1.[ContraAct],

Account=(CASE WHEN T2.AcctName IS NULL THEN  t3.cardname else  T2.AcctName end ),

T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode

LEFT JOIN OCRD T3 ON

T2.AcctCode = T3.DebPayAcct

WHERE

(T0.[RefDate] >= [%0] and T0.[RefDate] <= [%1]) and (T1.[Account] >= [%3] and T1.[Account] <= [%4])

Thanks,

[kothandaraman_nagarajan]

Better use system report. To make it easy for user, add this report under My shortcut.

It is not possible to cumulative balance via query. Only possible in Crystal report.

[Former Member]

Hi Sir,

Awesome Idea!!

Thank you once again..

Problem resolve..

But i don't want to miss this chance to learn-

how to add cumulative balance in the crystal report.

Can you please tell me.?

[former_member392937]

You can do that with running totals grouped by "Doc No"

[kothandaraman_nagarajan]

Have you tried any crystal report before?

[Former Member]

Yes i have some knowledge of crystal report.

[kothandaraman_nagarajan]

Run above query in crystal report by Add command and add your parameter.

[Former Member]

Yes Created...

[kothandaraman_nagarajan]

Issue solved?

[Former Member]

I added parameters only please suggest next

[former_member392937]

What is your problem now ?

[former_member392937]

Please give to us some more clarification.

For example you want all the credit/debit of the accounts for all the journal entries?

You need a Crystal Report or just a query?

With Regards,

Harris K.

[Former Member]

Hi Harris,

Thanks for reply,

I want to add blow fields in to report-

I have attached file with this please check it.

[former_member205766]

Hi

Check the below link

With regards

Balaji

[former_member392937]

Could you please be more specific. What do you need exactly ?

For example the query below gives you all the rows of the journal entries grouped by account and ordered by account too.

Select

account as 'Account Number',

debit as 'Debit amount',

credit as 'Credit amount'

  from jdt1

  group by account, debit, credit

  order by account

If you need a query with all columns you have at the photos, please let as know.

With Regards,

HK

[Former Member]

Hi Harris,

I tried this query as below but i want to add Doc No,Doc Type,Control Acc and Commutative Balance in this. Please assist,

SELECT T1.[Account], T2.[AcctName], T0.[RefDate], T0.[Number], T0.[DueDate], T0.[TaxDate], T1.[TransId], T1.[ProfitCode], T1.[FCDebit],T0.[Memo], T1.[ContraAct],Account=(CASE WHEN T2.AcctName IS NULL THEN  t3.cardname else  T2.AcctName end ), T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line] FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode INNER JOIN OCRD T3 ON T2.AcctCode = T3.DebPayAcct

Thanks,

[former_member392937]

Try the below please:

-- Account = Control Account

-- month = the month (as i saw, you had in the excel)

-- Baseref = Document Number

-- case... is the document type

-- * I could not find the Commutative Balance... could you please help me with that ?

-- i think that you mean Cumulative Balance and there is no field for that. If you want that let me know !!

With Regards,

HK

SELECT

T1.[Account] as 'Control Account',

T2.[AcctName],

T0.[RefDate],

T0.[Number],

t0.BaseRef as 'Doc Number',

T0.[DueDate],

T0.[TaxDate],

month (t0.CreateDate) as 'Month',

T1.[TransId],

T1.[ProfitCode],

T1.[FCDebit],

T0.[Memo],

(Case when t0.TransType = '15' then 'Delivery'

  when t0.TransType = '16' then 'Returns'

  when t0.TransType = '203' then 'A/R Down Payment'

  when t0.TransType = '13' then 'A/R Invoice'

  when t0.TransType = '165' then 'A/R Correction Invoice'

  when t0.TransType = '166' then 'A/R Correction Invoice Reversal'

  when t0.TransType = '14' then 'A/R Credit Memo'

  when t0.TransType = '132' then 'Correction Invoice'

  when t0.TransType = '20' then 'Goods Receipt PO'

  when t0.TransType = '21' then 'Goods Return'

  when t0.TransType = '204' then 'A/P Down Payment'

  when t0.TransType = '18' then 'A/P Invoice'

  when t0.TransType = '163' then 'A/P Correction Invoice'

  when t0.TransType = '164' then 'A/P Correction Invoice Reversal'

  when t0.TransType = '19' then 'A/P Credit Memo'

  when t0.TransType = '69' then 'Landed Costs'

  when t0.TransType = '24' then 'Incoming Payment'

  when t0.TransType = '25' then 'Deposit'

  when t0.TransType = '46' then 'Vendor Payment'

  when t0.TransType = '57' then 'Checks for Payment'

  when t0.TransType = '76' then 'Postdated Deposit'

  when t0.TransType = '182' then 'BoE Transaction'

  when t0.TransType = '-2' then 'Opening Balance'

  when t0.TransType = '-3' then 'Closing Balance'

  when t0.TransType = '321' then 'Internal Reconciliation'

  when t0.TransType = '30' then 'Journal Entry'

  when t0.TransType = '58' then 'Stock List'

  when t0.TransType = '59' then 'Goods Receipt'

  when t0.TransType = '60' then 'Goods Issue'

  when t0.TransType = '67' then 'Inventory Transfers'

  when t0.TransType = '68' then 'Work Instructions'

  when t0.TransType = '162' then 'Inventory Valuation'

  when t0.TransType = '202' then 'Production Order'

  when t0.TransType = '-1' then 'All Transactions'

end) as 'Document Type',

T1.[ContraAct],

Account=(CASE WHEN T2.AcctName IS NULL THEN  t3.cardname else  T2.AcctName end ),

T0.[Ref2],

T0.[Ref3],

T1.[Ref1],

T1.[Ref2],

T1.[Ref3Line]

  FROM OJDT T0

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

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

  INNER JOIN OCRD T3 ON T2.AcctCode = T3.DebPayAcct

[Former Member]

Hi HK,

Yes i  want to add commutative balance in this report.

Thank you for your efforts,

Thanks,

[former_member392937]

If you want to have the cumulative balance in a query it should be a different one. Because at the query you created you have all the lines of jdt1 table. You can't have a cumulative balance in line level.

The only solution for your problem if you want to have a sum of all credits and a sum of all debits at the end of the query is to insert two more fields at the existing query, debit and credit. When you run the query if you click "Ctrl + (the name of the field at header level)" you will have a sum at the end of the query.

[Former Member]

Hi Harris,

I am trying to do as you suggesting me in the crystal report,

But no luck,could you please help me to create cumulative balance?

Thanks,

[former_member392937]

You tried the Ctrl+click or the running totals ?

I didn't understand... Sorry.

With Regards,

HK

[Former Member]

i dont understand what is CTRL+Click action as you said.

I added 2 fields debit and credit to existing query but not able to do sum that fields.

Giving me error

[kothandaraman_nagarajan]

It is not related to crystal report. The Ctrl + Click is used to get total at end of column.

[Former Member]

In Crystal Report how can archive it sir.

[kothandaraman_nagarajan]

What you mean by archive?

[Former Member]

Sorry my typing mistake i mean,

How can i achieve cumulative balance in crystal report?