Monthly category report with selectable categories and time range?

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Monthly category report with selectable categories and time range?

Post by DavidGreen »

With cost of living increases, I'd like to monitor the trend of all my monthly spending in specific categories (like food), and see a chart over the past 6 or 12 months, say. Please can you suggest a report which allows me to select one or more categories to report, and the time period?

The closest I can find is the Monthly Categories report but its chart amalgamates all categories over the last 12 months, and shows data for every category.
The Monthly Income vs Expenses report contains exactly the Period and date selector I need, but has no category filter.

I have no experience of coding SQL and have not found anything similar to what I need in https://github.com/moneymanagerex/general-reports. When I have imported the CategoryTrend report, I don't even understand how to update category and subcategory!
dbolton
Super User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Monthly category report with selectable categories and time range?

Post by dbolton »

The CategoryTrend report sounds like what you are looking for. It is set up to show Food:Groceries initially. Is that part working for you?

Although it isn't ideal, you can get different categories by editing one line of the SQL. Here's instructions to edit the category and subcategory.
  1. From the main menu, select Tools > General Report Manager.
  2. In the left pane, select Category-CategoryTrend.
  3. In the right pane, select the SQL tab.
  4. On line 35, replace the word 'Food' with the category you want to track: WHERE cat.CATEGNAME = 'Food'
  5. On line 35, replace the word 'Groceries' with the subcategory you want to track (or delete the second part, from 'AND' on, if you don't want to limit to a single subcategory): AND subcat.SUBCATEGNAME = 'Groceries'
  6. Select Save.
  7. Select Run.
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

Thanks for your clear instructions. After editing line 35 as you explain I can generate a Category Trend Report.

Is there any way to add a Period and date selector like I see in the Monthly Income vs Expenses report?

David Green
User avatar
Renato
MVP User
Posts: 669
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Monthly category report with selectable categories and time range?

Post by Renato »

Probably this would be the solution: https://github.com/moneymanagerex/money ... ssues/4282
Renato Forum Administrator
dbolton
Super User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Monthly category report with selectable categories and time range?

Post by dbolton »

You could insert the following lines immediately after line 35 of the SQL tab:

Code: Select all

	AND TRANSDATE >= '&begin_date'
	AND TRANSDATE <= '&end_date'
The selectable date range doesn't work in the Report Manager but if you Save and Close the manger then go to the report in the main window it shows a date range drop-down menu at the top.
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

Thank you dbolton.

Is there something similar I can do to add a category filter/selector?

D Green
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

dbolton wrote: Tue Apr 12, 2022 8:27 pm You could insert the following lines immediately after line 35 of the SQL tab:

Code: Select all

	AND TRANSDATE >= '&begin_date'
	AND TRANSDATE <= '&end_date'
The selectable date range doesn't work in the Report Manager but if you Save and Close the manger then go to the report in the main window it shows a date range drop-down menu at the top.
Before I add these lines the report generates a bar chart and data table, but after adding the lines the chart disappears and the data is empty.
trendBefore.png
trendBefore.png (115.11 KiB) Viewed 7880 times
trendAfter.png
trendAfter.png (46.64 KiB) Viewed 7880 times
Nikolay
Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Monthly category report with selectable categories and time range?

Post by Nikolay »

Could you run the report from the Navigation tree?
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

This is the output from the Navigation tree.
User avatar
Renato
MVP User
Posts: 669
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Monthly category report with selectable categories and time range?

Post by Renato »

for me it works with these lines:
.
where cat.CATEGNAME = 'Haus' and subcat.SUBCATEGNAME = 'Garten'
and t1.STATUS <> 'V'
AND TRANSDATE >= '2018/01/01'
AND TRANSDATE <= '2020/12/31'
group by year, month
order by year asc, month asc;
Renato Forum Administrator
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

those lines give me:
SQL logic error[1]: no such column: t1.STATUS
User avatar
Renato
MVP User
Posts: 669
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Monthly category report with selectable categories and time range?

Post by Renato »

.
this is my SQL Code:

Code: Select all

-- TODO: Update category and subcategory name in line 19. 
select strftime('%Y', t1.TRANSDATE) as YEAR
    , strftime('%m', t1.TRANSDATE) as MONTH
    , total(t1.TRANSAMOUNT * c1.BASECONVRATE) 
    + (select total(split.SPLITTRANSAMOUNT * c2.BASECONVRATE) from CHECKINGACCOUNT_V1 as t2
       inner join ACCOUNTLIST_V1 as a2 on a2.ACCOUNTID = t1.ACCOUNTID
       inner join CURRENCYFORMATS_V1 as c2 on c2.CURRENCYID = a2.CURRENCYID
       inner join SPLITTRANSACTIONS_V1 as split on t2.TRANSID = split.TRANSID
       where strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
       and strftime('%m', t2.TRANSDATE) = strftime('%m', t1.TRANSDATE)
       and split.CATEGID = cat.CATEGID
       and split.SUBCATEGID = subcat.SUBCATEGID
       and t2.STATUS <> 'V') as AMOUNT
from CHECKINGACCOUNT_V1 as t1
inner join ACCOUNTLIST_V1 as a1 on a1.ACCOUNTID = t1.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c1 on c1.CURRENCYID = a1.CURRENCYID
inner join CATEGORY_V1 as cat on t1.CATEGID = cat.CATEGID
inner join SUBCATEGORY_V1 as subcat on t1.SUBCATEGID = subcat.SUBCATEGID
where cat.CATEGNAME = 'Haus' and subcat.SUBCATEGNAME = 'Garten'
    and t1.STATUS <> 'V'
    AND TRANSDATE >= '2018/01/01'
    AND TRANSDATE <= '2020/12/31'
group by year, month
order by year asc, month asc;
Renato Forum Administrator
dbolton
Super User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Monthly category report with selectable categories and time range?

Post by dbolton »

I created a new report that lets you select the period and category.

Instructions
  1. Download the attachment below (Category amounts over time.grm)
  2. Open Money Manager Ex
  3. From the main menu, choose Tools > General Report Manager
  4. Choose Import and select the download file (Category amounts over time.grm )
  5. Choose Close
  6. In the left pane choose Reports > Category amounts over time
Attachments
Category amounts over time.grm
(2.68 KiB) Downloaded 346 times
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

Thanks dbolton!

Please is there a way to make the following improvements?:
  • ability to select by sub category as well
  • remember Category/sub-category selection when date Period changes (currently reverts to 1st category!)
  • report the average value over the selected period
  • tabulate data under the chart
D Green
dbolton
Super User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Monthly category report with selectable categories and time range?

Post by dbolton »

I understand the want for subcategories. I want it myself, but omitted it to begin with (to simplify making the report).

I don't know a way to remember the category selection using the built-in date selection (It reloads the whole page from scratch using new data each time you select a period). Out of curiosity, what do you use the period selection for? I tend to pick "Over Time" and not change it after that.

Also it would be helpful to understand how you would use the data table. The chart does show the exact numbers if you hover. Are you looking for a table that matches the category you select and shows the same data as the chart (plus maybe the averages)?
DavidGreen
New User
Posts: 11
Joined: Fri Jul 03, 2020 2:28 pm
Are you a spam bot?: No

Re: Monthly category report with selectable categories and time range?

Post by DavidGreen »

dbolton wrote: Fri Apr 29, 2022 1:56 am Out of curiosity, what do you use the period selection for? I tend to pick "Over Time" and not change it after that.
At the end of each month I want to see what our spending has been in category 'Food' subcat 'Grocery shopping' over 'Last 90 days' say, so that I can see how we've done against previous months.
dbolton wrote: Fri Apr 29, 2022 1:56 amAlso it would be helpful to understand how you would use the data table. The chart does show the exact numbers if you hover. Are you looking for a table that matches the category you select and shows the same data as the chart (plus maybe the averages)?
I'm trying to easily read each month's values. A table showing each month's value would help, or annotation on the chart itself. Yes - I'd expect data only for the cat/sub-cat I've selected. The average value (plus a line on the graph!) would be helpful.

For inspiration, the Homebank application handles this reporting pretty neatly, although average only shown with cumulative data:
1.png
1.png (48.12 KiB) Viewed 10347 times
2.png
2.png (52.79 KiB) Viewed 10347 times
3.png
3.png (54.98 KiB) Viewed 10347 times
Nikolay
Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Monthly category report with selectable categories and time range?

Post by Nikolay »

Have you already looked at the budget reports?
Post Reply