Page 1 of 4

Average Spending per Category/Subcategory Report

Posted: Fri Mar 21, 2014 12:33 pm
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.

Re: Average Spending per Category/Subcategory Report

Posted: Mon Mar 24, 2014 2:32 pm
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>

Re: Average Spending per Category/Subcategory Report

Posted: Tue Mar 25, 2014 3:55 am
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

Re: Average Spending per Category/Subcategory Report

Posted: Tue Mar 25, 2014 7:34 pm
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.

Re: Average Spending per Category/Subcategory Report

Posted: Wed Mar 26, 2014 2:55 am
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.

Re: Average Spending per Category/Subcategory Report

Posted: Wed Mar 26, 2014 8:37 am
by Nikolay
try newest vertion

mmex_svn6254_x64_portable.zip

https://sourceforge.net/projects/moneym ... unstable/?

Re: Average Spending per Category/Subcategory Report

Posted: Wed Mar 26, 2014 12:12 pm
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!

Re: Average Spending per Category/Subcategory Report

Posted: Wed Mar 26, 2014 12:20 pm
by guanlisheng
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

Posted: Wed Mar 26, 2014 1:42 pm
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

Re: Average Spending per Category/Subcategory Report

Posted: Wed Mar 26, 2014 1:46 pm
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