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