Tax summary report

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

Moderator: Renato

dbolton
Super MMEX User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Tax summary report

Post by dbolton »

I'm new to the report manager. How would I go about creating a report that does the following?
1. Lists all the transactions in a handful of tax-related categories (such as "Interest", "Salary", "Business expense", "Charity", etc.)
2. Includes totals for each of these tax-related categories.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Tax summary report

Post by Nikolay »

Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Tax summary report

Post by Nikolay »

I don't understand your task. But you can start from this report:
https://github.com/moneymanagerex/gener ... ithdrawals

I've added where sc.SUBCATEGNAME in ('Interest', 'Salary') at the botton of the following script.

Code: Select all

SELECT	wd_data.id				id,
	wd_data.date					AS date,
	wd_data.amount				AS amount,
	COALESCE(c.categname, '')
	||  ":" || 
	COALESCE(sc.subcategname, '')	AS cat,
	wd_data.notes				AS notes,
	p.payeename					AS payee,
	acc.accountname				AS account,
	c.PFX_SYMBOL				AS pfx_symbol,
	c.SFX_SYMBOL				AS sfx_symbol,
	c.GROUP_SEPARATOR			AS group_separator,
	c.DECIMAL_POINT				AS decimal_point
FROM (SELECT	t1.transid		AS id,
	t1.transdate				AS date,
	t2.splittransamount		AS amount,
	t2.categid				AS catid,
	t2.subcategid				AS subcatid, 
	''						AS notes,
	t1.payeeid				AS payeeid,
	t1.accountid				AS accountid
	FROM splittransactions_v1 AS t2
		INNER JOIN checkingaccount_v1 AS t1	ON t1.TRANSID = t2.TRANSID
	WHERE  
	t1.transcode = "Withdrawal"
UNION ALL
SELECT	ca.transid	AS id,
	ca.transdate		AS date,
	ca.transamount	AS amount,
	ca.categid		AS catid,
	ca.subcategid		AS subcatid, 
	ca.notes			AS notes,
	ca.payeeid		AS payeeid,
	ca.accountid		AS accountid
	FROM  checkingaccount_v1 AS ca
	WHERE  
		ca.transcode = "Withdrawal" AND ca.categid <>-1
) AS wd_data
LEFT JOIN accountlist_v1 AS acc ON wd_data.accountid = acc.accountid
LEFT JOIN CURRENCYFORMATS_V1 AS c ON c.CURRENCYID = acc.CURRENCYID
LEFT JOIN category_v1 AS c ON wd_data.catid=c.categid
LEFT JOIN subcategory_v1 AS sc ON wd_data.subcatid= sc.subcategid
LEFT JOIN payee_v1 AS p ON wd_data.payeeid = p.payeeid
where sc.SUBCATEGNAME in ('Interest', 'Salary')
ORDER BY date ASC;
mikhailo2608il
Senior MMEX User
Posts: 31
Joined: Tue Jun 12, 2018 3:36 am
Are you a spam bot?: No

Re: Tax summary report

Post by mikhailo2608il »

The SQL query that Nikolay gave you above will give a listing of all the relevant transactions. Getting the subtotals is a bit more challenging. What I suggest is that you export the result of the SQL query above to an HTML file and take that file and put it into a spreadsheet where you use a pivot table to get you the subtotals by category. Let me explain in more detail. I would tweak the SQL to have the date picker allow you to select a range of transactions. To that end you want to change the WHERE clause

ca.transcode = "Withdrawal" AND ca.categid <>-1
to

ca.transcode = "Withdrawal" AND ca.categid <>-1 AND ca.transdate >= '&begin_date' AND ca.transdate <= '&end_date'

This will require that you select the date range you are interested in when you run the report.

Once you run the report you will go into the File menu and select the Export choice and within the Export choice select the Report to HTML choice. After you have saved the report to an HMTL file you open a spreadsheet program (Excel, LibreCalc, ...) and then open the HTML file you exported from Money Manager EX. Now within the spreadsheet program you select the cells that comprise the SQL column headers and the list of transactions to create a pivot table. When the pivot table dialog comes up you move the SQL column header "cat" from the available fields to the row fields and you move the SQL column header "amount" from the available fields to the data fields, hit ENTER and you will be looking at a pivot table that has the subtotals of all the subcategories that were in the original query as well as a grand total.

This is the easiest way to get the information you need. It took me less than 15 minutes to do what is described above from start to finish. And it only required little more than a novice knowledge of Excel. And it is low maintenance in that if you want to include more subcategories in the SQL query you simply update the query within the General Report Manager. The pivot table you create will take care of the subtotals.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If you were determined to accomplish getting the subtotals within Money Manager EX itself then you will be working towards that end for several hours or even days. You will have to do the subtotalling in the LUA tab of the report definition. And you will have to edit the Template tab of the report definition to include those subtotals. And if you ever wanted to subtotal some additional subcategories that you later decide to include then you have to change the LUA code and the Template again in addition to the SQL query.

Regards.
dbolton
Super MMEX User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Tax summary report

Post by dbolton »

Amazing! Nikolay, I adapted your last time to include all the categories I wanted, then added an "ORDER BY" line to group the transactions by category/subcategory.

Code: Select all

WHERE sc.SUBCATEGNAME in ('Charity','Dentist','Doctor','Eyecare')  OR c.CATEGNAME in ('Interest Inc','Salary','Other Inc','Business expense','Tax')
ORDER BY c.CATEGNAME, sc.SUBCATEGNAME, date ASC;
I also deleted SQL code that limited transactions to withdrawals only.

This successfully completes point 1 in my original post. I'm now struggling with point 2. How would I add a row above each category that includes a total for all the transactions in that category?

EDIT: Sorry I didn't refresh my tab and see the additional information from mikhailo2608il. Thank you for the insight.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Tax summary report

Post by Nikolay »

LUA script may help with a subtotals.

Learn how other reports use it

For example:
https://github.com/moneymanagerex/gener ... ontent.lua
dbolton
Super MMEX User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Tax summary report

Post by dbolton »

Nikolay, I am almost there with the totals, but having some trouble with a few of the totals. Is there any rules/limitations to what can be set via:

Code: Select all

result:set("VARIABLE_NAME", value)
For example is there a string length limitation for "VARIABLE_NAME"?
dbolton
Super MMEX User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Tax summary report

Post by dbolton »

I got the totals working in the end. Instead of Lua, I used JavaScript on the table to calculate the sum for each category. This approached worked better for me for three reasons.
* I already know JavaScript.
* I can exporting the report to HTML, open in a browser, and use my normal tools to develop and debug the JavaScript.
* The JavaScript can work with any categories in the table. No need to hard code any categories in Lua or the Template.
siowena
Super MMEX User
Posts: 109
Joined: Tue Jul 17, 2018 8:04 am
Are you a spam bot?: No

Re: Tax summary report

Post by siowena »

Hi dbolton

If you are willing, please export the report into a grm files and upload into the report section on github as others may find this useful.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Tax summary report

Post by Nikolay »

Nice.
Aslo you could upload a zip archive of the report here.
Then I can add it to GitHub repo
https://github.com/moneymanagerex/general-reports
Post Reply