Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Question Unanswered: How to include a set of different attributes from same dimension in MDX crossjoin

    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...!
    Last edited by SJF; 04-20-12 at 06:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •