Hello, I’m close to getting my MDX working but I’m really struggling to get
it completely working and any help would be greatly appreciated. I posted a
while back and with some assistance got something working in some instances.
I have a sales cube with revenue in it and an exchange rate cube. My current
MDX (in a virtual cube) is:


IIF( [Website Code].CurrentMember.Name = "All Country",
( [Revenue], [Country].[UK])+
SUM( Descendants( [Week Dimension].CurrentMember
, [Week Dimension].[Report Date])
, ([Revenue], [Country].[US]) / ( [Ex Rate], [Week Dimension] )
)
,[Revenue])

This to me says if we’re at the All Country level give me the UK revenue
plus the US revenue / Ex rate. This is spot on when I simply have my cube
viewed at the week dimension. The report date is where the exchange rate is
stored and obviously this can’t roll up. However, my fact table has some
other dimensions built from it (such as product). If I slice with any other
dimensions then I just get -1.#J on anything but the All Country level. I
need to get something like: (there are other dimensions)

All Country level
Week 51

Revenue
Product A 150
Product B 231

Where revenue is the revenue value in my fact table but divided by the
exchange rate at the day level when it’s US and then summed up. Any help
would be greatly appreciated as I’m really struggling with what to do.

An idea of the data is as follows:

I have a cube with a fact table like:

Reportdate Country Revenue Product
01 Dec 2004 UK 100.00 A
01 Dec 2004 UK 50.00 B
01 Dec 2004 US 214.00 A
01 Dec 2004 US 209.00 B
02 Dec 2004 UK 131.25 A
02 Dec 2004 UK 171.00 B
02 Dec 2004 US 167.45 A
02 Dec 2004 US 237.27 B


The revenue value is in £ for UK and $ for US. It has a time dimension with
day and week. I then have an exchange rate cube something like:

ReportDate Country ExRate
01 Dec 2004 US 1.89432
02 Dec 2004 US 1.89355

I created a virtual cube and in that cube I want my revenue value to be -
when at the UK level of my country dimension I should see the UK £ figure,
when at the US level to see the $ figure. That's straightforward because
that’s the fact table. But when at the All Country Level I want to see UK(£)
plus US($) converted into (£) via the exchange cube.

Looks like some of the dimensions (eg: Product) don't apply directly to
the exchange rate cube - this is a common scenario for using
ValidMeasure() in virtual cubes:

[Revenue], [Country].[UK]) +
SUM(Descendants([Week Dimension].CurrentMember,
[Week Dimension].[Report Date]),
([Revenue], [Country].[US]) /
ValidMeasure(([Ex Rate], [Country].[US])))
, [Revenue])

- Deepak

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

That's spot on Deepak. Thank-you very much. I didn't even know that function
(perhaps I should buy a book).

Merry Christmas
Dave



"Deepak Puri" wrote: