Average Spending per Category/Subcategory Report

31 posts
Average Spending per Category/Subcategory Report

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
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 400 times
Re: Average Spending per Category/Subcategory Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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>
Re: Average Spending per Category/Subcategory Report

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
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
Re: Average Spending per Category/Subcategory Report

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
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.
Re: Average Spending per Category/Subcategory Report

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
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.
Re: Average Spending per Category/Subcategory Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
try newest vertion

mmex_svn6254_x64_portable.zip

https://sourceforge.net/projects/moneymanagerex/files/mmex_unstable/?
Re: Average Spending per Category/Subcategory Report

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
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!
Re: Average Spending per Category/Subcategory Report

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
you can view the report like other normal reports directly within MMEX if you are using the latest binary for window.
Re: Average Spending per Category/Subcategory Report
User avatar
Renato
Super MMEX User

Posts: 107
Joined: Mon Apr 25, 2011 1:36 pm
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
regards Renato

use MMEX since 2009
Re: Average Spending per Category/Subcategory Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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
Who is online

Users browsing this forum: No registered users and 1 guest