InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 13:30:29 GMT20http://www.sqlservercentral.com/Forums/Topic440910-147-1.aspxI 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 Count2007 January Faculty 12007 January User 12007 February Faculty 12007 February User (null)2007 March Faculty 32007 March User (null)2007 April Faculty (null)2007 April User 22007 May Faculty 412007 May User 44Wed, 09 Jan 2008 15:03:25 GMTRobert Ross-381936http://www.sqlservercentral.com/Forums/Topic440910-147-1.aspxHere 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 nnn2007 January User nnn2007 February Faculty nnn2007 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 Count2007 January User 12007 February User (null)2007 March User (null)2007 April User 22007 May User 442007 January Faculty 12007 February Faculty 12007 March Faculty 32007 April Faculty (null)2007 May Faculty 41Here 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?Wed, 09 Jan 2008 14:23:07 GMTRobert Ross-381936