Create an annual summary Report of Categories?

MMEX4Desktop, incl. Windows, macOS (10.10 or newer), and Most Unix variants using the GTK+ toolkit

Moderator: Renato

Post Reply
xylodrum56
New MMEX User
Posts: 6
Joined: Tue Apr 05, 2016 7:41 pm
Are you a spam bot?: No

Create an annual summary Report of Categories?

Post 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.
Nikolay
MMEX 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?

Post by Nikolay »

xylodrum56
New MMEX 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?

Post 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?
Nikolay
MMEX 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?

Post 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
Nikolay
MMEX 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?

Post by Nikolay »

As result it will be somthing like that:
screenshot.55.jpg
screenshot.55.jpg (109.21 KiB) Viewed 3348 times
Is that what are you looking for?
xylodrum56
New MMEX 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?

Post 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
Post Reply