MDX:如何从两个维度合并结果？

Question

我是 MDX 的新手。现在我想结合来自 2 个不同维度的两个结果。我使用的代码如下:

With 
 MEMBER [Measures].[Student Head Count].[Program1] AS
([Program1].[Program1].&[1])
MEMBER [Measures].[Student Head Count].[Program2] AS
([Program2].[Program2].&[1]) 

SELECT
NON EMPTY
{
[Measures].[Student Head Count].[Program1] ,
[Measures].[Student Head Count].[Program2]
}
ON COLUMNS

FROM [Current Student Overview]

错误显示:

The Program1 calculated member cannot be created because its parent is at the lowest level in the Measures hierarchy.

我知道“Student Head Count”是最底层的，但是不知道怎么修改语法，如果我把“Student Head Count”换成“Test”，也不行。此外，“联盟”在我尝试时不起作用。任何人都可以帮我修改或新的 MDX 吗？

我尝试使用以下脚本使用 Crossjoin:

SELECT
 NON EMPTY
{

 [Measures].[Student Head Count]
}
ON COLUMNS,
NON EMPTY
{
NONEMPTYCROSSJOIN({[Program1].[Program1].&[1]},{[Program2].[Program2] }),
NONEMPTYCROSSJOIN({[Program1].[Program1] },{[Program2].[Program2].&[1]})  
}
on 1


FROM [Current Student Overview]

结果是:



那么如何消除或隐藏性能点中的“全部”列？

Answer 1

Measure 维度上只有一个级别。您的度量名称应该是 [Measures].[Program21]和 [Measures].[Program2] .

您还可以像这样重写查询:

SELECT
NON EMPTY
{
    ([Program1].[Program1].&[1], [Program2].[Program2].&[1])
}
ON COLUMNS
FROM [Current Student Overview]
WHERE ([Measures].[Student Head Count])

在 Mdx 中，您不能合并来自不同维度的结果，但您可以使用 CrossJoin 进行叉积。功能。

编辑:
当您的集合中只有一个成员时，您不需要使用 CrossJoin。你可以做 (member1, member2) .

如果您不想看到 All您可以使用这样的查询:

WITH
 MEMBER [Measures].[Student Head Count Program1] AS
    ([Measures].[Student Head Count], [Program1].[Program1].&[1], [Program2].[Program2].[All])
MEMBER [Measures].[Student Head Count Program2] AS
    ([Measures].[Student Head Count], [Program1].[Program1].[All], [Program2].[Program2].&[1]) 
SELECT
NON EMPTY
{
    [Measures].[Student Head Count Program1] ,
    [Measures].[Student Head Count Program2]
}
ON COLUMNS
FROM [Current Student Overview]