MDX syntax for condition operator using @Prompt function

[Former Member]

I am trying to get the correct MDX syntax for a condition operator using the @Prompt function in Designer.

Source is SAP BW Query and i am using XI 3.1 SP2 Designer.

Here's the MDX code that is for the @Prompt function to enter the operator condition in free form text as Equal or NotEqual when prompted. I get a parsing error at OPERATORCONDITION saying the MDX is not valid.

<FILTER KEY="@Select(Transaction Number\L01 Transaction Number)">

<CONDITION OPERATORCONDITION="@Prompt('Select Operator', 'A',,,)">

<CONSTANT CAPTION= "@Prompt('Quote Number ?','A','Transaction Number\L01 Transaction Number',mono,constrained)"> </CONSTANT>

</CONDITION>

</FILTER>

If i change the syntax to actual condition text to "Equal" or "NotEqual" instead of @Prompt it works fine.

<FILTER KEY="@Select(Transaction Number\L01 Transaction Number)">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION= "@Prompt('Quote Number ?','A','Transaction Number\L01 Transaction Number',mono,constrained)"> </CONSTANT>

</CONDITION>

</FILTER>

BTW, i do have Didier's OLAP universe best practices documents and i am trying to simulate his example of using Dynamic operators against SAP BW data sources.

Any one has any thoughts or suggestions?

Regards,

P.G

[Former Member]

Hi,

The syntax contains a small error.

I also recommand to force user to select among a list of operators rather than to let theme ntering wrong values.

Here is the syntax:

<FILTER KEY="@Select(Transaction Number\L01 Transaction Number)">
<CONDITION OPERATORCONDITION="@Prompt('Select Operator', 'A:N',{'Equal':'Equal','Not Equal':'NotEqual'},mono,primary_key)">
<CONSTANT CAPTION= "@Prompt('Quote Number ?','A','Transaction Number\L01 Transaction Number',mono,constrained)"> </CONSTANT> 
</CONDITION>
</FILTER>

Please pay attention, the Designer parser might not validate the expression. So do not take the parsing message into account and use the universe with WebI: Parser bugs will be fixed in XI 3.1 SP3.

Regards

Didier

[Former Member]

Hi Didier,

I agree that i should restrict the prompt options rather than free form text, but i wanted to make sure i was getting the syntax correctly.

As you suggested i put in the updated syntax and i ignored the MDX error message, but the object is showing up as hidden in the universe (in italics). I am assuming it is because the MDX parser is throwing the error.

BTW, is SP3 released or do you happen to know when it will be released?

Thanks,

P.G

[Former Member]

I had to uncheck the "Use filter as mandatory in the query" option to enable the object in the universe. Although the parser was throwing an error, i was able to export the universe and got the prompt for the condition operator in Webi.

Didier - Thanks for providing the correct syntax. It would really help everyone if the Designer documentation is also updated with these nice tips and tricks for getting the correct MDX syntax for such basic requests.

[Former Member]

Hi I want to give one operator as default in this list

Like my query is:

<OPTIONAL><FILTER KEY="[ZIMATLM]"><CONDITION OPERATORCONDITION="@Prompt('Select Material Operator', 'A:N',{'Equal':'Equal','Not Equal':'NotEqual','Less':'Less','Greater':'Greater','GreaterOrEqual':'GreaterOrEqual','LessOrEqual':'LessOrEqual','InList':'InList'},mono,primary_key)"><CONDITION OPERATORCONDITION="In List"><CONSTANT TECH_NAME="@Prompt('Select Material','A','Material\Material',multi,free)"/>

</CONDITION></FILTER></OPTIONAL>

which appears as a list of given operators in the Prompts but i want to give one operator as a default one then what should be the Syntax for that.

[Former Member]

Hi,

Try this and set "Equal" as default operator for example:

<OPTIONAL><FILTER KEY="[ZIMATLM]">
<CONDITION OPERATORCONDITION="@Prompt('Select Operator', 'A:A',{'Equal':'Equal','Not Equal':'NotEqual','Less':'Less','Greater':'Greater','Greater Or Equal':'GreaterOrEqual','Less Or Equal':'LessOrEqual','In List':'InList'},mono,primary_key,,{'Equal'})">
<CONSTANT TECH_NAME="@Prompt('Select Material','A','Material\Material',multi,primary_key)"/>
</CONDITION></FILTER></OPTIONAL>

Pay attention that you have set "multi" for the Materiula selection and it could be incompatible with the operator selected by the user: multi works only for Inlist and NotInList.

Didier

[Former Member]

This condition on Parse is showing Error UNV0023.

and also Can we give (=,>=,<=) signs in place of Text (Equal,Greater or Equal,Less Or Equal)

[Former Member]

Hi,

Don't pay attention to the check integrity error messages; We are aware of unstability of these feature and we have identiified several issues.

The check integrity/parse will be fixed in BOE XI 3.1 SP3 coming soon.

By the way you can do what you are requiring. Try this, it works:

<OPTIONAL><FILTER KEY="[ZIMATLM]">
<CONDITION OPERATORCONDITION="@Prompt('Select Operator', 'A:A',{'=':'Equal','<>':'NotEqual','<':'Less','>':'Greater','>=':'GreaterOrEqual','<=':'LessOrEqual','In':'InList'},mono,primary_key,,{'=':'Equal'})">
<CONSTANT TECH_NAME="@Prompt('Select Material','A','Material\Material',multi,primary_key)"/>
</CONDITION></FILTER></OPTIONAL>

Please, check also that the object used in the FILTER KEY and the reference to the class/object in the second prompt are correct.

Regards,

Didier

[Former Member]

Thanks for the reply Didier....

Its working Now

[Former Member]

Hi didier,

I am back again with a query.I gave one default selection in my MDX command and when we execute the report with the same selected Operator i.e 'InList' then it shows error.But again when i select the same Inlist from the list of Values report runs fine for me. For reference please check the query.

<OPTIONAL><FILTER KEY="[ZIMATLM]"><CONDITION OPERATORCONDITION="@Prompt('Material Operator', 'A:N',{'InList []':'InList','Equal =':'Equal','Not Equal <>':'NotEqual','Less <':'Less','Greater >':'Greater','GreaterOrEqual >=':'GreaterOrEqual','LessOrEqual <=':'LessOrEqual'},mono,primary_key,,{'InList []'})"><CONSTANT NAME="@Prompt('Material','A',,multi,free)"/>
</CONDITION></FILTER></OPTIONAL>

Edited by: snsbok on Nov 8, 2010 1:49 PM

[Former Member]

Hi,

You are using index aware in the @Prompt with static values.

So you have correctly defined the parameter primary_key but the default value must contain both caption and key instead of caption only.

Didier