切片命名集 [英] Slicing over a named set

问题描述

这是对此我有以下工作代码:

WITH 
SET NewIDs AS
    INTERSECT(NonEmpty([ID].Children, CROSSJOIN([Date].&[T-1], [Category].&[Old]))
        , NonEmpty([ID].Children, CROSSJOIN([Date].&[T], [Category].&[EOD]))
        )
SELECT 
NON EMPTY([Dim2].Children
    ) ON 0
,
NON EMPTY([NewIDs]
    ) ON 1
FROM [MyCube]
WHERE [Measures].[Value]

但是我想在行上设置另一个维度，而不是ID(我们称其为Dim1)

However I would like to have another dimension on rows, not the ID (let's call it Dim1)

我已经尝试了以下方法，但是它不起作用(因为我认为您无法对命名集进行切片)

I have tried the below, but it does not work (as I don't think you can slice on named sets)

WITH 
SET NewIDs AS
    INTERSECT(NonEmpty([ID].Children, CROSSJOIN([Date].&[T-1], [Category].&[Old]))
        , NonEmpty([ID].Children, CROSSJOIN([Date].&[T], [Category].&[EOD]))
        )
SELECT 
NON EMPTY([Dim2].Children
    ) ON 0
,
NON EMPTY([Dim1].Children
    ) ON 1
FROM [MyCube]
WHERE [Measures].[Value]
, [NewIDs]

一些进一步的澄清.我通常汇总Value度量，然后在列的Dim2和行的Dim1的表中查看它(这将包括 all ID).但是现在我只想拥有它，而只在[NewIDs]集上切片.

Some further clarity. I usually aggregate the Value measure, and look at it in a table of Dim2 on columns and Dim1 on rows (this will include all IDs). However now I want to just have this but sliced only on the [NewIDs] set.

使用下面的代码几乎可以解决我的问题，但是在行上我有一个不需要的列，其中每个值都是 NewIDs ，理想情况下我不希望拥有.

Using the below code almost solves my question, but on rows I have an unwanted column where every value is NewIDs ideally which I would like not to have.

WITH 
SET NewIDs AS
    INTERSECT(NonEmpty([ID].Children, CROSSJOIN([Date].&[T-1], [Category].&[Old]))
        , NonEmpty([ID].Children, CROSSJOIN([Date].&[T], [Category].&[EOD]))
        )
MEMBER [ID].[NewIDs] AS
       Aggregate([NewIDs])
SELECT 
NON EMPTY([Dim2].Children
    ) ON 0
,
NON EMPTY([ID].[NewIDs] * [Dim1].Children
    ) ON 1
FROM [MyCube]
WHERE [Measures].[Value]

推荐答案

如果在添加到WHERE子句之前进行聚合，会收到错误消息吗?

Do you get an error if you aggregate before adding to the WHERE clause?

WITH 
SET [NewIDs] AS
    INTERSECT(
          NonEmpty(
             [ID].Children, 
             ([Date].&[T-1], [Category].&[Old])
          )
        , NonEmpty(
             [ID].Children, 
             ([Date].&[T], [Category].&[EOD])
          )
        )
MEMBER [ID].[All].NewIDs AS
    AGGREGATE( [NewIDs] )
SELECT 
NON EMPTY([Dim2].Children
    ) ON 0
,
NON EMPTY([Dim1].Children
    ) ON 1
FROM [MyCube]
WHERE 
( [Measures].[Value]
, [ID].[All].NewIDs );

尝试将整个表达式插入WHERE子句中:

Try chucking the whole expression into the WHERE clause:

SELECT 
     NON EMPTY([Dim2].Children) ON 0
    ,NON EMPTY([Dim1].Children) ON 1
FROM [MyCube]
WHERE 
( [Measures].[Value]
, AGGREGATE( 
  INTERSECT(
          NonEmpty(
             [ID].Children, 
             ([Date].&[T-1], [Category].&[Old])
          )
        , NonEmpty(
             [ID].Children, 
             ([Date].&[T], [Category].&[EOD])
          )
        )
 ) 
);

这篇关于切片命名集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！