New Category report

[vomikan]

Regarding this issue I've created GRM adapted report
moneymanagerex/moneymanagerex#1689

Categories_v4.zip

[lexa2]

Hi @vomikan,

Nice work, looks beautiful. There are bugs though :-(. As I've been writing quite some time ago in moneymanagerex/moneymanagerex#789 it is a somewhat challenge to get accounts in different currencies to play well for this report within the bounds of the GRF framework.

I hadn't been following changes in GRF framework since than so I'm not sure if things hadn't changed now but at least I can state that the report you posted here produces incorrect results for my main database. My main DB currency is RUR but I've got several accounts billed in EUR and USD. It looks like all expenditures and reciepts for these accounts are treated to be RUR without taking rates conversion into the account.

Also there's an UI bug in MMEx (the build I used is from git commit 51e4e669, master branch): when you select period to be anything else than "Current month" and then click on any other report and then click back on the "Categories_v4" you will end up with report generated for "Current month" period while "Period" dropdown list will show your last choice instead. It is not related to this issue and your report, it is a bug in MMEx, I just thought it'd worth mentioning as chances are you'd be faster than me chasing this one.

[slodki]

My original SQL from moneymanagerex/moneymanagerex#1689 should correctly report all amounts in base currency using historical rates (or 1:1 if not defined):

select
  CATEGNAME || coalesce(':' || SUBCATEGNAME, '') as ID,
  sum(TRANSAMOUNT) as amount
from (
  select
    coalesce(s.CATEGID, c.CATEGID) as CATEGID,
    coalesce(s.SUBCATEGID, c.SUBCATEGID) as SUBCATEGID,
    coalesce(CURRVALUE,1)*coalesce(SPLITTRANSAMOUNT,TRANSAMOUNT)*(case TRANSCODE when 'Withdrawal' then -1 else 1 end) as TRANSAMOUNT
  from CHECKINGACCOUNT c
    left join SPLITTRANSACTIONS s on s.TRANSID = c.TRANSID
    left join ACCOUNTLIST a on a.ACCOUNTID = c.ACCOUNTID
    left join CURRENCYHISTORY hist on (hist.CURRENCYID = a.CURRENCYID AND CURRDATE = (
      select max(h2.CURRDATE)
      from CURRENCYHISTORY h2
      where h2.CURRDATE<=TRANSDATE
    ))
  where TOACCOUNTID <> 32702 and TRANSCODE <> 'Transfer'
) as trans
  left join CATEGORY cat on cat.CATEGID = trans.CATEGID
  left join SUBCATEGORY subcat on subcat.SUBCATEGID = trans.SUBCATEGID  
group by ID
having amount < 0 -- <0 == Withdrawal, >0 == Deposit
;

[lexa2]

@slodki, thanks for your example query. In attachment you may find an updated Categories_v4 report with SQL query utilizing currency history in a similar way you did in your report. I had also done some minor changes to template (replaced toLocaleString with toFixed(2) at relevant places)

2018-07-19-#01-Categories_v4.zip

[vomikan]

This sql would work fine if currency exchange rates has been set correctly for all transaction date range.
If exchange rate is missing for some date in the past - it became 1.

[slodki]

This

select max(h2.CURRDATE)
from CURRENCYHISTORY h2
where h2.CURRDATE<=TRANSDATE

will:

1. select currency rate for given date if found
2. last known rate before given date
3. rate=1 if there are no historical rates till given date with coalesce(CURRVALUE,1)

[vomikan]

Categories_v5.zip

[vomikan]

In htt file the following line may be replaced by next one:

//var color = d3.scaleSequential(d3.interpolateRainbow);
var color = d3.scaleOrdinal()
    .range(["#FFFF33","#6699FF","#FF9900","#66FF66","#FF6633","#FFCCCC", "#FF6699"]);