Click here to monitor SSC
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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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   Wink

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   Wink
, 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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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   Wink,
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