Search code examples
jodatimeiccube

Usage of joda.time in icCube

I would like to use the following MDX statement:

with member [x] as now()->plusMonths(1)->withDayOfMonth(1)->minusDay(1)
select [x] on 0
from sales

But I get the error "withDayOfMonth" is unknown. I know the function "plustMonths()" works fine. How can I make this other Joda function work?

The following line is active in the icCube.xml. The help explicitely states to add child packages as well if these are required, but I do not know if withDayOfMonth is a child package and I do not know where to find that:

<allowedPackage>org.joda.time</allowedPackage>
Solution

  • Unfortunately, now() is creating an internal date/time object that do not support yet all the JODA methods (we'll add them in the next release). In the meantime, here are several way to compute the end of month:

    with
 // Be aware it is the server's end of month not the client, if you don't see the problem you've been lucky...for the time being.

 // using MDX functions ( function can be added to the schema )
 function ic3_EOM() as DateTime( now().year() , now().month() +1, 1 )->plusDays(-1)

 // using JODA DateTime ( function can be added to the schema )
 function ic3_EOM_2() as J!org.joda.time.DateTime()->plusMonths(1)->withDayOfMonth(1)->minusDays(1)->toLocalDate()

 member [ic3_EOM] as ic3_EOM()
 member [ic3_EOM_2] as ic3_EOM_2()

select { [ic3_EOM], [ic3_EOM_2] } on 0 from [sales]