MDX used in Universe for Prompt with a substring function (e.g. left)

[Former Member]

Hello,

I Try to solve the following Problem

I try do setup a prompt like Year (YYYY) = Year of Month (YYYYMM) where the user has to select a month. Out of his selection I want to extract the Year of the selected month.

I have the Dimension YYYYMM (e.g. 200903) ([0CALMONTH].[LEVEL01].[NAME])

I have the Dimbension YYYY (e.g. 2009) ([0CALYEAR].[LEVEL01].[NAME])

I have a Prompt on YYYYMM which works:

<FILTER KEY ="[0CALYEAR].[LEVEL01].[NAME]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTATN CAPTION = "@PROMPT('YYYYMM','A',u2018D01 TIME\YYYYMM',MONO,CONSTRAINED)" />

</CONDITION>

</FILTER>

The following Filter doesn't retrun anything and i don't know why. Does anybody has a tip form me?

<FILTER KEY ="[0CALYEAR].[LEVEL01].[NAME]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTATN CAPTION = "LEFT(@PROMPT('YYYYMM','A',u2018D01 TIME\YYYYMM',MONO,CONSTRAINED),4)" />

</CONDITION>

</FILTER>

[Former Member]

and this is latest  trace log



2009/12/10 12:07:00.322|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 580
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 581
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 582
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 583
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 584
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 585
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 586
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 587
2009/12/10 12:07:00.332|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 588
2009/12/10 12:08:00.445|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 589
2009/12/10 12:08:00.495|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 590
2009/12/10 12:08:00.505|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 591
2009/12/10 12:08:00.505|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 592
2009/12/10 12:08:00.525|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 593
2009/12/10 12:08:00.535|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 594
2009/12/10 12:08:00.545|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 595
2009/12/10 12:08:00.575|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).
.\sresmanager.cpp:1188: TraceLog message 596
2009/12/10 12:08:00.585|>>|A| | 5208|5504| |||||||||||||||assert failure: (.\sresmanager.cpp:1188). (false : Resource Manager is not initialized.).

[Former Member]

Hi Didier,

thanx al lot for you support.

This is the XML I'm using

<FILTER EXPRESSION = "IIF ([0CALYEAR].currentmember.properties("[20CALYEAR]")  =LEFT("@Prompt('test','A','D01 Zeit\Monatssequenz',Mono,Constrained)",4) ,1,0)">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT NAME = "1" />
</CONDITION>
</FILTER>

[Former Member]

Hi,

now I tried

<FILTER EXPRESSION ="IIF ( [0CALYEAR].currentmember.properties("[20CALYEAR]") = LEFT("@PROMPT('YYYYMM','N',u2018D01 TIME\YYYYMM',MONO,CONSTRAINED)",4) ,1,0)">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT NAME = "1" />

</CONDITION>

</FILTER>

Now I get data but the data but it is unfiltered. May be I did something wrong. For what does "[20CALYEAR]" stand for? Or, what expression do I have to use for 20CALYEAR ?

[Former Member]

Hi,

Can you provide the XML and MDX traces to see why data are unfiltered?

Didier

[Former Member]

Hello,

thanx al lot for your support!

Yes, it is a SAP BW Universe

I tried <CONSTANT NAME ....> instead of <CONSTANT CAPTION ...>.

I also tried InList instead of Equal. The result is still the same, I do not retrieve any data (No data to retrieve. Query 1)

[Former Member]

Hi,

I want to bring a correction on the Syntax I provided.

It is important to notice that @Prompt syntax is case sensitive.

LEFT function is also case sensitive.

Do not forget double-quotes in the syntax.

So please copy the following syntax as it:

<FILTER EXPRESSION="IIF ( [0CALYEAR].currentmember.properties("[20CALYEAR]") = LEFT("@Prompt('YYYYMM','N','D01 TIME\YYYYMM',Mono,Constrained)",4) ,1,0)">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT CAPTION="1" />
</CONDITION>
</FILTER>

Regards,

Didier

[former_member142702]

Hi

is this an SAP BW based universe?

Regards,

Stratos

[Former Member]

Hi,

If your LEFT() function is working fine then try this:

<FILTER KEY ="0CALYEAR.LEVEL01.NAME">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT NAME = "LEFT(@PROMPT('YYYYMM','A',u2018D01 TIME\YYYYMM',MONO,CONSTRAINED),4)" />

</CONDITION>

</FILTER>

Regards,

Rohit

[Former Member]

Hi,

You cannot enter MDX expression in the result of a filter.

The result of a filter can be either a constant or a prompt/

Moreover SAP only support numeric as result of an MDX expression. String funtions within SAP MDX must be used with caution because it does not work in any case.

So in your case I am using the CALYEAR Key attribute.

So the correct syntax is:

<FILTER EXPRESSION ="IIF ( [0CALYEAR].currentmember.properties("[20CALYEAR]") = LEFT("@PROMPT('YYYYMM','N',u2018D01 TIME\YYYYMM',MONO,CONSTRAINED)",4)  ,1,0)">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT NAME = "1" />
</CONDITION>
</FILTER>

Regards

Didier

[Former Member]

Hi,

If your LEFT() function is working fine then try this:

<FILTER KEY ="0CALYEAR.LEVEL01.NAME">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT NAME = "LEFT(@PROMPT('YYYYMM','A',u2018D01 TIME\YYYYMM',MONO,CONSTRAINED),4)" />

</CONDITION>

</FILTER>

Regards,

Rohit