DataScope G/L Finanacial Statements

Data Setup

The Keys to

• Assign all Accounts to meaningful groups. For example:
  • EXPCOGS
  • EXPGA
  • ASTCUR
  • ASTFIX
• Assign all groups Heading group to top level Groups:
  • INC: Income
  • EXP: Expenses
  • AST: Assets
• Setup Sequence Numbers for all Groups. For Example
  • AST: 100
  • ASTCUR: 110
  • ASTFIX: 150
  • INCSALES: 410

Query

Income Statement Only:

select g.sequence||' '||g.group_id||' '||g.line_description Group_Name,t.gl_account||'  '||A.Description Account,G.PARENT_GROUP_ID PARGRP,(select sequence from gltacgrp gp where gp.group_id=g.parent_group_id) pgseq,a.gl_group,
t.trx_date,-t.trx_amount trx_amount,t.source_app,description_1, description_2,a.gl_account account,a.description,session_no,trans_no,a.segment_1,a.segment_2,Trx_Date Mo,trx_date yr, trx_date qtr from glttrx t
inner join gltacct a on a.gl_account=t.gl_account  
left outer join gltacgrp g on g.group_id = a.gl_group 
where trx_date between :beg_date and :end_date and trans_type not in ('B','R') and a.account_type='I'  
order by g.sequence

Income Statement and Balance Sheet:

select g.sequence||' '||g.group_id||' '||g.line_description Group_Name,t.gl_account||'  '||A.Description Account_name,t.trx_date yr,t.trx_date mo,t.trx_date,-t.trx_amount trx_amount,t.source_app,session_no,trans_no,description_1, description_2,a.gl_account account,a.description   ,G.PARENT_GROUP_ID PARGRP,(select sequence from gltacgrp gp where gp.group_id=g.parent_group_id) pgseq
,a.segment_1,a.segment_2,A.ACCOUNT_TYPE IB,a.NOTES_1||' '||a.notes_2 "GL Account" from glttrx t
inner join gltacct a on a.gl_account=t.gl_account   
left outer join gltacgrp g on g.group_id = a.gl_group
where (trx_date between :beg_date and :end_date and trans_type not in ('B','R')) and gl_account not between 'Y' AND 'Z'
  union
select g.sequence||' '||g.group_id||' '||g.line_description Group_Name,t.gl_account||'  '||A.Description Account,
cast ('1/1/2000' as date),cast ('1/1/2000' as date),trx_date,-t.trx_amount,t.source_app,session_no,trans_no,description_1, description_2,a.gl_account account,a.description
,G.PARENT_GROUP_ID PARGRP,(select sequence from gltacgrp gp where gp.group_id=g.parent_group_id) pgseq
,a.segment_1,a.segment_2,A.ACCOUNT_TYPE,NOTES_1||' '||notes_2 from glttrx t
inner join gltacct a on a.gl_account=t.gl_account   
left outer join gltacgrp g on g.group_id = a.gl_group
where (trx_date < :beg_date and trans_type not in ('B') and a.account_type='B')

Income Statement with separate drilldown query:

Main Query

select 
 gp.sequence pgseq,
 G.PARENT_GROUP_ID PARGRP,
 g.sequence, g.group_id, g.line_description,
 t.gl_account, A.Description,
 t.trx_date,
 t.Trx_Date Mo,t.trx_date yr, t.trx_date qtr,
 sum(-t.trx_amount) trx_amount,
 sum(-t.trx_amount) pct_comp
from glttrx t
inner join gltacct a on a.gl_account=t.gl_account  
left outer join gltacgrp g on g.group_id = a.gl_group 
left outer join gltacgrp gp on gp.group_id = g.parent_group_id
where (t.trx_date between :beg_date and :end_date) and 
 (t.trans_type not in ('B','R')) and
 (a.account_type='I') and
 (t.gl_account not between 'Y' AND 'Z')
group by gp.sequence, g.parent_group_id, g.sequence, g.group_id, g.line_description,
 t.gl_account, a.description, t.trx_date

Drilldown Query

select
 a.gl_group,
 t.trx_date, t.trx_amount, t.source_app,
 t.description_1, t.description_2,
 t.gl_account, a.description,
 t.session_no, t.trans_no,
 a.segment_1, a.segment_2
from glttrx t
inner join gltacct a on a.gl_account=t.gl_account  
left outer join gltacgrp g on g.group_id = a.gl_group 
left outer join gltacgrp gp on gp.group_id = g.parent_group_id
where (t.trx_date between :beg_date and :end_date) and 
 (t.trans_type not in ('B','R')) and
 (a.account_type='I') and
 (t.gl_account not between 'Y' AND 'Z') and
[WHERE pgseq=gp.sequence]
[WHERE pargrp=g.parent_group_id]
[WHERE sequence=g.sequence]
[WHERE group_id=g.group_id]
[WHERE line_description=g.line_description]
[WHERE gl_account=t.gl_account]
[WHERE description=a.description]
[WHERE yr=extract(year from t.trx_date)]
[WHERE mo=extract(month from t.trx_date)]