订购复杂的套装

诱惑是在标记为HERE的点处添加ORDER函数，即该行将变为:MyMonthsWithEmployeesSets * ORDER(MyEmployees, [Measures].[EmployeeRank])这样做会导致以下错误消息: 在分析字符串[EmployeeSet0]时，在多维数据集中找不到维度"[EmployeeSet0]".我相信这是因为度量EmployeeRank使用了在generate函数中创建的内联集.解决方案如果将EmployeeRank的EmployeeRank重新定义为11，则可以将该成员添加到作为Filter的第一个参数的集合中，并应用Order，因为它将在位置1到10的后面排序:WITH SET MyMonths AS TopPercent ( [Date].[Calendar].[Month].MEMBERS ,20 ,[Measures].[Reseller Sales Amount] ) SET MyEmployees AS [Employee].[Employee].[Employee].MEMBERS SET MyMonthsWithEmployeesSets AS Generate ( MyMonths ,Union ( {[Date].[Calendar].CurrentMember} ,StrToSet (" Intersect({}, {TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date]. [Calendar].CurrentMember)) as EmployeeSet" + Cstr(MyMonths.CurrentOrdinal) + "})" ) ) ) MEMBER [Employee].[Employee].[RestOfEmployees] AS Aggregate ( Except ( MyEmployees ,StrToSet ( "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths)) ) ) ) MEMBER [Measures].[EmployeeRank] AS IIF([Employee].[Employee].CurrentMember IS [Employee].[Employee].[RestOfEmployees], 11, Rank ( [Employee].[Employee].CurrentMember ,StrToSet ( "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths)) ) ) )SELECT { [Measures].[EmployeeRank] ,[Measures].[Reseller Sales Amount] } ON 0 , Order( Filter ( MyMonthsWithEmployeesSets * UNION(MyEmployees, {[Employee].[Employee].[RestOfEmployees]}) , [Measures].[EmployeeRank] >= 1 ), [Measures].[EmployeeRank] ) ON 1FROM [Adventure Works];I've been playing with a script of Chris Webb's found here: http://cwebbbi.wordpress.com/2007/06/25/advanced-ranking-and-dynamically-generated-named-sets-in-mdx/The adapted script is this:WITH SET MyMonths AS TopPercent ( [Date].[Calendar].[Month].MEMBERS ,20 ,[Measures].[Reseller Sales Amount] ) SET MyEmployees AS [Employee].[Employee].[Employee].MEMBERS SET MyMonthsWithEmployeesSets AS Generate ( MyMonths ,Union ( {[Date].[Calendar].CurrentMember} ,StrToSet (" Intersect({}, {TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date]. [Calendar].CurrentMember)) as EmployeeSet" + Cstr(MyMonths.CurrentOrdinal) + "})" ) ) ) MEMBER [Employee].[Employee].[RestOfEmployees] AS Aggregate ( Except ( MyEmployees ,StrToSet ( "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths)) ) ) ) MEMBER [Measures].[EmployeeRank] AS Rank ( [Employee].[Employee].CurrentMember ,StrToSet ( "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths)) ) ) SELECT { [Measures].[EmployeeRank] ,[Measures].[Reseller Sales Amount] } ON 0 ,Hierarchize ( Union ( Filter ( MyMonthsWithEmployeesSets * MyEmployees //<<<HERE<<<< , [Measures].[EmployeeRank] >= 1 ) ,{ MyMonthsWithEmployeesSets * [Employee].[Employee].[RestOfEmployees] } ) ) ON 1FROM [Adventure Works];How do I ORDER the output so that each set of ten employees is in order 1 to 10, with the MEMBER called RestOfEmployees always found in position 11 i.e. it should follow the member ranked 10 ?The temptation is to add the ORDER function at the point marked HERE i.e. that line will become :MyMonthsWithEmployeesSets * ORDER(MyEmployees, [Measures].[EmployeeRank])Doing that results in the following error message: The dimension '[EmployeeSet0]' was not found in the cube when the string, [EmployeeSet0], was parsed.I believe this is because the measure EmployeeRank uses the inline sets created during the generate function. 解决方案 If you redefine EmployeeRank to be 11 for RestOfEmployees, you can just add that member to the set that is the first argument to Filter, and than apply Order, as it will sort after position 1 to 10:WITH SET MyMonths AS TopPercent ( [Date].[Calendar].[Month].MEMBERS ,20 ,[Measures].[Reseller Sales Amount] ) SET MyEmployees AS [Employee].[Employee].[Employee].MEMBERS SET MyMonthsWithEmployeesSets AS Generate ( MyMonths ,Union ( {[Date].[Calendar].CurrentMember} ,StrToSet (" Intersect({}, {TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date]. [Calendar].CurrentMember)) as EmployeeSet" + Cstr(MyMonths.CurrentOrdinal) + "})" ) ) ) MEMBER [Employee].[Employee].[RestOfEmployees] AS Aggregate ( Except ( MyEmployees ,StrToSet ( "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths)) ) ) ) MEMBER [Measures].[EmployeeRank] AS IIF([Employee].[Employee].CurrentMember IS [Employee].[Employee].[RestOfEmployees], 11, Rank ( [Employee].[Employee].CurrentMember ,StrToSet ( "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths)) ) ) )SELECT { [Measures].[EmployeeRank] ,[Measures].[Reseller Sales Amount] } ON 0 , Order( Filter ( MyMonthsWithEmployeesSets * UNION(MyEmployees, {[Employee].[Employee].[RestOfEmployees]}) , [Measures].[EmployeeRank] >= 1 ), [Measures].[EmployeeRank] ) ON 1FROM [Adventure Works]; 这篇关于订购复杂的套装的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！