Page 1 of 1
Create an annual summary Report of Categories?
Posted: Tue Apr 05, 2016 11:37 pm
by xylodrum56
Hello,
Does anyone know if it is possible to create a Report which shows a summary of categories for a given year?
Been at it for a few days but I can't find a way to do it. Also cannot find a way to go back further than 18 months (last financial year).
The Help directive is of no use in this regard, right-clicking on the REPORTS label in the navigation tree does not bring up a "new report" option.
Am I asking this software to do something that it is not designed to do?
Thanks very much, I appreciate any and all input.
Re: Create an annual summary Report of Categories?
Posted: Fri Apr 08, 2016 2:50 pm
by Nikolay
Re: Create an annual summary Report of Categories?
Posted: Fri Apr 08, 2016 3:10 pm
by xylodrum56
Thank you Nikolay! I think I am beginning to understand.
At the links you provided, I do not see a report template for Category Summaries prior to Last Year, which is what I need. In order to create that do I have to write the corresponding source code?
Re: Create an annual summary Report of Categories?
Posted: Fri Apr 08, 2016 3:16 pm
by Nikolay
Try to run this SQL in GRM:
Code: Select all
with c AS (select c.CATEGID, s.SUBCATEGID
, c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
from CATEGORY_V1 c, SUBCATEGORY_V1 s
where c.CATEGID = s.CATEGID
union all
select CATEGID, -1 SUBCATEGID, CATEGNAME from CATEGORY_V1),
t as (select t.TRANSDATE, strftime('%Y', t.TRANSDATE) year , strftime('%m', t.TRANSDATE) month
, t.transcode, t.PAYEEID
, case ifnull(t.categid, -1) when -1 then s.categid else t.categid end categid
, case when ifnull(t.categid, -1) = -1 AND ifnull(t.subcategid, -1) = -1 then s.subcategid else t.subcategid end subcategid
, (case ifnull(s.splittransid, -1) when -1 then t.transamount else s.splittransamount end)
*(case t.transcode when 'Deposit' then 1 else -1 end) as Amount
from checkingaccount_v1 t
LEFT JOIN splittransactions_v1 s ON s.transid=t.transid )
select t.year , t.month, c.CATEGORY, total(t.AMOUNT) as total
from t
inner join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
where t.TRANSDATE <=strftime('%Y-%m-%d', '2015-12-31')
and t.TRANSDATE >=strftime('%Y-%m-%d', '2015-01-01')
group by t.year, t.month, c.CATEGORY
order by t.month, c.CATEGORY
Re: Create an annual summary Report of Categories?
Posted: Fri Apr 08, 2016 3:18 pm
by Nikolay
As result it will be somthing like that:
- screenshot.55.jpg (109.21 KiB) Viewed 3420 times
Is that what are you looking for?
Re: Create an annual summary Report of Categories?
Posted: Fri Apr 08, 2016 3:54 pm
by xylodrum56
Nikolay wrote:As result it will be somthing like that:
1350: screenshot.55.jpg
Is that what are you looking for?
Thank you Nikolay, that script gives me an accounts total, to date. What I am trying to to is a list of Category totals for the Annual Year 2014 - Jan 1 - Dec 31, 2014