企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1359|回复: 7

mdx issues

[复制链接]

80

主题

407

帖子

591

积分

高级会员

Rank: 4

积分
591
QQ
发表于 2014-6-29 23:01:31 | 显示全部楼层 |阅读模式
Hi fellows,

Ran into a brick wall here...

MDX below is not happy:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)},[Customer Active Status].([Period].([System Info].( StrToMember([System Parameters].[Current Reporting Period]) ) ,[Active Status Measure].[Value])=0))}

What I am trying to do here is to filter out customer dim based on Customer Active Status cube value of 0... while its straightforward, the culprit is that I am trying to replace [Period].[Element] qualifier with the current reporting period string from the System Info cube (dim [Period]. [element from System info cube] ) ... .

this part here: [Customer Active Status].([Period].([System Info].( StrToMember([System Parameters].[Current Reporting Period]) )

Error im getting:
syntax error at or near: '[System Info].(StrMember( ......



I tried to pull that period string in the actual period dim with

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, [System Info].(StrToMember("[System Parameters].[Current Reporting Period]"),[System Measures].[String]))}

and worked happily. . .

Please suggest. I think I have been looking at it for too long now

本帖子中包含更多资源

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

x
回复

使用道具 举报

87

主题

373

帖子

564

积分

高级会员

Rank: 4

积分
564
QQ
发表于 2014-6-30 00:13:01 | 显示全部楼层
You migh  want to use EXCLUDE mdx rather than go that way round
回复 支持 反对

使用道具 举报

80

主题

399

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-6-30 00:13:25 | 显示全部楼层
Darkhorse wrote:You migh  want to use EXCLUDE mdx rather than go that way round

Hi Darkhorse,

Thanks for the suggestion, however I dont see how is it going to help to be honest. In simple words I am trying to create Dynamic variable behavior of the Period dimension based on the System Info cube string, e.g CurrentReporting period 2013-09... and use this dynamic variable to filter out Customers based on their values in Customer Active Status cube for that period/value . Does it make sense?

If  I was passing this mdx in excel i would use something along this lines:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)},[Customer Active Status].([Period].['&CellReference&'],[Active Status Measure].[Value])=0))}

As you can see from the example above, im trying to create dynamic variable (CellReference) inside subset editor

Thanks
EvgenyT
回复 支持 反对

使用道具 举报

66

主题

394

帖子

543

积分

高级会员

Rank: 4

积分
543
QQ
发表于 2014-6-30 01:02:43 | 显示全部楼层
Hi you want to make things dynamic
PPeriod= q4;

Use ['| pPeriod|']

And the same for the exclude. If you use exclude it will again be easily replaced using the above solution and make the variable dynamic
回复 支持 反对

使用道具 举报

83

主题

416

帖子

588

积分

高级会员

Rank: 4

积分
588
QQ
发表于 2014-6-30 01:29:10 | 显示全部楼层
Darkhorse,

This is not in TI but in the Subset Editor.
回复 支持 反对

使用道具 举报

73

主题

409

帖子

584

积分

高级会员

Rank: 4

积分
584
QQ
发表于 2014-6-30 01:30:35 | 显示全部楼层
Giggle.

Evgeny, try something like this.

TM1Member(TM1SUBSETALL([Period]).Item([System Info].([System Parameters].[Current Reporting Period],[System Info Measure].[String])) ,0)

Given you have a System Info Measure dimension in your cube with an element 'String'.
This is not tested exactly like that as I had to change a few things around trying to fit you names.

The above can just be a dynamic subset in the period dimension called "Current Period" and elsewhere you could refer to it with something like:

tm1member(tm1subsettoset([Period], "Current Period").item(0),0)

Cheers
回复 支持 反对

使用道具 举报

76

主题

403

帖子

586

积分

高级会员

Rank: 4

积分
586
QQ
发表于 2014-6-30 01:58:23 | 显示全部楼层
Hi Gregor,

Thank you for your suggestion. Yes I was leaning toward using subset to set since the nesting was getting out of hand there      

I guess I could easily store current period under consolidation in Period dimension (and rebuild it with TI every so often) and just reference that in mdx, but I was trying to prove to myself that I can make it work         

Darkhorse - thanks for your contribution, but I think you misunderstood the problem. Passing dynamic variables in subset editor is different from TI or Active Form. There is no easy way of doing it, as far as I know.

Thanks guys,

Evgeny

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

71

主题

397

帖子

558

积分

高级会员

Rank: 4

积分
558
QQ
发表于 2014-6-30 02:53:29 | 显示全部楼层
Gregor Koch wrote:
TM1Member(TM1SUBSETALL([Period]).Item([System Info].([System Parameters].[Current Reporting Period],[System Info Measure].[String])) ,0)

Gregor, unfortunate mdx threw Failed to Compile error with syntax above, however I was able to achieve it with the following syntax:

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, [System Info].(StrToMember("[System Parameters].[Current Reporting Period]"),[System Measures].[String]))}

And Victorious at last:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)},[Customer Active Status].(tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0),[Active Status Measure].[Value])=1 )}

        

I have removed reference to [Period]. [ElementName] and replaced it as you suggested with (tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0)

I just wish there was an easier way of doing it ....

Thanks Gregor,

Evgeny

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2019-1-17 18:36 , Processed in 0.150540 second(s), 13 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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