In my cube I have a Time Dimension CRM Date and a Session dimension. The
session dimension essentially tracks when the cube was processed. So there
is one for November, one for December, etc. Each session that is processed
contains all the data for all time. It is a nice way to give some point in
time capabilities in the cube.

So I have a calculated member where I sum up all my amounts for the selected
CRM Date (or last date if the All level is selected). Problem is, after I
process December and my CRM Date dimension contains days for december, when I
look back at my November session, it calculates to 0. This is because the
"last date" of the CRM Date dimension contains no data in the novemeber
session.

I have this IIF statement too chose which date to end on:

IIF ( CDate(Tail(Descendants([CRM
Date].CurrentMember,,Leaves),1).Item(0).Properties("CRM Date")) <
CDate([Session].CurrentMember.Properties("Session Date")),
TupleToStr(Tail(Descendants([CRM Date].CurrentMember,,Leaves),1).Item(0) ),
Format(
[Session].CurrentMember.Properties("Session Date"),
"""[CRM Date].[Year].&[" + [Session].CurrentMember.Properties("FYYear") +
"].&[""" + [Session].CurrentMember.Properties("FYQuarter") +
"""].&[""yyyymm""].&[""yyyymmdd""]"""
)
)

As you can see, ugly and it's also very slow since I reuse the same code in
multiple calculated measures. Any suggestions on how to improve the speed or
architect this better?

What are you trying to accomplish? A little explanation will help so others
don't have to first reverse engineer your code sample.

--
Cheers,

'(' Jeff A. Stucker
\

Business Intelligence
www.criadvantage.com
--------------------------------------
"Twig" <Twig (AT) discussions (DOT) microsoft.com> wrote

Sure. This IIF statement essentially defines the set I sum across. So my
calculated measure is:

SUM(Measures.[Amount], IIF(...))

The IIF itself is determining which is the last available date that I can
use to SUM across. If the CRM Date selected is less then the SessionDate
Property of my Session dimension, it uses the CRM Date. If the SessionDate
is less, it uses the CRMDate leaf that matches that date. If no CRM Date is
selected, it gets the last leaf and treats it as the selected one.


"Jeff A. Stucker" wrote:

By avoiding iif() and the associated need to convert tuples to strings,
a simpler expression can be constructed (don't know if it improves
performance, though):

[CRM Date].CurrentMember,,Leaves)),
Filter([CRM Date].[Day].Members,
[CRM Date].Properties("CRMDate") =
[Session].Properties("Session Date"))))).Item(0)

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!