企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 458|回复: 5

How to create dynamic subset for proceeding months

[复制链接]

86

主题

396

帖子

584

积分

高级会员

Rank: 4

积分
584
QQ
发表于 2014-3-19 22:20:42 | 显示全部楼层 |阅读模式
I have a system parameter cubes named Sys_Para, which defines current month and its index.

Cube : Sys_Para
Dimension1: Sys_Para, Members: Current_Period, Current_Period_Index
Dimension2: Sys_Para_Value, Members: Value, Notes

In the cube,
['Current_Period','Value' ] = "2014-02"
['Current_Period_Index','Value' ] =51 (which is index of "2014-02" in period dimension)


Also, there is a dimension called Period, whose member is listed in order as below.
Member Index
........
2013-01 38
2013-02 39
2013-03 40
.......
2014-01 50
2014-02 51
.......

Now I wish to create a dynamic subset in dimension Preiod starting from 6 months prior to current month shown in cube Sys_Para to next 12 months.
How can I make it?

I tried some MDX function, but can't find right method. Please help. Thanks
回复

使用道具 举报

67

主题

355

帖子

520

积分

高级会员

Rank: 4

积分
520
QQ
发表于 2014-3-20 00:46:30 | 显示全部楼层
If you google (or search this forum) for "MDX Primer" you will find what is a pretty detailed explanation of how to use all the MDX functionality.

However, in your case I would just use a TI that pulls in the current date from your cube and does while loops to go back 6 months etc. Just run that TI every time you update the control cube and job done.
回复 支持 反对

使用道具 举报

72

主题

369

帖子

527

积分

高级会员

Rank: 4

积分
527
QQ
发表于 2014-3-20 01:20:05 | 显示全部楼层
Thank you for your suggetsion, Declan.

I have created an TI program as your suggestion, since this subset should only be updated every month.

But for a study purpose, how can I make it in Subset definition by MDX.

According to the article '"MDX Primer', I can set dynamic subset of current month with below code:
{  TM1FILTERBYPATTERN(
                                            {  TM1SubsetAll( [Period]) } ,
                                                [Sys_Para].(
                                                                    [Sys_Para].[Current_Period],
                                                                    [Sys_Para_Value].[Value]
                                                                  )
                                         
                                         )
}

But there is only one month ----- the current month that can be displayed.

Also, I know we can set a range by below code
{ [Period].[2014-02].lag(6): [Period].[2014-02].Lead(12)}

But I have no way to replace '2014-02' with the string value obtained from cube 'Sys_para'. I tried StrtoMemebr function, but failed.

Can you or someone show me a sample to make it in MDX?

Thank you.
回复 支持 反对

使用道具 举报

81

主题

411

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-20 01:28:12 | 显示全部楼层
Try this:

First mdx expression creates dynamic subset with current month value based on you control cube (save its as sys.CurrentPeriod):

TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, [Sys_Para].(StrToMember("[Sys_Para].[Current_Period]"),[sys_Para_Value].[SValue] ]))}

Second mdx expression references this subset in the new expression,so essential you are breaking down complex MDX syntax into 2 steps:

{ (tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0).lag(6): (tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0).Lead(12)}

Just keep in mind that if you have period dimension with consolidations e.g. months roll up to yeat etc, lag/lead function can return unexpected results. On the other hand the example I gave you above will work with flat period dimension.

Thanks

ET
回复 支持 反对

使用道具 举报

85

主题

419

帖子

604

积分

高级会员

Rank: 4

积分
604
QQ
发表于 2014-3-20 01:58:44 | 显示全部楼层
It works. Thank you very much.
回复 支持 反对

使用道具 举报

83

主题

388

帖子

565

积分

高级会员

Rank: 4

积分
565
QQ
发表于 2014-3-20 02:07:19 | 显示全部楼层
Cool, you are welcome. Sometimes MDX nesting gets out of hand, so I split it into several part  

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-7-2 16:11 , Processed in 0.198168 second(s), 17 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表