#136 Transaction Reports Not showing Category report for Split

1.0_and_below
closed-fixed
nobody
None
9
2013-03-14
2008-08-13
Pisharody
No

Transaction Reports for Category is not reflecting Subcategory item totals if the trasaction is a split transaction.

Example:

Automobile category may have subcategories like

Gasoline
Oil Change
Wiper Blade
Air Filter

Now consider simple transaction (Transaction 1)

Automobile:Gasoline 34.55

and another split transaction (Transaction 42)

Sears Auto Center:
Automobile:Oil Change 30.00
Automobile:Air Filter 06.00

Ideally when you request Transaction Report for Category Automobile, you expect the sum of Transaction 1 and Transaction 42 Which is 70.55. But the Report will show 34.55 ignoring split transactions.

Simple Solution is to generate these reports from a new database view which joins few tables as follows

CREATE VIEW TRANSACTIONS_V1 as
select
c.transid TRANSID,
c.transdate TRANSDATE,
a.accountname ACCOUNTNAME,
p.payeename PAYEENAME,
c.transcode TRANSCODE,
i.categname CATEGNAME,
s.subcategname SUBCATEGNAME,
c.totransamount TOTALAMOUNT
from
checkingaccount_v1 c,
accountlist_v1 a,
payee_v1 p,
category_v1 i,
subcategory_v1 s
where
c.categid<>-1 and
c.subcategid <> -1 and
c.accountid=a.accountid and
c.payeeid=p.payeeid and
c.categid=i.categid and
c.categid=s.categid and
c.subcategid=s.subcategid
union all
select
c.transid,
c.transdate,
a.accountname,
p.payeename,
c.transcode,
i.categname,
NULL,
c.totransamount
from
checkingaccount_v1 c,
accountlist_v1 a,
payee_v1 p,
category_v1 i
where
c.categid<>-1 and
c.subcategid = -1 and
c.accountid=a.accountid and
c.payeeid=p.payeeid and
c.categid=i.categid
union all
select
c.transid,
c.transdate,
a.accountname,
p.payeename,
c.transcode,
i.categname,
s.subcategname,
spt.splittransamount
from
splittransactions_v1 spt,
checkingaccount_v1 c,
accountlist_v1 a,
payee_v1 p,
category_v1 i,
subcategory_v1 s
where
spt.transid=c.transid and
c.accountid=a.accountid and
c.payeeid=p.payeeid and
spt.categid=i.categid and
spt.categid=s.categid and
spt.subcategid=s.subcategid;

Also a "NOTE" column for each split items in a split transaction will be helpful.

Thanks

Discussion

  • Pat O

    Pat O - 2009-11-12

    2848241 was a duplicate of this, but contains a helpful mmb file

     
  • Pat O

    Pat O - 2009-11-12
    • priority: 5 --> 7
     
  • Pat O

    Pat O - 2009-11-12
    • priority: 7 --> 9
     
  • Nikolay

    Nikolay - 2013-03-14
    • status: open --> closed-fixed
    • milestone: --> 1.0_and_below
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks