主页 / dba / 问题 / 183861
Accepted
Julian Goldsmith
Asked: 2017-08-19 10:05:34 +0800 CST

在 MDX 中用列 FORMAT_STRING 覆盖行 FORMAT_STRING

  • 772

有谁知道如何用列上的格式字符串覆盖行上的格式字符串？

我有如下查询：

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]

ROH Count 行的格式正确地设置为整数，但 MTD 与 Prev 除外。MTD % 列，其格式正确为百分比。

问题出在 ROH Total Sold Gross 行，其中所有列的格式都设置为货币。

以前有人做过这样的事吗？

mdx

1 个回答

  1. Best Answer

    在做了更多研究之后，我发现SOLVE_ORDER成员属性可以SSAS使用MTD vs. Prev. MTD %成员的格式字符串。

    以下代码对我有用：

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

相关问题