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.
Create an annual summary Report of Categories?
Moderator: Renato
-
- New User
- Posts: 6
- Joined: Tue Apr 05, 2016 7:41 pm
- Are you a spam bot?: No
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: Create an annual summary Report of Categories?
Could you try using GRM
https://sourceforge.net/p/moneymanagere ... 20Manager/
somthing like
https://github.com/moneymanagerex/gener ... st12Months
https://sourceforge.net/p/moneymanagere ... 20Manager/
somthing like
https://github.com/moneymanagerex/gener ... st12Months
-
- New User
- Posts: 6
- Joined: Tue Apr 05, 2016 7:41 pm
- Are you a spam bot?: No
Re: Create an annual summary Report of Categories?
Thank you Nikolay! I think I am beginning to understand.Nikolay wrote:Could you try using GRM
https://sourceforge.net/p/moneymanagere ... 20Manager/
somthing like
https://github.com/moneymanagerex/gener ... st12Months
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?
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: Create an annual summary Report of Categories?
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
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: Create an annual summary Report of Categories?
As result it will be somthing like that:
Is that what are you looking for?
Is that what are you looking for?
-
- New User
- Posts: 6
- Joined: Tue Apr 05, 2016 7:41 pm
- Are you a spam bot?: No
Re: Create an annual summary Report of Categories?
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, 2014Nikolay wrote:As result it will be somthing like that:
Is that what are you looking for?