SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX query using ORDER and UNION functions


MDX query using ORDER and UNION functions

Author
Message
Robert Ross-381936
Robert Ross-381936
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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?
Robert Ross-381936
Robert Ross-381936
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search