鸟食轩

#1楼    回复  引用    

这样的结果是对的
With
Member [DATE].[CALENDAR].[cm0] As '0'
Member [DATE].[CALENDAR].[cm1] As '1'
Member [DATE].[CALENDAR].[cm2] As '2'
Member [DATE].[CALENDAR].[cm3] As '3'
Member [DATE].[CALENDAR].[cm4] As '4'
Select
{[Measures].[Customer Count]} On Columns ,
Non Empty HIERARCHIZE({
[DATE].[CALENDAR].[CALENDAR YEAR].&[2001],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2002],
[DATE].[CALENDAR].[cm0],
[DATE].[CALENDAR].[cm1]
}, POST) On Rows
From [ADVENTURE WORKS]

#2楼 [楼主]   回复  引用  查看    

@June
给Hierarchize函数加上POST参数后，查询结果中的成员个数对了，不过又有另外一个问题。这个POST参数的效果在现在这个语句中，好像没有生效的说：
查询结果为： 
             Customer Count
CY 2001 1,013
CY 2002 2,677
CY 2003 9,309
CY 2004 11,377

而根据对POST的描述：If POST is specified, the Hierarchize function sorts the members in a level using a post-natural order. 上面那个查询的结果似乎应该是： 
             Customer Count
CY 2004 11,377
CY 2003 9,309
CY 2002 2,677
CY 2001 1,013

#3楼    回复  引用    

看来不加post参数的时候确实是个bug啊，reference says:
"If POST is not specified, the function sorts members in a level in their natural order. Their natural order is the default ordering of the members along the hierarchy when no other sort conditions are specified. Child members immediately follow their parent members.
"
既然natural order的时候child members follow他们的parent, post-natural order应该是相反呢？即parent follow child.
至于2001与2004谁在前谁在后，如果要描述其先后顺序，其意思我觉得至少得用ascending/descending之类的词来指定参数吧；而post-的意思是“后-”，从查询的效果来看应该是指定parent vs child的顺序。
e.g.
Select {[Measures].[Customer Count]} On Columns ,
HIERARCHIZE({
[DATE].[CALENDAR].[CALENDAR YEAR].&[2004],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2003],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2002],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2001],
[Date].[Calendar].[Calendar Semester].&[2001]&[2],
[Date].[Calendar].[Calendar Semester].&[2002]&[1],
[Date].[Calendar].[Calendar Semester].&[2002]&[2],
[Date].[Calendar].[Calendar Semester].&[2003]&[1],
[Date].[Calendar].[Calendar Semester].&[2003]&[2],
[Date].[Calendar].[Calendar Quarter].&[2003]&[2],
[Date].[Calendar].[Calendar Quarter].&[2004]&[3].
[Date].[Calendar].[Date].&[16]
},POST) On Rows From [ADVENTURE WORKS]

H2 CY 2001 1,013
CY 2001 1,013
H1 CY 2002 1,193
H2 CY 2002 1,484
CY 2002 2,677
Q2 CY 2003 950
H1 CY 2003 1,738
H2 CY 2003 8,178
CY 2003 9,309
CY 2004 11,377

去调post之后的结果为
CY 2001 1,013
H2 CY 2001 1,013
CY 2002 2,677
H1 CY 2002 1,193
H2 CY 2002 1,484
CY 2003 9,309
H1 CY 2003 1,738
Q2 CY 2003 950
H2 CY 2003 8,178
CY 2004 11,377

#4楼 [楼主]   回复  引用  查看    

@June
天天都在网上post来post去，俺把post想成提交前的顺序了。其实这个hierarchize还属还真该提供一个这样的功能，就可以解决memebers排序的问题了。
我希望hierarchize可以提供只按层次放置成员，而在同一层内部不进行按natural order排序的功能，而是按entered order顺序输出，就很完美了:)

#5楼    回复  引用  查看    

好文章！

#6楼    回复  引用  查看    

@birdshome
另外，要实现您的要求可以这样做：
WITH
SET [Calendar Year] AS
{
[DATE].[CALENDAR].[CALENDAR YEAR].&[2004],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2003],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2002],
[DATE].[CALENDAR].[CALENDAR YEAR].&[2001]
}
SET [Calendar Semester] AS
{
[Date].[Calendar].[Calendar Semester].&[2001]&[2],
[Date].[Calendar].[Calendar Semester].&[2002]&[2],
[Date].[Calendar].[Calendar Semester].&[2002]&[1],
[Date].[Calendar].[Calendar Semester].&[2003]&[1],
[Date].[Calendar].[Calendar Semester].&[2003]&[2]
}
Select {[Measures].[Customer Count]} On Columns ,
GENERATE(
[Calendar Year],
{
[DATE].[CALENDAR].CurrentMember,
Exists([Calendar Semester], [DATE].[CALENDAR].CurrentMember)
}
) On Rows
From [ADVENTURE WORKS]