MDX: Calculated Dimensions?

Question

I don't know if this is possible or not, or if my limited knowledge of MDX is pushing me in the wrong direction...

The cube I'm dealing with has two different dimensions for dates, [Statement Dates] and [Premium Dates]. Further in the hierarchy of each looks like this:

[Statement Dates].[Statement Year].[2008]
[Payment Dates].[Payment Year].[2008]

For the business logic I'm implementing I need to do something like:

select
  ({ [Measures].[Commission] }) on columns,
  ({[Products].[Product Category]}) on rows
from [Cube]
where
(
  IIF( [Products].[Product Category].CurrentMember.Name = "Some Category",
       [Statement Dates].[Statement Year].[2008], 
       [Payment Dates].[Payment Year].[2008] )
)

So I need it to discriminate what dimension to use for filtering the year based on what product category is being used.

This parses ok and the query runs, but the numbers seem to suggest that the IIF statement is always returning false.

Answer 1

Because the WHERE clause gets evaluated first the .CurrentMember function in the IIF will only be seeing "All Product Cateogories". In which case the [Products].[Product Category].CurrentMember.Name will never be equal to "Some Category" as the only product category in context is "All Product Cateogories".

One possible work around would be to do a calculation like the following:

WITH MEMBER Measures.Commission2 as 
SUM(
{[Products].[Product Category].[Product Category].Members}
, IIF( [Products].[Product Category].CurrentMember.Name = "Some Category"
   , ([Statement Dates].[Statement Year].[2008],[Measures].[Commission])
   , ([Payment Dates].[Payment Year].[2008].[Measures].[Commission]) )
)
select  
   ({ [Measures].[Commission2] }) on columns
,  ({[Products].[Product Category]}) on rows
from [Cube]

You could also do a scoped assignement in the calculation script in your cube to do this sort of thing.