Calculated member with measure and dimension

Hi,

We have a little problem with our calculated member... in our table we have the total amount of a sale in 2 different currencies (US and CAN).

In another field we have the currency (1 being can, 2 being US)

Now I need to create a measure that will give me all the amount in CAN money and one in US money (depending on how users want to view the cube) but it's not working since my Exchange rate is not a measure and the type of currency isn't either, so the system isn't entering in my case.

I tried to do something like this :

case [Probill General].[Payeur Fund type]

when 1

then ([Measures].[Probill Total] * [Probill General].[Us Exch Rate])

when 2

then ([Measures].[Probill Total])

end

But it doesn't enter in the cases and it gives me an empty data field when I put it in the cube.

Any idea on how to bypass that problem?

Thanks,

Jason

Hello. One problem with your Case statement is that you do no write this in the TSQL way.

Instead try something like:

Case When [Probill General].[Payeur Fund type] = 1

Then [Measures].[Probill Total] * [Probill General].[Us Exch Rate])

Else [Measures].[Probill Total]

End

A good Blog post about currency conversion you can find here: http://blogs.conchango.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx

HTH

Thomas Ivarsson

|||

Didn't work...

I went to that page and tried to create something based on that which didn't really solved my problem.

I think the real problem in my cube lies in the fact that my table has total that are both in canadian funds and us funds... all the solution on the web are to convert a full us fund (or other) to another currency but I can't seem to find anything where I can just transform or not an amount into a currency based on a dimension field...

I talked to my boss and she wants everything in Canadian funds, which means that IF my "[Probill General].[Payeur Fund type] = 1" then "[Measures].[Probill Total]" stays the same, but if it's "[Probill General].[Payeur Fund type] = 2" then it has to be "[Measures].[Probill Total] / [Probill General].[Us Exch Rate]" .

Any idea on how you can apply that in a cube?

Thanks

|||

With only two currencies the simple solution is to solve this problem in the ETL-process(not inte cube), when you load your data mart/data warehouse.

You will need a currency table with dates and currencies. If you have the currency code in the fact table when you load data, you join the date and the currency with your currency table.

If the requirement is that all values should be i CAD you only need to keep the exchange rate for CAD against your incoming currencies.

HTH

Thomas Ivarsson