MDX query using ORDER and UNION functions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » MDX query using ORDER and UNION functions

MDX query using ORDER and UNION functions

Rate Topic

Display Mode

Topic Options

Author

Message

Robert Ross-381936

Posted Wednesday, January 09, 2008 2:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 25, 2012 6:54 PM
Points: 3, Visits: 147

Here is my base query, and corresponding output, that I would like to order by year num, month name, and then faculty name. Basically, I want the output to look like the following;

2007 January Faculty nnn
2007 January User nnn
2007 February Faculty nnn
2007 February User nnn
...etc.

Please note that duplicate year and month names are desired, as are the nulls in the output. The year and month matrix is defined by input date parameters which may not occur on the first of the month. They still want the data aggregated on year/month boundaries. Additionally, retention of dupes across Faculty and User could occur and need to be retained.

My problem is that I can't seem to get the ORDER function working in conjunction with the UNION function. I tried wrapping the UNION function with the ORDER function, and also embedding the ORDER function within the UNION function. Both scenarios give me errors.

Is there something simple that I have overlooked?

----- base query ------
SELECT { [Measures].[Fact Email Count] } ON COLUMNS,
union ( {([Dim Date].[year num].[year num], [Dim Date].[Month Name].[Month Name],
[Dim Domain Course User].[Faculty Name].&[User])},
{([Dim Date].[year num].[year num], [Dim Date].[Month Name].[Month Name],
[Dim Domain Course User].[Faculty Name].&[Faculty])} , all )

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Dim Domain Course User].[Domain Name].[dom1] } ) ON COLUMNS
FROM ( SELECT ( {[Dim Date].[20070120]:[Dim Date].[20070515]} ) ON COLUMNS
FROM [ANGEL74DW]))
WHERE ( IIF( STRTOSET("dom1", CONSTRAINED).Count = 1, STRTOSET("dom1", CONSTRAINED),
[Dim Domain Course User].[Domain Name].currentmember )
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;

----- current output -----
Fact Email Count
2007 January User 1
2007 February User (null)
2007 March User (null)
2007 April User 2
2007 May User 44
2007 January Faculty 1
2007 February Faculty 1
2007 March Faculty 3
2007 April Faculty (null)
2007 May Faculty 41

Here is the query, and the error message when wrapping the UNION with the ORDER function;

SELECT { [Measures].[Fact Email Count] } ON COLUMNS,
order (
union ( {([Dim Date].[year num].[year num], [Dim Date].[Month Name].[Month Name],
[Dim Domain Course User].[Faculty Name].&[User])},
{([Dim Date].[year num].[year num], [Dim Date].[Month Name].[Month Name],
[Dim Domain Course User].[Faculty Name].&[Faculty])} , all )
, asc)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Dim Domain Course User].[Domain Name].[dom1] } ) ON COLUMNS
FROM ( SELECT ( {[Dim Date].[20070120]:[Dim Date].[20070515]} ) ON COLUMNS
FROM [ANGEL74DW]))
WHERE ( IIF( STRTOSET("dom1", CONSTRAINED).Count = 1, STRTOSET("dom1", CONSTRAINED),
[Dim Domain Course User].[Domain Name].currentmember )
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;

Query (7, 5) An expression was expected for the function argument and no expression was detected.

According to BOL, the UNION returns a set, so why doesn't this work?

Post #440910

Robert Ross-381936

Posted Wednesday, January 09, 2008 3:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 25, 2012 6:54 PM
Points: 3, Visits: 147

I got it figured out. Here is the updated query and the results. Thanks for looking.

SELECT { [Measures].[Fact Email Count] } ON COLUMNS,
order (
union ( {([Dim Date].[year num].[year num], [Dim Date].[Month Name].[Month Name],
[Dim Domain Course User].[Faculty Name].&[User])},
{([Dim Date].[year num].[year num], [Dim Date].[Month Name].[Month Name],
[Dim Domain Course User].[Faculty Name].&[Faculty])} , all ),
settostr({([Dim Date].[year num], [Dim Date].[Month Name],
[Dim Domain Course User].[Faculty Name])}), asc)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Dim Domain Course User].[Domain Name].[dom1] } ) ON COLUMNS
FROM ( SELECT ( {[Dim Date].[20070120]:[Dim Date].[20070515]} ) ON COLUMNS
FROM [ANGEL74DW]))
WHERE ( IIF( STRTOSET("dom1", CONSTRAINED).Count = 1, STRTOSET("dom1", CONSTRAINED),
[Dim Domain Course User].[Domain Name].currentmember )
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;

Fact Email Count
2007 January Faculty 1
2007 January User 1
2007 February Faculty 1
2007 February User (null)
2007 March Faculty 3
2007 March User (null)
2007 April Faculty (null)
2007 April User 2
2007 May Faculty 41
2007 May User 44

Post #440921

« Prev Topic | Next Topic »

Permissions