Selectable date range

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

Moderator: Renato

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

Selectable date range

Post by dbolton »

Any way to make a selectable date range for reports in the customizable General Reports Manager (similar to the built-in reports)?
whall3y
MMEX Developer
Posts: 343
Joined: Fri Aug 21, 2020 5:44 pm
Are you a spam bot?: No

Re: Selectable date range

Post by whall3y »

Use &from_date and &end_date in your SQL.

Not sure if any of the example reports use this, but the AccountBalances shows how to use a single date - so you will get help looking at that for what to do in SQL and template as neccessary
https://github.com/moneymanagerex/gener ... ntBalances

E.g.

Code: Select all

    select ACCOUNTID, STATUS
        , (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
        , TRANSDATE
    from CHECKINGACCOUNT_V1    
    where STATUS <> 'V'
        and TRANSDATE >= '&from_date'
        and TRANSDATE <= '&end_date'
dbolton
Super MMEX User
Posts: 126
Joined: Fri Jan 03, 2020 3:24 pm
Are you a spam bot?: No
Contact:

Re: Selectable date range

Post by dbolton »

Thank you for that! Here's what I found so far.
  • You must include the single quotes around '&from_date' and '&end_date' to avoid an SQL error (like your example showed, but I accidentally omitted them my first time).
  • You must add both the '&from_date' and '&end_date' to the SQL for the date menu to appear in Money Manager EX (I initially experimented with just the '&from_date' but that doesn't do anything).
I'm working with a variation of the Transation-Withdrawals report. If I add the following WHERE clause to the very bottom of the SQL, it seem to always show all withdrawals regardless of the date range I select.

Code: Select all

WHERE 
	wd_data.date >= '&from_date'
	AND wd_data.date <= '&end_date'
ORDER BY date ASC;
Any idea what I'm missing?
whall3y
MMEX Developer
Posts: 343
Joined: Fri Aug 21, 2020 5:44 pm
Are you a spam bot?: No

Re: Selectable date range

Post by whall3y »

My error, it should be begin_date and end_date
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Selectable date range

Post by Nikolay »

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

Re: Selectable date range

Post by dbolton »

That did the trick! Thank you!
Post Reply