Editing Reports

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
petemac
New MMEX User
Posts: 7
Joined: Tue May 17, 2016 8:07 am
Are you a spam bot?: No

Re: Editing Reports

Post by petemac »

Thank you very much, that's interesting. What are the year parameters - 1 January to 31 December? What I generally need are the financial year totals, which for me are 1 May to 30 April (hence needing the custom date function if going beyond the Current/Last Financial Years in the stock installation). I can see how to edit the years in the way you suggest, so that's good esp as I'm a SQL know-nothing. What could I edit to introduce the financial year parameters in a simple way?

I look forward to version 4.

siowena
MVP MMEX User
Posts: 104
Joined: Tue Jul 17, 2018 8:04 am
Are you a spam bot?: No

Re: Editing Reports

Post by siowena »

petemac wrote:Thank you very much, that's interesting. What are the year parameters - 1 January to 31 December? What I generally need are the financial year totals, which for me are 1 May to 30 April (hence needing the custom date function if going beyond the Current/Last Financial Years in the stock installation). I can see how to edit the years in the way you suggest, so that's good esp as I'm a SQL know-nothing. What could I edit to introduce the financial year parameters in a simple way?

I look forward to version 4.
Yes, the year is Calendar Jan-01 to Dec-31.

I've modified the report to now read the Financial Year start month/day form the MMEX Tools -> Options -> General -> Financial Year settings.

You'll need to change the line 20 in the SQL to FIN_YEAR and also change and line 44 for the years you are interested.
Attachments
Income vs Expenses Transactions By Year.zip
(3.99 KiB) Downloaded 440 times
Income vs Expenses Transactions By Year.zip
(3.99 KiB) Downloaded 464 times

petemac
New MMEX User
Posts: 7
Joined: Tue May 17, 2016 8:07 am
Are you a spam bot?: No

Re: Editing Reports

Post by petemac »

Thanks again. I've got it working, paddling into personally uncharted waters with that sort of data manipulation. I'm a graphic designer not a coder though I've worked with html and php. SQL is probably something I'll mostly leave to those with a talent for it, like yourself. Cheers.

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

Re: Editing Reports

Post by Nikolay »

Using substr('&single_date', 1, 4) and strftime('%Y', c.TRANSDATE) year you can create report that will change date by a date widget.

Code: Select all

with p as (select PAYEEID, PAYEENAME from PAYEE_V1),
c as (
select 
strftime('%Y', c.TRANSDATE) year,
c.payeeid, count(*) count, 
sum( TRANSAMOUNT) summ
from CHECKINGACCOUNT_V1 c
where c.STATUS !='Void' and c.TRANSCODE='Deposit'
group by c.payeeid
)
select 
-- c.year, 
p.payeename, c.count, 
c.summ,
case  when  c.summ>45 then '*****' when c.summ>35 then '****' when c.summ>25 then '***'  when c.summ>15 then '**' else '*' end stars
from c, p
where p.payeeid = c.payeeid
and c.year= substr('&single_date', 1, 4)
group by c.payeeid
order by 4 desc, 3 desc, 1

Post Reply