3

Is there any way to convert a value in MDX to a string using its FORMAT_STRING?

I have code similar to the following:

SELECT *
FROM OPENQUERY(DWDB001AS, '
WITH MEMBER [Measures].[ROH Total Sold Gross]
    AS [Measures].[ROH Total Sold Dollars] - [Measures].[ROH Total Sold Cost]
        ,FORMAT_STRING=''Currency''
SELECT {
    [Measures].[ROH Total Sold Gross]
    } ON COLUMNS
FROM [ServiceDept]
')

I am trying to get it to return formatted strings, instead of numeric values. The CSTR function doesn't seem to take the format string into account.

Converting them in SQL isn't an option here, because the rows have different format strings. It might be possible to convert them based on the titles of the rows, but I'd prefer to have SSAS do the work for me.

2

I ended up solving this using the Format function, similar to the following:

MEMBER [RO Count] AS Format([Measures].[ROH Count], "General Number")

I'll keep the question open for a bit, in case anyone has a cleaner answer. I would have liked to pull the format string in from the calculated members, instead of hardcoding it, but I couldn't figure out how.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.