3

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?

2

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
| 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.