The three basic reports 

• Balance sheet
• Profit and Loss Statement
• Trial balance

Some context for reporting

“Golden” rules of accounting:

• Real account: Debit what comes in and credit what goes out.
• Personal account: Debit who receives and Credit who gives.
• Nominal account: Debit all expenses & losses and Credit all incomes and gains

	Debit	Credit
Asset	Increase	Decrease
Liability	Decrease	Increase
Equity	Decrease	Increase
Income	Decrease	Increase
Expenditure	Increase	Decrease

7.1   Balance Sheet

A balance sheet summarizes an organization or individual’s assets, equity and liabilities at a specific point in time. 

Formula : Assets = Liabilities + Equity

7.1.1 Sample Query

The below query is for assets, the queries for liabilities and equity would be same but for the calculation of balance (would be credit – debit refer verbiage in section 6)

select debits.glcode as ‘glcode’, debits.name as ‘name’, (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as ‘balance’
from
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘debitamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’DEBIT’
and acc_coa.category in (‘ASSET’)
group by name
order by glcode) debits
LEFT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as ‘creditamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’CREDIT’
and acc_coa.category in (‘ASSET’)
group by name) credits
on debits.name=credits.name

union

select credits.glcode as ‘glcode’, credits.name as ‘name’, (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as ‘balance’
from
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘debitamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’DEBIT’
and acc_coa.category in (‘ASSET’)
group by name
order by glcode) debits
RIGHT OUTER JOIN
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘creditamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’CREDIT’
and acc_coa.category in (‘ASSET’)
group by name
order by glcode) credits
on debits.name=credits.name;

7.2  Income Statement (P & L statement)

Income Statement is a company’s financial statement that indicates how the revenue (money received from the sale of products and services before expenses are taken out) is transformed into the net income (the result after all revenues and expenses have been accounted for, also known as Net Profit ).
It displays the revenues recognized for a specific period, and the cost and expenses charged against these revenues.

Formula: Net Income = Total Income – Total Expenses

7.2.1 Sample Query

Below query is for Income, the query for expenses would be similar (save for calculation of balance, refer verbiage in section 6)

select *
from
(
select debits.glcode as ‘glcode’, debits.name as ‘name’, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as ‘balance’
from
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘debitamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’DEBIT’
and acc_coa.category in (‘INCOME’)
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) debits
LEFT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as ‘creditamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’CREDIT’
and acc_coa.category in (‘INCOME’)
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name) credits
on debits.name=credits.name

union

select credits.glcode as ‘glcode’, credits.name as ‘name’, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as ‘balance’
from
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘debitamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’DEBIT’
and acc_coa.category in (‘INCOME’)
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) debits
RIGHT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as ‘creditamount’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’CREDIT’
and acc_coa.category in (‘INCOME’)
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) credits
on debits.name=credits.name)as fullouterjoinresult
order by glcode;

7.3 Trial Balance

A trial balance is a list of all the General ledger accounts (both revenue and capital) contained in the ledger of a business.

This list will contain the name of the nominal ledger account and the value of that nominal ledger account.The value of the nominal ledger will hold either a debit balance value or a credit balance value.

The debit balance values will be listed in the debit column of the trial balance and the credit value balance will be listed in the credit column.

7.3.1 Sample Query

This is the complete query for the report

select *
from
(select debits.glcode as ‘glcode’, debits.name as ‘name’,IF(debits.type = ‘ASSET’ or debits.type = ‘EXPENSES’, ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),”) as ‘debit’, IF(debits.type = ‘INCOME’ or debits.type = ‘EQUITY’ or debits.type = ‘LIABILITIES’, ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),”) as ‘credit’
from
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘debitamount’,acc_coa.type as ‘type’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’DEBIT’
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) debits
LEFT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as ‘creditamount’,acc_coa.type as ‘type’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’CREDIT’
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) credits
on debits.name=credits.name
union
select credits.glcode as ‘glcode’, credits.name as ‘name’,IF(credits.type = ‘ASSET’ or credits.type = ‘EXPENSES’, ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),”) as ‘debit’, IF(credits.type = ‘INCOME’ or credits.type = ‘EQUITY’ or credits.type = ‘LIABILITIES’, ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),”) as ‘credit’
from
(select acc_coa.gl_code as ‘glcode’,name,sum(amount) as ‘debitamount’,acc_coa.type as ‘type’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’DEBIT’
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) debits
RIGHT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as ‘creditamount’,acc_coa.type as ‘type’
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type=’CREDIT’
and acc_journal_entry.entry_date between ‘2012-11-01’ and ‘2012-11-31’
group by name
order by glcode) credits
on debits.name=credits.name) as fullouterjoinresult
order by glcode