In the COST cube I have a measure - Amount and I also have a dimension
called Currency.

The currency holds values like CAD, USD, EUR, AUD, MXP, SGD,etc the
currency key.

When I display the Amount measure value I want to display it in the
following format:

8900 USD
8200 CAD
5800 MXP
2200 SGD


So I have created a calculated member NetAmount which reflects the
measure Amount with a Format "0.00" instead I want the format to be
like "0.00 USD" or more specifically I want it to be general like

concatenate("0.00", Currency.Currenmember) so that way depending on
whatever currency key value I have for the current amount it will be
used for generating the right currency format.

WITH
MEMBER [Measures].[NetAmount] AS 'Format([Measures].[Amount], "0.00")'

SELECT
{[Measures].[NetSales]} on Columns,
{[Plant].Members} on Rows
FROM [Cost]

Help is greatly appreciated.

Thanks
Karen

Not sure if I've understood your problem but if it is just a case of
sticking the currency code after the value then you can concatenate
them together once you've converted the numbers to a string. The code
below will do it....

WITH
MEMBER [Measures].[NetAmount] AS 'Str(Format([Measures].[Amount],
"0.00")) + " " + [Currency].[Currentmember].name'

SELECT
{[Measures].[NetAmount]} on Columns,
{[Plant].Members} on Rows
FROM [Cost]

Cheers,
L



KarenM wrote:

Hi Lozza
Thanks for your MDX calc member somehow it did not work.

I tried the following variant of your MDX calc member:

WITH
MEMBER [Measures].[NetAmount] AS 'Format([Measures].[Amount], "0.00" +
" " + [Currency].Currentmember.name + " " )'

This works but the only problem is I now see the values like the
following for amount:

Plant Amount
------------------------
ANC 9800 All Currency
BCD 8200 All Currency
------------------------


I see for all values it shows the All Currency but not the actual
currency each plant is in either USD, CAD or EUR,etc but it shows All
Currency for all of them.

Appreciate your help

Thanks
Karen



Lozza wrote:

please help

Thanks
Karen

Hi Karen,
The only way I can get the currency to show is if you actually cross
join to the currency dimension at the lower level, but this would mean
that you'll see the currency dimension as well as the formatting at the
end of your number fields as per below...

Plant Curr Amount
---------------------------------------
ANC USD 9800 USD
BCD EUR 8200 EUR
---------------------------------------

I'll see if I can come up with anything better but in the meantime my
only other solution is to create a 'currency' member property for each
of your plants and then concatenate that into the amount field. (I
haven't tried it but it should be possible)

Cheers,
L



KarenM wrote:

Hi Lozza

Thanks for the update unfortunately I cannot create a linkage between
currency to plant as it will become difficult to maintain this linkage
on a ongoing basis as new plants are added.

I will await for the other option you said you are looking at.

I did not quite understand what you meant by cross join currency.

Thanks
Karen
Lozza wrote: