Problem
After creating a calculated member in your SSAS cube, you connect to the cube via Excel 2007 and drag the calculation into your Pivot table. However, although the Format is set to Currency in the cube , the calculation appears as an unformatted number in the Pivot table.
Solution
When creating the calculation in the Form View, there is an Format String drop down box that allows for selecting “Currency” as the format. However, the LANGUAGE property of the calculation needs to be set for this to carry over into the Excel pivot table example described above.
To set the LANGUAGE property of the calculation, click on the Script View to show all calculations in the cube. In the Script View, the MDX can be edited for any and all calculations and additional properties can be added. These additional properties are not available via the Form View but the properties are valid MDX properties of a calculated member. One such property is the LANGUAGE property.
The LANGUAGE property accepts the LANGUAGE CODE ID as the value to be set. For example, the value for US Currency is 1033.
The following is an example of a simple calculated member with the LANGUAGE property being set:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Avg Sales Amt]
AS IIF(ISEMPTY([Measures].[SALES Count]), NULL, IIF([Measures].[SALES Count] = 0, 0,
[Measures].[AMOUNT] / [Measures].[SALES Count])),
FORMAT_STRING = "Currency", LANGUAGE=1033,
NON_EMPTY_BEHAVIOR = { [AMOUNT] },
VISIBLE = 1 ;
For more information about the LANGUAGE property, navigate to the following KB article: http://support.microsoft.com/kb/950598
1 comments:
There's a big problem with this approach if you're dealing with currencies from countries which have different ways of formatting decimals and thousands, which I blogged about here:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1863.entry
Format_String is more work to implement, but does what you want.
Post a Comment