Page 1 of 1

Tax summary report

Posted: Mon Jun 15, 2020 5:11 pm
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.

Re: Tax summary report

Posted: Tue Jun 16, 2020 6:08 am
by Nikolay

Re: Tax summary report

Posted: Tue Jun 16, 2020 6:18 am
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;

Re: Tax summary report

Posted: Wed Jun 17, 2020 2:34 am
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.

Re: Tax summary report

Posted: Thu Jun 18, 2020 7:26 am
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.

Re: Tax summary report

Posted: Thu Jun 18, 2020 12:02 pm
by Nikolay
LUA script may help with a subtotals.

Learn how other reports use it

For example:
https://github.com/moneymanagerex/gener ... ontent.lua

Re: Tax summary report

Posted: Fri Jun 19, 2020 5:30 am
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"?

Re: Tax summary report

Posted: Sun Jun 21, 2020 6:45 am
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.

Re: Tax summary report

Posted: Sun Jun 21, 2020 2:48 pm
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.

Re: Tax summary report

Posted: Mon Jun 22, 2020 1:22 pm
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