I have an MDX query that is returning one column of values. Row-wise, I am doing a cross-join to break out a set of four elements of one dimension attribute by two elements of a second dimension attribute.
Code:
SELECT
[Measures].[Fact Compt Importance Categorical Count] ON COLUMNS,
CROSSJOIN
(
{[Dim Model Elements].[Element No].[9], [Dim Model Elements].[Element No].[10]},
{[Dim Importance Level Bands].[Importance Bands].[All].Children}
) ON ROWS
FROM EstimationEngineCube
So I get a result set along the following lines:
9 1 24
9 2 34
9 3 4
9 4 4
10 1 6
10 2 20
10 3 9
10 4 31
I now need to add an additional cross-join, where for a new set of elements, I cycle through all the elements already included in the query so far. However, the challenge I have is that the elements are a set that spans different attributes of the same dimension. I have tried to place all these elements into a named set, and then cross-join with that:
Code:
WITH SET [ContextSet] AS
{
(
[Dim Job SOC Context Aggregated].[s3context3008].[4],
[Dim Job SOC Context Aggregated].[s3context3014].[3],
[Dim Job SOC Context Aggregated].[s3context3017].[3]
)
}
SELECT
[Measures].[Fact Compt Importance Categorical Count] ON COLUMNS,
CROSSJOIN
(
[ContextSet],
CROSSJOIN
(
{[Dim Model Elements].[Element No].[9],
[Dim Model Elements].[Element No].[10]},
{[Dim Importance Level Bands].[Importance Bands].[All].Children}
)
)
ON ROWS
FROM EstimationEngineCube
However, this does not work – the set is just treated as what effectively looks like a slicer or where condition:
4 3 9 1 93
4 3 9 2 136
4 3 9 3 29
4 3 9 4 2
4 3 10 1 53
4 3 10 2 108
4 3 10 3 47
4 3 10 4 52
What I was hoping to get was a resultset with this structure:
3 9 1 93
3 9 2 136
3 9 3 29
3 9 4 2
3 10 1 53
3 10 2 108
3 10 3 47
3 10 4 52
4 9 1 93
4 9 2 136
4 9 3 29
4 9 4 2
4 10 1 53
4 10 2 108
4 10 3 47
4 10 4 52
As I am relatively new to MDX, I assume (hope?) that I have got some wires crossed that can easily be uncrossed - any help much appreciated...!