Overriding a row FORMAT_STRING with a column FORMAT_STRING in MDX

mdx

Does anyone have an idea how you could override the format string on a row with a format string on a column?

I have a query like the following:

WITH
    MEMBER [Measures].[ROH Total Sold Gross]                AS [Measures].[ROH Total Sold Dollars] - [Measures].[ROH Total Sold Cost], FORMAT_STRING='Currency'

    MEMBER [ClosedDate].[Date].[MTD]                        AS AGGREGATE([ClosedDate].[Date].[2017-07-01]:[ClosedDate].[Date].[2017-07-31])
    MEMBER [ClosedDate].[Date].[PrevMTD]                    AS AGGREGATE([ClosedDate].[Date].[2017-06-01]:[ClosedDate].[Date].[2017-06-30])

    MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD]          AS [ClosedDate].[Date].[MTD] - [ClosedDate].[Date].[PrevMTD]
    MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %]        AS [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD], FORMAT_STRING='Percent'
SELECT
    {
        [ClosedDate].[Date].[MTD],
        [ClosedDate].[Date].[PrevMTD],
        [ClosedDate].[Date].[MTD vs. Prev. MTD],
        [ClosedDate].[Date].[MTD vs. Prev. MTD %]
    } ON COLUMNS,
    NON EMPTY
    {
        [Measures].[ROH Count],
        [Measures].[ROH Total Sold Gross]
    } ON ROWS
FROM
    [ServiceDept]

The ROH Count row is properly formatted as an integer, with the exception of the MTD vs. Prev. MTD % column, which is properly formatted as a percentage.

The problem is the ROH Total Sold Gross row, where all columns are formatted as currency.

Has anyone done anything like this before?

Best Answer

After doing a bit more research, I found that the SOLVE_ORDER member property can get SSAS to use the format string from the MTD vs. Prev. MTD % member.

The following code worked for me:

MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %] AS 
  [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD],
  FORMAT_STRING='Percent', SOLVE_ORDER=1
Related Question