datazen&MDX学习笔记

1. 得到当前时间例子

with member thismonth as format(dateadd('d',-1,now()),'yyyyMM')
select 
{
[Measures].[销售成本],
[Measures].[销售金额],
[Measures].[销售利润]
}
on 0,
non empty{
[Dim Product].[SEASON KEY].children*
[Dim Product].[PRICETIER KEY].children*
[Dim Shop].[AREA].children*
[Dim Shop].[SHOP].children
} on 1
from [HxDataWarehouse]
where strtoset('([Dim Date].[Calendar].[MONTH KEY].&['+thismonth+'])')

2. DATAZEN时间参数传递。这里datazen时间的处理还是有问题，应该会有更好的方式，暂时没时间研究。

with member sstartdate as left('{{ @startdate }}',10)
member senddate as left('{{ @enddate }}',10)
member tstartdate as str(year(sstartdate))+right("0"+str(month(sstartdate)),2)+right("0"+str(day(sstartdate)),2)
member tenddate as str(year(senddate))+right("0"+str(month(senddate)),2)+right("0"+str(day(senddate)),2)
select 
{
[Measures].[销售成本],
[Measures].[销售金额],
[Measures].[销售利润]
}
on 0,
non empty{
[Dim Product].[商品属性].children*
[Dim Shop].[SHOP].children
} on 1
from [HxDataWarehouse]
where 
strtoset('([Dim Date].[DATE KEY].&['+tstartdate+']:[Dim Date].[DATE KEY].&['+tenddate+'])')

3. 同时把keycolumn和namecolumn查出来。

 

select
    [Measures].[销售金额] on 0, 
non empty{[Dim Shop].[AREA].children } DIMENSION PROPERTIES MEMBER_KEY on 1
from 
[HxDataWarehouse]

 

WITH
MEMBER [Measures].[Parent Member Name] AS
[Product].[Product Categories].CurrentMember.Properties("LEVEL_NUMBER") 

SELECT
{([Measures].[Parent Member Name])} ON COLUMNS,
{[Product].[Product Categories].AllMembers} ON ROWS
FROM [Step-by-Step];

 4. 

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

 

member [Measures].[大类库存数量] as 
sum({
[Dim Product].[大类].&[大类]
}
,[Measures].[实存数量])

 

 

 时间处理汇总：

with 
member today as format(dateadd('d',-10,now()),'yyyyMMdd')

member [Measures].[连带率] as '[Measures].[销售数量]/[Measures].[成交单数]' ,Format_String='0.00' 
member [Measures].[平均单价] as '[Measures].[销售金额]/[Measures].[销售数量]',Format_String='0' 
member [Measures].[折扣率] as '[Measures].[销售吊牌金额]/[Measures].[销售金额]' ,Format_String='0.00' 
member [Measures].[库存数量] as '[Measures].[实存数量]+[Measures].[在途数量]' ,Format_String='0.00' 
member [Measures].[库存吊牌金额] as '[Measures].[实存吊牌金额]+[Measures].[在途吊牌金额]' ,Format_String='0.00' 
member [Measures].[大类库存数量] as 
sum({
[Dim Product].[大类].&[大类]
}
,[Measures].[实存数量]+[Measures].[在途数量])
member [Measures].[大类日均销] as 
sum({
[Dim Product].[大类].&[大类]
}
,[Measures].[日均销])

MEMBER [Measures].[未达标门店]
AS
COUNT(
   FILTER(
       DESCENDANTS([Dim Shop].[SHOP].CURRENTMEMBER,[Dim Shop].[SHOP].[SHOP]),
       [Measures].[销售金额] < [Measures].[销售目标]
        )
 )

MEMBER [Measures].[80%未达标门店]
AS
COUNT(
   FILTER(
       DESCENDANTS([Dim Shop].[SHOP].CURRENTMEMBER,[Dim Shop].[SHOP].[SHOP]),
       [Measures].[销售金额] < [Measures].[销售目标]*0.8  
        )
 )

 MEMBER [Measures].[营业门店总数]
AS
COUNT(
       DESCENDANTS([Dim Shop].[SHOP].CURRENTMEMBER,[Dim Shop].[SHOP].[SHOP])
        )
MEMBER [Measures].[本月到当天销售]
AS
 Aggregate(
       PeriodsToDate(
 [Dim Date].[Calendar].[MONTH KEY],
 [Dim Date].[Calendar].CURRENTMEMBER
),
       [Measures].[销售金额]
        )
MEMBER [Measures].[本月目标]
AS
   SUM(
       [Dim Date].[Calendar].CURRENTMEMBER.PARENT,
        [Measures].[销售目标]
        )

MEMBER [Measures].[昨日销售] as
        (
ParallelPeriod(
[Dim Date].[Calendar].[DATE KEY],
1,
[Dim Date].[Calendar].CurrentMember
  ),
[Measures].[销售金额]
)

MEMBER [Measures].[本月到当天成本]
AS
 Aggregate(
       PeriodsToDate(
 [Dim Date].[Calendar].[MONTH KEY],
 [Dim Date].[Calendar].CURRENTMEMBER
),
       [Measures].[销售成本]
        )
MEMBER [Measures].[本月到当天利润]
AS
 [Measures].[本月到当天销售]-[Measures].[本月到当天成本]

MEMBER [Measures].[上月同期销售]
AS
Aggregate(
 PeriodsToDate(
 [Dim Date].[Calendar].[MONTH KEY],
 PARALLELPERIOD([Dim Date].[Calendar].[MONTH KEY],1,[Dim Date].[Calendar].CURRENTMEMBER
))
 ,[Measures].[销售金额])

 MEMBER [Measures].[上月同期成本]
AS
Aggregate(
 PeriodsToDate(
 [Dim Date].[Calendar].[MONTH KEY],
 PARALLELPERIOD([Dim Date].[Calendar].[MONTH KEY],1,[Dim Date].[Calendar].CURRENTMEMBER
))
 ,[Measures].[销售成本])

 MEMBER [Measures].[上月同期利润]
AS
[Measures].[上月同期销售]-[Measures].[上月同期成本]

select 
{
[Measures].[销售成本],
[Measures].[销售金额],
[Measures].[销售利润],
[Measures].[成交单数],
[Measures].[销售数量],
[Measures].[连带率],
[Measures].[平均单价],
[Measures].[折扣率],
[Measures].[销售目标],
[Measures].[库存数量],
[Measures].[库存吊牌金额],
[Measures].[大类库存数量],
[Measures].[大类日均销],
[Measures].[未达标门店],
[Measures].[80%未达标门店],
[Measures].[营业门店总数],
[Measures].[昨日销售],
[Measures].[本月到当天销售],
[Measures].[本月到当天成本],
[Measures].[本月到当天利润],
[Measures].[本月目标],
[Measures].[上月同期销售],
[Measures].[上月同期成本],
[Measures].[上月同期利润]
}
on 0
from [HxDataWarehouse]
where 
(strtoset('([Dim Date].[Calendar].[DATE KEY].&['+today+'])'),
[Dim Shop].[SHOP].&[3])

 

 

http://www.cnblogs.com/ycdx2001/p/mdx.html