这几个函数用来动态生成成员, 元素, 集合或者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]