Dear Jacopo, Thanks so much for your kind help, the solution works perfectly as it is exactly what I wanted ! Now I can summary the total in the dimensions I want, i.e. MONTH and YEAR. Thanks so much again, and I also think it is helpful to commit these two added functions to public trunk in case others have similar requirements like mine.
-- 发自我的ONEPLUS智能手机 Jacopo Cappellato <[email protected]>编写： >month is not a standard function of SQL even if nowadays is available in >most database management systems. >However you may find it useful the patch below that adds the support for >the date and month functions (if others are interested it could be >committed to public repository). >In order to use it you will have to define an alias field like: > ><alias entity-alias="EX" name="trx_date" function="month"/> > >I hope it helps, > >Jacopo > >Index: >../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java > >=================================================================== > >--- >../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java >(revision >1637645) > >+++ >../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java >(working >copy) > >@@ -65,6 +65,8 @@ > > functionPrefixMap.put("count-distinct", "COUNT(DISTINCT "); > > functionPrefixMap.put("upper", "UPPER("); > > functionPrefixMap.put("lower", "LOWER("); > >+ functionPrefixMap.put("year", "YEAR("); > >+ functionPrefixMap.put("month", "MONTH("); > > } > > > > /** Contains member-entity alias name definitions: key is alias, value >is ModelMemberEntity */ > >@@ -480,7 +482,7 @@ > > fieldSet = alias.getFieldSet(); > > } > > } > >- if ("count".equals(alias.function) || >"count-distinct".equals(alias.function)) { > >+ if ("count".equals(alias.function) || >"count-distinct".equals(alias.function) || "year".equals(alias.function) || >"month".equals(alias.function)) { > > // if we have a "count" function we have to change the type > > type = "numeric"; > > } > > >On Sat, Nov 15, 2014 at 8:19 AM, [email protected] < >[email protected]> wrote: > >> Guys, >> >> I have an Expense entity with a trx_date colum of type DATE to record the >> date that I bought sth. >> And then I wanna summary the total expenses by month, and I hit a blocker. >> I tried to create an view entity with a complex alias to represent the >> month, it's like: >> <alias name="month" group-by="true"> >> <complex-alias operator="month()"> >> <complex-alias-field entity="Expense" field="trx_date"> >> </complex-alias> >> </alias> >> >> However, it did not work. Could anyone help me out here to get what I >> wanted? Thanks so much! >> >> I am using the default database derby by the way. And Ofbiz version is >> 12.04. >> -- >> 发自我的ONEPLUS智能手机
