Hi and thanks to all those who've helped me previously. I'm trying to get the
exchange rate working in my cubes. I have a cube with a fact table like:

Reportdate Country Revenue
01 Dec 2004 UK 100.00
01 Dec 2004 US 214.00
02 Dec 2004 UK 131.25
02 Dec 2004 US 167.45

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
thats the fact table. But when at the All Country Level I want to see UK(£)
plus US($) converted into (£) via the exchange cube. I'm getting close. The
following works correctly when my time dimension is at the day (report date)
level:

IIF ([Country].CurrentMember.Name = "All Country Code",
([Revenue], [Country].[UK])+
(([Revenue], [Country].[US]) / ([ExRate], [Week Dimension])),

However, when I go up to the week level the ExRate is the rolled up values
over the week. US revenue / that days exchange rate rolled up. Any help would
be greatly appreciated.

Hi Dave

If I understand you correctly, then what you need is something like this:

IIF( [Country].CurrentMember.Name = "All Country Code"
, ( [Revenue], [Country].[UK])+
IIF( [Week Dimension].CurrentMember.Level is [Week Dimension].[Day]
, ( [Revenue], [Country].[US] ) / ( [ExRate], [Week Dimension] )
, sum( descendants( [Week Dimension].CurrentMember
, [Week Dimension].[Day]
, ( [Revenue], [Country].[US] ) / ( [ExRate], [Week Dimension] )
)
)
....
The key here is the recursive contruct, where you roll the children in your
Week Dimension.

When you are not on the Day-level ("IIF( [Week
Dimension].CurrentMember.Level is [Week Dimension].[Day]" is not fullfilled)
you roll up the values from the day-level with "sum( descendants(..." which
targets itself on the day-level.

Please forgive me if the syntax is not 100% correct, I had a little trouble
in keeping track of all the parentheses.
Also I'm not 100% sure that the " / ( [ExRate], [Week Dimension] )" is the
the right place.

Hopes this helps you.

Best regards
Dan Reving

"DaveK" <DaveK (AT) discussions (DOT) microsoft.com> skrev i en meddelelse
news:06C23F94-0A24-4B06-B1AC-A9634167CB0A (AT) microsoft (DOT) com...

Thanks I'll take it look. It certainly points me in the right direction
hopefully.

Thanks again.

"Dan Reving" wrote: