I have a cube that displays open invoices. One of my dimensions is time. The aggregation on this dimension cannot be additive (because a particular invoice that was open in Jan might still be open in Feb and would be counted multiple times). To get around this the selected approach was to make a custom rollup on the time dimension that says “[date].currentmember.lastchild” thus the number displayed for Jan’s total is the last day of Jan and the number displayed for 2003 is the last day of the last month of the year. This works well until you add another filter that reduces that data set returned. It is then possible to see a case where there is no data for the last day of 2003 but there is data for some children within the year (e.g. there is data for Jan but none for Dec 31). Since the last period has no data the entire year is not displayed and the user looses visibility to data within that year

The simple solution for this seemed to change the time dimension custom rollups to be “CoalesceEmpty([date].currentmember.lastchild,0)” thus displaying the value of the last period or 0 if the last period has no data. This allows us to effectively display empty rows or cells for only one dimension, time, while leaving the others unaffected. Where the problem arises is I can’t figure out an MDX formula for the leaf level (in our case the day level) that will just allow me to take the value that would normally be returned from the fact table and use it in a formula. My plan would be to use CoalesceEmpty(whatever this MDX is, 0) to force the day level to display 0 for empty cells. Any attempt to return this “normal” value in formula just returns #err

Can anyone help with a solution

thank
don

Hello Don,

I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you.

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Don,

I learned you want to use "CoalesceEmpty([date].currentmember.lastchild,0)"
to force the leaf level of the dimension (date level in this case) to
display 0 for empty cells. If I have misunderstood, please feel free to let
me know. Based on my experience, because the members of the leaf level of
the dimension did not have child, I am afraid you cannot use
[date].currentmember.lastchild, 0) to roll up. It seems you need to verify
if the current member has children before.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

I am using CoalesceEmpty([date].currentmember.lastchild,0) on the non-leaf
levels. I am looking for a similar function that will work on the leaf
levels. If should return the normal value for that leaf or a zero if the
leaf is empty.

thanks
don

""Yuan Shao"" <v-yshao (AT) online (DOT) microsoft.com> wrote

Hi Don,

Thanks for your update. It sees you are working on the closing balance
problem. I fully understand the problem you encountered. Based on my
knowledge, I am afraid there is not an easy way to help you using custom
rollup formular. To deal with this kind of issue, we always preferred to
use calculated member. I have attached the following article with the mail
to you for your reference.

Topic: OLAP Services: Semiadditive Measures and Inventory Snapshots

Also, as a simple workaround, you can create a view of the fact table.
which will modify the Null value data to 0.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.