SSAS的MDX的基础函数(三),及聚合函数

1.  Filter函数

Filter(Set_Expression, Logical_Expression )返回根据搜索条件对指定集进行筛选后得到的集。

Filter 函数对指定集中的每个元组计算指定的逻辑表达式。如果逻辑表达式计算结果为 true，该函数将返回由指定集中的每个元组构成的集。如果所有元组的计算结果都不为 true，则返回一个空集。

Filter 函数的工作方式与 IIf 函数类似。IIf 函数只返回两个选项中的一个，返回哪一个取决于 MDX 逻辑表达式的值；而 Filter 函数返回符合指定搜索条件的元组集。实际上，Filter 函数是对集中的每个元组执行 IIf(Logical_Expression, Set_Expression.Current, NULL)，然后返回所得到的集。

示例1:

SELECT
{ 
([Measures].[Reseller Sales Amount]), 
([Measures].[Internet Sales Amount]) 
} ON COLUMNS, 
Filter( 
{[Product].[Product].[Product].Members}, 
([Measures].[Internet Sales Amount]) > ([Measures].[Reseller Sales Amount]) 
) ON ROWS 
FROM [BF_MDX]

效果:

示例2:

SELECT
{ 
([Measures].[Reseller Sales Amount]), 
([Measures].[Internet Sales Amount]) 
} ON COLUMNS, 
Filter( 
{[Product].[Category].children*[Product].[Product].[Product].Members}, 
([Measures].[Internet Sales Amount]) > ([Measures].[Reseller Sales Amount]) AND 
( 
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR 
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories] 
) 
) ON ROWS 
FROM [BF_MDX]

效果:

   示例3:

SELECT
{ 
([Measures].[Reseller Sales Amount]), 
([Measures].[Internet Sales Amount]) 
} ON COLUMNS, 
Filter( 
{[Product].[Category].children*[Product].[Product].[Product].Members}, 
([Measures].[Internet Sales Amount]) > 
([Measures].[Reseller Sales Amount]) AND 
NOT ( 
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR 
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories] 
) 
) ON ROWS 
FROM [BF_MDX]

效果:

  

一些思考

Filter如何取代where切片

扩展思维

with member measures.year as  vbamdx!cint([Date].[Calendar Year].currentmember.properties('key'))

给最左边的mdx语句加上时间限制. 查询只大于等于2003年的数据

 2. 组合结果集函数

Union 合并,intersection交集和, Except减去第二个集合

示例1:

SELECT{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Union
(
  TopCount(
    {[Product].[Product].[Product].Members},
      5,
    ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
  ),

  TopCount(
    {[Product].[Product].[Product].Members},
      5,
      ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
    )
) 
      ON ROWS
    FROM [bf_mdx]

效果1:

  

示例2:

SELECT
 {([Measures].[Reseller Sales Amount])} ON COLUMNS,
 Intersect(
 TopCount(
 {[Product].[Product].[Product].Members},
 10,
 ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
 ),
 TopCount(
 {[Product].[Product].[Product].Members},
 10,
 ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
 )
 ) ON ROWS
FROM [BF_MDX]

效果2:

   示例3:

SELECT
 {([Measures].[Reseller Sales Amount])} ON COLUMNS,
 Except(
  TopCount(
 {[Product].[Product].[Product].Members},
 10,
 ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
 ),
 TopCount(
 {[Product].[Product].[Product].Members},
 10,
 ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
 )
 ) ON ROWS 
FROM [BF_MDX]

效果3: