strToMember,strToTuple, strToSet, strToValue以及一些成员属性, 以及其使用
这几个函数用来动态生成成员, 元素, 集合或者value.
例如
with member Measures.FactiTVUserInfoCountNotNull as iif( [Measures].[FactiTVUserInfoCount]<=0,0,[Measures].[FactiTVUserInfoCount]) SELECT NON EMPTY { [Measures].[FactiTVUserInfoCountNotNull] } ON COLUMNS , { [DimUser].[UserID].[UserID].ALLMEMBERS * [DimUser].[UserStatus].[UserStatus].ALLMEMBERS * [DimUser].[SourceName].[SourceName].ALLMEMBERS * [DimUser].[SourceID].[SourceID].ALLMEMBERS * [DimUser].[AgentName].[AgentName].ALLMEMBERS * ( strtomember("[CDate].[DateHierarchy].[CalendarDate].["+@StartDate+"]") :strtomember("[CDate].[DateHierarchy].[CalendarDate].["+@EndDate+"]") ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [iTVDW]
这个例子用的是AdventureWorks数据库. 这里使用传进来的参数动态生成了成员. 可以完成动态查询的功能.
还有几个成员属性. CURRENTMEMBER.MEMBER_CAPTION, CURRENTMEMBER.UNIQUENAME, CURRENTMEMBER.LEVEL.ORDINAL
WITH MEMBER [Measures].[ParameterCaption] AS [Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Product].[Product Categories].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL
我们从字面上来理解. 这三个属性是 成员标题. 唯一标识, 级别的序数.
SELECT { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS, { Descendants( [Product].[Product Categories].[All Products], [Product].[Product Categories].[Subcategory], SELF_AND_BEFORE ) } ON ROWS FROM [Step-by-Step]
这样的. 就是Member_Caption. 可以唯一找到这个member的. 就是 uniqueName. 级别就更好理解了.
这几个东西比较使用. 比如可以用作选择字典(相当于asp.net里面查询页的dropdownlist.). 比如, 我们现在来制作一个.
with member Measures._Key as [DimUser].[AgentName].currentMember.member_caption member Measures._Value as [DimUser].[AgentID].currentMember.uniqueName select {Measures._Key, Measures._Value} on 0, { ([DimUser].[AgentID].children-[DimUser].[AgentID].[All].UNKNOWNMEMBER )*[DimUser].[AgentName].children } on 1 from [itvdw]
本人在长沙, 有工作可以加我QQ4658276