Monthly category report with selectable categories and time range?

[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]

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]

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

[Renato]

Probably this would be the solution: https://github.com/moneymanagerex/money ... ssues/4282

[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]

Thank you dbolton.

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

D Green

[DavidGreen]

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 (115.11 KiB) Viewed 2029 times

trendAfter.png (46.64 KiB) Viewed 2029 times

[Nikolay]

Could you run the report from the Navigation tree?

[DavidGreen]

This is the output from the Navigation tree.

[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;