Create an annual summary Report of Categories?

Page 1 of 1
6 posts
Create an annual summary Report of Categories?

xylodrum56
New MMEX User

Posts: 6
Joined: Tue Apr 05, 2016 1:41 pm
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?

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Re: Create an annual summary Report of Categories?

xylodrum56
New MMEX User

Posts: 6
Joined: Tue Apr 05, 2016 1:41 pm
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?

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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?

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
As result it will be somthing like that:

screenshot.55.jpg
(109.21 KiB) Downloaded 257 times

Is that what are you looking for?
Re: Create an annual summary Report of Categories?

xylodrum56
New MMEX User

Posts: 6
Joined: Tue Apr 05, 2016 1:41 pm
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
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 3 guests