MDX查询语句

另:http://www.cnblogs.com/biwork/p/3437959.html

1.排名+排序+量值过滤：

WITH
member [Measures].[排名] AS
IIF (ISEMPTY ([终端销售数量]), NULL ,
RANK ([货品].[品牌]. CurrentMember,
ORDER (AddCalculatedMembers([货品].[品牌].CurrentMember.Siblings),
[终端销售数量], DESC ))
)

SELECT {[Measures].[终端销售数量],[Measures].[排名]} ON 0,
non empty{ 
    order(
        FILTER([货品].[品牌].children,
        [Measures].[排名]<5 )
    ,[Measures].[排名]
    ,asc)
} ON 1
FROM  [TestCube]

	终端销售数量	排名
ABC	1425179	1
皮卡	261450	2
Avent	14656	3
汤美	10099	4

如果要显示多列属性，直接在后面order() * [货品].[年份].children

2.多条件查询,使用子查询，提高效率

 SELECT 
     NON EMPTY { [Measures].[终端销售数量] } ON COLUMNS, 
     NON EMPTY { ([品牌].[品牌名称].[品牌名称].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
 FROM ( 
     SELECT ( { [渠道].[经营方式].&[自营], [渠道].[经营方式].&[专柜] } ) ON COLUMNS 
     FROM ( 
            SELECT ( { [日期].[年].&[2013] } ) ON COLUMNS 
            FROM ( 
                   SELECT ( { [货品].[季节].&[春秋] } ) ON COLUMNS 
                   FROM [TestCube]
                 )
          )
      ) 
 WHERE ( [货品].[季节].&[春秋], [日期].[年].&[2013], [渠道].[经营方式].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

以上黄色部分为查询条件

3.维度用来计算成员中

with 
member  [Measures].[ABC] AS
--IIF([Dim Date].[Year].currentmember.PROPERTIES("caption")="2013", 5,  6 )
[Dim Date].[Year].currentmember.PROPERTIES("caption")/2

select 
{[Measures].[订购数量], [Measures].[ABC]} on 0,
non empty{
[Dim Date].[Year].children
} on 1 
from [TSCube]

4.vb中函数在MDX里的使用

with 
member  [Measures].[ABC] AS
--获取属性caption,name
--IIF([Dim Date].[Year].currentmember.PROPERTIES("caption")="2013", 5,  6 )

--数字字符可以直接参与计算
--[Dim Date].[Year].currentmember.PROPERTIES("caption")/2

--日期转换，二个日期的间隔，cdate()函数为vb函数，mdx是弱类型语言，可支持vb函数
--Datediff("d",Now(),cdate("2013-11-22"))

--随机函数
rnd(1)*100
select 
{[Measures].[ABC]} on 0
from [TSCube]

5.Lag函数，Lead函数()

返回在成员级别中比指定成员位置靠前或靠后成员

with
set [time] as
[日期].[年月日].[年].&[2014].&[01月].&[1].lag(2)
SELECT
{time} ON 0
FROM [TestCube]

查询前7天累计销售：

with 
member [Measures].[7天内销售] as 
sum({[日期].[年月日].currentmember.lag(7):[日期].[年月日].currentmember}
,[Measures].[销售数量])

select 
{[Measures].[销售数量],[Measures].[7天内销售]} on 0
from [TestCube]
where strtomember('[日期].[年月日].[年].&[2013].&[02月].&[20]')

6.MDX中使用截取字符MID

说明：此函数为vb函数，在MDX中也可以使用，STRTOSET("")做维度之间的转换，截取字符串可能会用

with
member [Measures].[A] as
mid("abc",2,2)

select 
{[Measures].[A] } on 0
from [TestCube]

7.层级过滤，巧妙的用属性过滤

（只要专柜和自营的数据）

WITH 
MEMBER [Measures].[ParameterCaption] AS [出方渠道].[渠道层级].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [出方渠道].[渠道层级].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [出方渠道].[渠道层级].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT 
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , 
[出方渠道].[渠道层级].ALLMEMBERS ON ROWS 
FROM 
(SELECT ( [出方渠道].[渠道层级].ALLMEMBERS) ON COLUMNS 
    FROM (
        SELECT ( { [出方渠道].[经营方式].&[专柜], [出方渠道].[经营方式].&[自营] } ) ON COLUMNS 
        FROM [RegentCube]
    )
)

如果只取该层次结构里的某几级（ParameterLevel=0，1，2)

WITH 
MEMBER [Measures].[ParameterCaption] AS [渠道].[渠道层级].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [渠道].[渠道层级].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [渠道].[渠道层级].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT 
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , 

{
[渠道].[渠道层级].[All],[渠道].[渠道层级].[经营方式].ALLMEMBERS,[渠道].[渠道层级].[区域].ALLMEMBERS
}
ON ROWS 
FROM 
(SELECT ( [渠道].[渠道层级].ALLMEMBERS) ON COLUMNS 
    FROM [YeehooCube]
)

8.新增会员计数，会员计数与日期没有关联，通过会员的开卡日期属性记数当天的会员计数

(STRTOMEMBER("[会员].[开卡日期].&["+[日期].[年月日].currentmember.parent.parent.PROPERTIES("caption")
+left([日期].[年月日].currentmember.parent.PROPERTIES("caption"),2)
+right("00"+[日期].[年月日].currentmember.PROPERTIES("caption"),2)+"]")
,[Measures].[会员计数])

9.根据【去年日期】(20130612)作关联，取对应销量

with 
member [measures].[去年销售生意额] as
(strtomember("[日期].[年月日期].[年].&["+
left([日期].[去年日期].currentmember.PROPERTIES("caption"),4)
+"].&["+
right(left([日期].[去年日期].currentmember.PROPERTIES("caption"),6),2)
+"月].&["+
[日期].[去年日期].currentmember.PROPERTIES("caption")
+"]")
,[Measures].[终端销售生意额])

select
{
[measures].[去年销售生意额],[Measures].[终端销售生意额]
}on 0,
non empty{[出方渠道].[渠道].members}on 1

from [CTFCube]
where [日期].[年月日期].[年].&[2014].&[06月].&[20140612]

10.基本

with 
member [a] as 
([货品].[品牌].&[英氏],[Measures].[终端销售数量])
+
([货品].[品牌].&[汤美天地],[Measures].[终端销售数量])

select 
{[Measures].[a]} on 0,
non empty{[渠道].[渠道ID].members} on 1
from [YeehooCube]

11.变态需求记录：

下载RDL文件

11.时间段销量

with 
MEMBER Measures.[本月销量] AS
sum(
([日期].[日期ID].&[20131007]:[日期].[日期ID].&[20131008]),
[Measures].[终端销售数量])

MEMBER Measures.[去年本月销量] AS
sum(
([日期].[日期ID].&[20121007]:[日期].[日期ID].&[20121008]),
[Measures].[终端销售数量])

select 
{Measures.[本月销量],Measures.[去年本月销量]} on 0,
non empty{[会员].[会员姓名].children} on 1
from [RegentCube]

12.通过一个日期参数，取截止日期量值

with 

member  [Measures].[count] as 
STRTOSET('{[日期].[年月日].[年].&[2014].&[01月].&[1],[日期].[年月日].[年].&[2014].&[01月].&[2]}').count-1

 member  [Measures].[a] as 

( STRTOSET('{[日期].[年月日].[年].&[2014].&[01月].&[1],[日期].[年月日].[年].&[2014].&[01月].&[2]}').ITEM([Measures].[count])
,[Measures].[终端销售数量])

     SELECT NON EMPTY { [Measures].[a],[Measures].[终端销售数量] } ON COLUMNS 
     FROM ( 
     SELECT 
      STRTOSET('{[日期].[年月日].[年].&[2014].&[01月].&[1],[日期].[年月日].[年].&[2014].&[01月].&[2]}')
       ON COLUMNS 
     FROM [RegentCube]) 

     WHERE ( [日期].[年月日].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
      FONT_NAME, FONT_SIZE, FONT_FLAGS

13.查询【终端销售数量】前10-topcount

with 
set [top10Set] as 
'TopCount([渠道].[渠道].Members, 10, [终端销售数量])'

select 
{[Measures].[终端销售数量],[Measures].[渠道出货数量]} on 0,
{[top10Set]} on 1
from [RegentCube]

结果：

14.查询【终端销售数量】有值的所有数据-filter

with 
set [TestSet] as 
'filter([渠道].[渠道].children*[渠道].[渠道ID].children,not isempty([Measures].[终端销售数量]) and [Measures].[终端销售数量]>0)'

select 
{[Measures].[终端销售数量],[Measures].[渠道出货数量]} on 0,
{[TestSet]} on 1
--non empty{[渠道].[渠道].children*[渠道].[渠道ID].children} on 1
from [RegentCube]

15.当天或累计

with 
member [Measures].[累计] as
sum([Dim 日期].[日期ID].[All],[Measures].[Qty])

select 
{[Measures].[Qty],[Measures].[累计]} on 0,
{[Dim 日期].[日期ID].&[20150121]} on 1
from [RFMDW]

16.占比

WITH  
MEMBER [Measures].[占比] AS 
'([日期.年].CurrentMember, [Measures].[小票计数]) / ([日期.年].Levels(0).Members(0), [Measures].[小票计数]) ' 

SELECT  
NON EMPTY {  { { {  { AddCalculatedMembers([出方渠道.区域].[区域].Members), [出方渠道.区域].[(All)] }  } } }  }  DIMENSION PROPERTIES MEMBER_TYPE  ON ROWS , 

NonEmpty( { { {  { AddCalculatedMembers([日期.年].[年].Members), [日期.年].[(All)] }  } } } ,  { [Measures].[小票计数], [Measures].[占比]} ) 
*  { [Measures].[小票计数], [Measures].[占比]}  DIMENSION PROPERTIES MEMBER_TYPE  ON COLUMNS 

FROM [RegentCube]  CELL PROPERTIES VALUE, FORMATTED_VALUE

17.行列占比

WITH  
MEMBER [Measures].[行占比] AS '([日期.年].CurrentMember, [Measures].[小票计数]) / ([日期.年].Levels(0).Members(0), [Measures].[小票计数]) ' 

MEMBER [Measures].[列占比] AS '([出方渠道.区域].CurrentMember, [Measures].[小票计数]) / ([出方渠道.区域].Levels(0).Members(0), [Measures].[小票计数]) ' 

SELECT  NON EMPTY {  { { {  { AddCalculatedMembers([出方渠道.区域].[区域].Members), [出方渠道.区域].[(All)] }  } } }  }  DIMENSION PROPERTIES MEMBER_TYPE  ON ROWS , 

NonEmpty( { { {  { AddCalculatedMembers([日期.年].[年].Members), [日期.年].[(All)] }  } } } ,  { [Measures].[小票计数], [Measures].[行占比], [Measures].[列占比]} ) *  { [Measures].[小票计数], [Measures].[行占比], [Measures].[列占比]}  DIMENSION PROPERTIES MEMBER_TYPE  ON COLUMNS 

FROM [RegentCube]  CELL PROPERTIES VALUE, FORMATTED_VALUE

18.维度排序 (按日期倒序)

With
    Member [Measures].[订单均价] AS
    '
        ([Dim 日期].[日期ID].CurrentMember,[Measures].[生意额])/
        ([Dim 日期].[日期ID].CurrentMember,[Measures].[小票数])
    ',Format_String='0.00'
    Member [Measures].[日期ID] AS
    [Dim 日期].[日期ID].CurrentMember.Member_Caption
Select 
    NON Empty
    ORDER([Dim 日期].[日期ID].Children,[Measures].[日期ID],DESC)
    ON Rows,
    {
        ([Dim 是否首次].[是否首次].Members,[Measures].[生意额]),
        ([Dim 是否首次].[是否首次].Children,[Measures].[小票数]),
        ([Dim 是否首次].[是否首次].Children,[Measures].[订单均价])
    } ON Columns
From [RFMDW]

19.维度属性查询

with 
member  [Measures].[年] AS
[Dim 日期].[年].currentmember.PROPERTIES("caption")

select 
{ [Measures].[年]} on 0
from [RFMDW]
where [Dim 日期].[日期ID].&[20040109]

with 
member  [Measures].[年] AS
MEMBERTOSTR([Dim 日期].[年].currentmember)
select 
{ [Measures].[年]} on 0
from [RFMDW]
where [Dim 日期].[日期ID].&[20040109]

20.EXCEPT函数过滤数据权限

select {
[Measures].[终端销售数量]
} on 0,
non empty{[店铺].[区域].children*[店铺].[店铺ID].children} on 1
from (
select {EXCEPT([店铺].[店铺ID].children,[店铺].[店铺ID].&[M550002])} on 0 from [TestCube]
)

21.求同期生意额

with member 
[Measures].[同期生意额] as 
sum(([日期].[年].currentmember.PREVMEMBER),[Measures].[终端销售生意额])

select 
    non empty{[Measures].[终端销售生意额],[Measures].[同期生意额]}on 1,
    non empty{{([日期].[月].children)}} on 0
from [Retail] 
    where {[日期].[年].&[2013]}

22.Descendants函数查询层级,可实现钻取

select 
{[Measures].[终端销售数量]} on 0,
{Descendants([店铺].[区域-店铺],1)} on 1
from [retail]

select 
{[Measures].[终端销售数量]} on 0,
{Descendants([店铺].[区域-店铺].[区域].&[东北],1)} on 1
from [retail]

23.GENERATE和 SetToStr 调试

WITH  
member [Measures].[debug] as 
GENERATE( 
[店铺].[区域-店铺].currentmember,
[店铺].[区域-店铺].currentmember.UniqueName)

select 
{[Measures].[终端销售数量],[Measures].[debug]} on 0,
{Descendants([店铺].[区域-店铺].[区域].&[东北],1)} on 1
from [retail]

24.where里放Measures

SELECT 
  non empty [店铺].[区域].&[东北] * [日期].[年].Members
ON 0 
FROM [retail]
WHERE [Measures].[终端销售生意额]

25.月同比，环比计算

说明：当前时间为2015年01月，

同比为去年同月（2014年1月）的数量

环比为上月（2014年12月）的数量

with

MEMBER [Measures].[同比终端销售数量]
AS
(PARALLELPERIOD([日期].[年月日].[年],1,[日期].[年月日].CurrentMember)
 ,[Measures].[终端销售数量])
 
MEMBER [Measures].[环比终端销售数量]
AS
([日期].[年月日].CurrentMember.lag(1)
 ,[Measures].[终端销售数量])
       
select 
 {[Measures].[终端销售数量],[Measures].[同比终端销售数量],[Measures].[环比终端销售数量]} on 0,
non empty{[店铺].[区域].children} on 1
from  [Retail] 

where [日期].[年月日].[年].&[2015].&[01月]

26.LastPeriods返回某指定成员之前或之后的指定个数量成员

--以下返回20140220之前的5个成员，如果第一个参数为-5,则返回之后的5个成员

select 
{[Measures].[终端销售数量]} on 0,
non empty lastperiods(5,[货品].[上市日期].&[20140220]) on 1
from [RegentCube]

posted @ 2013-09-17 14:16 Rain520 阅读(13709) 评论(1) 编辑收藏举报

刷新页面返回顶部

学海无涯苦作舟

MDX查询语句

公告