MDX查询语句

2022-12-29 09:58:53

另: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 ( { [日期].[年].&[] } ) ON COLUMNS

            FROM (

                   SELECT ( { [货品].[季节].&[春秋] } ) ON COLUMNS

                   FROM [TestCube]

                 )

          )

      )

 WHERE ( [货品].[季节].&[春秋], [日期].[年].&[], [渠道].[经营方式].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 [日期].[年月日期].[年].&[].&[06月].&[]

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].&[]:[日期].[日期ID].&[]),

[Measures].[终端销售数量])

MEMBER Measures.[去年本月销量] AS

sum(

([日期].[日期ID].&[]:[日期].[日期ID].&[]),

[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].&[]} 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].&[]

with

member  [Measures].[年] AS

MEMBERTOSTR([Dim 日期].[年].currentmember)

select

{ [Measures].[年]} on 0

from [RFMDW]

where [Dim 日期].[日期ID].&[]

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 {[日期].[年].&[]}

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 [日期].[年月日].[年].&[].&[01月]

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

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

select

{[Measures].[终端销售数量]} on ,

non empty lastperiods(,[货品].[上市日期].&[]) on

from [RegentCube]

码农公寓

相关文章