Account Summary Report

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
Post Reply
siena123
MMEX Developer
Posts: 9
Joined: Thu Jan 24, 2013 1:00 am
Are you a spam bot?: No

Account Summary Report

Post by siena123 »

  • SQL
  • [code] select a.ACCOUNTNAME, c.BASECONVRATE, c.PFX_SYMBOL, c.SFX_SYMBOL, (select INITIALBAL + total(TRANSAMOUNT) from (select ACCOUNTID, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, STATUS, TOTRANSAMOUNT from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer') as t where t.ACCOUNTID = a.ACCOUNTID and t.STATUS <> 'V') as Balance from ACCOUNTLIST_V1 as a inner join CURRENCYFORMATS_V1 as c on c.CURRENCYID = a.CURRENCYID where a.ACCOUNTTYPE in ('Checking', 'Term') and a.STATUS = 'Open' group by a.ACCOUNTNAME order by a.ACCOUNTNAME asc; [/code]
  • Lua
  • [code] local total = 0; function handle_record(record) local bal = string.format("%.2f", record:get('Balance')); local base = bal * record:get("BASECONVRATE"); record:set("Balance", record:get('PFX_SYMBOL') .. bal .. record:get('SFX_SYMBOL')); record:set("Base", base); total = total + base; end function complete(result) result:set("Total", total); end [/code]
  • Template
  • [code] <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title><TMPL_VAR REPORTNAME></title> <STYLE><TMPL_INCLUDE NAME="master.css"></STYLE> </head> <body> <div class="container"> <h3>Account Summary Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class="table"> <thead> <tr> <th>Account Name</th> <th class="text-right">Balance</th> <th class="text-right">Base Currency</th> </tr> </thead> <tbody> <TMPL_LOOP NAME=CONTENTS> <tr> <th><TMPL_VAR ACCOUNTNAME></th> <td class="text-right"><TMPL_VAR Balance></td> <td class="money text-right"><TMPL_VAR Base></td> </tr> </TMPL_LOOP> </tbody> <tfoot> <tr class="total"> <td>Total</td> <td></td> <td class="money text-right"><TMPL_VAR Total></td> </tr> </tfoot> </table> </div> <table class="table"> <tbody> <TMPL_LOOP ERRORS> <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr> <tr><td><TMPL_VAR ERROR></td></tr> </TMPL_LOOP> </tbody> </table> </div> </div> </body> <script type="text/javascript"> function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);} var elements= document.getElementsByClassName("money"); for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";} </script> </html> [/code]
Attachments
AccountSummary.grm
(1.7 KiB) Downloaded 557 times

ogouin
New MMEX User
Posts: 3
Joined: Mon May 17, 2010 10:15 am
Are you a spam bot?: No

Re: Account Summary Report

Post by ogouin »

Thanks for your report. How can I transform the SQL code to have the Account Summary by Year ?

Many Thanks
Olivier

gwalker
New MMEX User
Posts: 2
Joined: Sat Oct 18, 2014 2:29 am
Are you a spam bot?: No

Re: Account Summary Report

Post by gwalker »

I get the error Block not terminated when i run the report.  I cut and pasted the relevant code into the respective sections.

Any ideas please?

Mukund
New MMEX User
Posts: 12
Joined: Thu Oct 30, 2014 4:51 pm
Are you a spam bot?: No

Re: Account Summary Report

Post by Mukund »

gwalker wrote:I get the error Block not terminated when i run the report.  I cut and pasted the relevant code into the respective sections.

Any ideas please?
Please find & compare.
Attachments
Account Summary.grm
(1.94 KiB) Downloaded 450 times

guanlisheng
MMEX Developer
Posts: 205
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Account Summary Report

Post by guanlisheng »

Any package in the forum should be considered as outdated one and please download from github directly


LiSheng

Post Reply