Average Spending per Category/Subcategory Report

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

Moderator: Renato

dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Average Spending per Category/Subcategory Report

Post by dimidimi »

Hi everyone!

Here's my first attempt to build a custom report. I'm pretty sure it can be fine tuned, at least as far as the output is concerned. Please note that in the SQL statement I have explicitly excluded PayeeId 15 (

Code: Select all

payeeid <> 15
) as it represent some sort of temporary items in my setup.

Hope you find it useful. If you have any suggestions feel free to let me know
Dimitrios

PS. I haven't uploaded to the General Report collection on Github yes as the report lacks the Lua section and also I did not know how to create a .htt file.
Attachments
Average_Spending_Per_Category_Subcategory.grm
(1.39 KiB) Downloaded 690 times
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Average Spending per Category/Subcategory Report

Post by Nikolay »

That super if users can create reports. Seems GRM concept is working!

Dima,

try this template:

Code: Select all

<!DOCTYPE html><h3><TMPL_VAR REPORTNAME></h3>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title><TMPL_VAR REPORTNAME></title>
    <link href = "master.css" rel = "stylesheet">
</head>
<body>
<TMPL_VAR TODAY><hr>
<table class = "table">
<thead>
    <tr>
        <th>Category</th>
        <th>Subcategory</th>
        <th>Total Spend</th>
        <th>Months Active</th>
        <th>Average Spending</th>
    </tr>
</thead>
    <TMPL_LOOP NAME=CONTENTS>
        <tr>
        <td><TMPL_VAR "Category"></td>
        <td><TMPL_VAR "Subcategory"></td>
        <td class="money"><TMPL_VAR "Total Spend"></td>
        <td><TMPL_VAR "Months Active"></td>
        <td class="money"><TMPL_VAR "Average Spending"></td>
        </tr>
    </TMPL_LOOP>
</table>
<TMPL_LOOP ERRORS>
    <hr>
    <TMPL_VAR ERROR>
</TMPL_LOOP>
</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>
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Average Spending per Category/Subcategory Report

Post by guanlisheng »

Dimitrios, welcome to General Report board and your SQL is perfect. one screenshot is attached.
屏幕快照 2014-03-25 上午11.48.21.png
(132.77 KiB) Downloaded 1149 times
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by dimidimi »

guanlisheng wrote:Dimitrios, welcome to General Report board and your SQL is perfect. one screenshot is attached.
屏幕快照 2014-03-25 上午11.48.21.png
I'm glad you liked it guanlisheng :) I like your layout a lot better! I tried using Nikolay's template above, but it looks like the javascript portion did not have any effect on the numerical data; i.e. I did not get any currency formatting :(. Btw, is the "Lua" section mandatory in order to fork a report to the Github?

EDIT: I experimented a little bit the Nikolay's template and noticed that table formatting was applied after I included the full path name to the reference of master.css. However, I amm still unable to make the javascript portion work. I would greatly appreciate any pointers.
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Average Spending per Category/Subcategory Report

Post by guanlisheng »

Hi Dimitrios, the general report on windows platform is still problematic to load related css & js resource files.
The work around is to visit http://localhost:8080/ with your chrome browser.

Lua section is not mandatory and it just help to revise the dataset returned by SQL.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Average Spending per Category/Subcategory Report

Post by Nikolay »

try newest vertion

mmex_svn6254_x64_portable.zip

https://sourceforge.net/projects/moneym ... unstable/?
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by dimidimi »

Hi Nikolay! I used the latest unstable build you proposed and tried the workaround by visiting http://localhost:8080/, using three different browsers and still was not able to get the currency formatting to work. Let me know if you want me to send you the source of the generated html report. Thanks!
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Average Spending per Category/Subcategory Report

Post by guanlisheng »

you can view the report like other normal reports directly within MMEX if you are using the latest binary for window.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Average Spending per Category/Subcategory Report

Post by Renato »

Sorry Guys

I have the same Problem, its not work, the script is not executed, numbers are not formatted.
http://localhost:8080/ results on 404 Page not found
Renato Forum Administrator
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Average Spending per Category/Subcategory Report

Post by Nikolay »

download last dev release
https://sourceforge.net/projects/moneym ... _unstable/?

if report started from navigation panel (and mmex still started) the page localhost:8080 will be available
Post Reply