How do I reformat a measure in a mdx query

Question

I have an MDX query running on SSAS instance. The amount that gets displayed as a currency with spaces and , in the column. eg R 1 200,10.

I do not want to change anything on the data warehouse but want to change the format in the MDX query so that the results are displayed in the column as 1200.10 (a straight numeric)

I have included the following code on top of the exiting MDX query that give the wrong format.

with
member [Measures].[Gross Amt Exc Base]  as [Measures].[AMT] , FORMAT_STRING="###0.00"

This is the code i have created:

with
member [Measures].[Gross Amt Exc Base]  as [Measures].[AMT] , FORMAT_STRING="###0.00"

SELECT NON EMPTY { [Measures].[Gross Amt Exc Base]  } ON COLUMNS, 
NON EMPTY {(StrToMember('[Tran Date].[Actual Date].&[' + Format(Now()-1,'yyyy-MM-dd') + 'T00:00:00]')
           :StrToMember('[Tran Date].[Actual Date].&[' + Format(Now()-3,'yyyy-MM-dd') + 'T00:00:00]') *
             [Dim Store].[Store Number].[Store Number].ALLMEMBERS *
             [Dim SKU].[SKU Code].[SKU Code].ALLMEMBERS *
             [Dim SKU].[Product Code].[Product Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [Dolfin Dwh FTS] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I want to see 1200.10 but get the following error message

"Executing the query ...
The 'Gross Amt Exc Base' calculated member cannot be created because a member with the same name already exists.
Run complete"

Please can some one help me.

The error message means that you ALREADY have a measure with the same name. So, just use a different name, say, `[Gross Amt Exc Base Formatted]` — Subbu, Sep 27 '19 at 12:22

How do I reformat a measure in a mdx query

0 Answers0