Sql-server – Getting formatted MDX values using OPENQUERY

mdxsql server

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.

Best Answer

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.

Related Solutions

Use MDX to select Dimension-Members instead of Measure Values

You can achieve your desired results by using a crossjoin (the * notation) and putting the dimensions on the columns axis. Here's an example based upon the Adventure Works SSAS DB.

select {} on 0, 
{
[Customer].[Gender].[Gender].members
*
[Date].[Calendar Year].[Calendar Year].members 
}  on 1
from [Adventure Works]

enter image description here

MDX calculated member to band a measure

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY 
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.

Best Answer

Related Solutions

Use MDX to select Dimension-Members instead of Measure Values

MDX calculated member to band a measure

Related Question