Some reports should be updated

[vomikan]

https://forum.moneymanagerex.org/viewtopic.php?f=16&t=8015

[vomikan]

For simple update I suggest to add with CURRENCYFORMATS_V1 as

with CURRENCYFORMATS_V1 as (with h as (select  CURRENCYID 
        , max(CURRVALUE) CURRVALUE from CURRENCYHISTORY 
        where  CURRDATE<= date('now')
        group by CURRENCYID)
select c.CURRENCYID, c.CURRENCYNAME, c.PFX_SYMBOL, c.SFX_SYMBOL
    , DECIMAL_POINT, GROUP_SEPARATOR, SCALE, CURRENCY_SYMBOL
    , CURRENCY_TYPE, HISTORIC
         , ifnull(h.CURRVALUE, 1) BASECONVRATE
        from CURRENCYFORMATS c
        left join h on h.CURRENCYID=c.CURRENCYID)
select * from CURRENCYFORMATS_V1

[vomikan]

Example:
https://github.com/moneymanagerex/general-reports/blob/master/CategoriesStatLast12Months/sqlcontent_db_v13

[valsamis-d]

Thank you vomikan!

The issue, though, after upgrading to Alpha_6 is that my graphs won't show up. I fixed my queries in order to follow the revised schema, data are being returned in a tabular format but the graph above the report is not being drawn.

Dimitrios

[vomikan]

I've update Lua script. Related issue: moneymanagerex/moneymanagerex#1695
Average Spending per Category & Subcategory (To Date) - SQL Based.grm.zip