January 9, 2008 at 2:23 pm
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
2007JanuaryUser1
2007FebruaryUser(null)
2007MarchUser(null)
2007AprilUser2
2007MayUser44
2007JanuaryFaculty1
2007FebruaryFaculty1
2007MarchFaculty3
2007AprilFaculty(null)
2007MayFaculty41
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?
January 9, 2008 at 3:03 pm
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
2007JanuaryFaculty1
2007JanuaryUser1
2007FebruaryFaculty1
2007FebruaryUser (null)
2007MarchFaculty3
2007MarchUser(null)
2007AprilFaculty(null)
2007AprilUser2
2007MayFaculty41
2007MayUser44
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply