Calculated Member... Using case statment?!

Dear Friends,

I have a doubt that probably is to simple for you, but I'm a beginner in this subject and I need you.

how can I do a calculated member with case statment? I need to calculate the market value for factData based on each dimension...

DimInstrument InstrumentID InstrumentName 1 Bonds 2 Equities 3 Swaps ... ... 20 CreditSwaps

FactDados FactID InstrumentID AmountPos FxRate ... MarketValue 1 1 200.394,00 1 ... ... 2 2 776.000,00 1 ... ... 3 1 2.143.000,00 1,34 ... ... 4 18 546.560,00 1,34 ... ... 5 2 120.000,00 1 ... ... ... ... ... ... ... ... 123000 1 2.095.930,00 1 ... ...

For example the same calculated member, in the case of dimension is Bonds(1) the formula is one but when the dimension is Equities(2) the formula is diferent .

How can i do it? In the finally I only want the MarketValue (calculated member) for all the dimensions..

understood?

Thanks and regards!!

Hi,

Sure you can use case statement. Probably the best place to do it is a calculated memeber in dsv difinition of your fact table. This field should be calculated on a process step of your cube.

-Andrew

|||

Andy,

In SSAS you have Named Calculations is DSV and Calculater Members in the cube.

I want a calculated member and not named calculation because the formula is based on other calculated members that I dont have already in DSV, only in the cube...

So, what you told does not make sense, but thanks!!

|||Pedro,

Try this link - should help you plenty: http://technet.microsoft.com/en-us/library/ms144841.aspx

Rob|||Using SCOPE statements http://technet.microsoft.com/en-us/library/ms145989.aspx should provide better performance than using CASE. Custom Member formulas on the dimension members would be another option, it depends on the particular calculation as to which is the better option.|||Yeah - do what Darren suggests. He's an expert in MDX. But I'll restrain myself from advertising the user-group meeting that he's presenting at next week.

Rob|||

Thanks All!!

Where is the Darren meeting group?

Regards!

|||

I'm speaking at the Adelaide SQL Server User Group next week (the one that Rob is President of )

http://www.sqlserver.org.au/events/ViewEvent.aspx?EventId=263