I have something tricky to do (at least tricky for me).
I have a cube, in this cube we have:
An amount, dimensions and dates
As date we have a startdate and enddate.
When the user launch the report he must filled a fromdate and a todate.
Then I'll compare fromdate and startdate, todate and enddate.
I keep the smallest of (todate, enddate): date2 and the biggest of (fromdate, startdate): date1
My amount is for a full year so my prorata is:
amount / 365 * (date2-date1)
To be able to do so I had to store the startdate end enddate as a key figures in the cube.
The problem is when I don't display the very details of each line in the cube, the prorata is not properly computed as the startdate and enddate are no longer corrects.
This depend on the agregation function on the object (SUM, MAX or MIN).
I need a prorata temporis based the sum of the details of each prorata temporis and not a prorata temporis based on the sum of the Amount and dates...
So if anyone has an idea about this, that would more than usefull.