Each record in my fact table as a currency key and unit of measure key.



I have two measures in my COST cube - Amount and Qty.


In the fact table every record as a value for Amount & Qty and each
record as a value for
Amount currency like USD, EUR, or CAD and Qty as a unit of measure like

- EA, KG, KM,etc


All our users are asking me to do is provide a report that can show the

values like the
following:


8200 USD
9800 CAD


102.5 KG
1200 KM


I am bewildered I cannot do such a simple thing in analysis Services
the cube is the COST cube and I have Currency key and Unit key for unit

of measure. All I want is display the amount with the currency key
concatenated in Excel as well as on Web similarly the unit of measure.


For displaying Amount this is what I have done. I have created a new
calculated member called CAmount with the following MDX:


iif (
[Currency].currentmember ='CAD',
Format([Measures].[Amount],
"0.00" + " " + [Currency].Currentmember.name + " " ), NULL
)


But this somehow seems not to work some syntax errors.


All I want is the system for each of the currencies to set a value for
the display format using the approrpiate currency key so I can get the
display as above.


But this is driving me insane I cannot get such a simple scenario of
concatenating and displaying a measure and its associated Unit of
measure or currency.


I would greatly appreciate your help.


Thanks
Karen

Hi Karen,


From your earlier posts, there is also a [Plant] dimension; and measures
associated with a [Plant] are related to a [Currency] or [Unit], based
on its location. However, this location relationship is only represented
in the fact table, not explicitly in the [Plant] dimension.

When reporting at the individual Plant grnaularity, the associated
Currency and Unit would have to be inferred from measures - which would
not work when rolling up Plants:

[Measures].[CAmount]:
"0.00" + " "
+ Filter([Currency].[Currency].Members,
Not IsEmpty([Measures].[Amount])).Item(0).Item(0).Name
+ " ")

[Measures].[CQty]:
"0.00" + " "
+ Filter([Unit].[Unit].Members,
Not IsEmpty([Measures].[Qty])).Item(0).Item(0).Name
+ " ")

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak

Not sure if I understood you correctly.

This is how I changed the CAmount Member definition

WITH
MEMBER [Measures].[CAmount] AS 'Format([Measures].[Amount], "0.00" + "
" + Filter([Currency].[Currency].Members, Not
IsEmpty([Measures].[Amount])).Item(0).Item(0).Name + " " )'

SELECT {[Measures].[CAmount]} on columns,
{[Plant].Members} on rows
FROM [COSTCUBE]



After I tried this MDX I am not getting any currency values for the
measure Amount at all now.


Thanks
Karen

Hi Karen,

Here's a similar query for Foodmart Sales, which suffixes 'M' or 'F' to
Unit Sales for a customer, depending on their Gender (pl. note that
customer data in Foodmart has surprising genders for some names!)

MEMBER [Measures].[CSales] AS 'Format([Measures].[Unit Sales],
"0.00" + " " + Filter([Gender].[Gender].Members,
Not IsEmpty([Measures].[Unit Sales])).Item(0).Item(0).Name
+ " " )'

SELECT {[Measures].[CSales]} on columns,
{[Customers].[All Customers].[USA].[OR].[Salem].Children} on rows
FROM [Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***