cancel
Showing results for 
Search instead for 
Did you mean: 

Error MDX when executing WBI query with different prompt but same text

Former Member
0 Kudos

Current version is BO XI 3.1 SP2 FP 2.6

Connected with SAP BI7

Identified problem :

when using same text prompt in different filters, only the first one is correctly answered, the MDX code for following ones are not correctly generated : the engine uses the right filter but applied to the object from the first filter which gives a MDX code error.

Example :

In a report :

- asking a user choice on 'document month' for operational view

- using the same answer to fill up the 'posting month' in financial view

Document month (0FISCPER) filter is filter !V000001

Posting month (FISCPLCD) filter is filter !V000002

Question text is unique : "Single Year Period ?" and should be sent to both prompts/variable.

When using the same text prompt, the MDX code generated is :

SELECT  { [Measures].[4IUX93YJGXQEHGLX0T0GPNA9Y] }  ON COLUMNS , NON EMPTY CROSSJOIN( [0FISCPER].[LEVEL01].MEMBERS, [FISCPLCD].[LEVEL01].MEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [CATS_M01A/ZGL_CATS_UTILISATION] SAP VARIABLES [!V000001] INCLUDING [FISCPLCD].[Z12010006]

Please notice that in this generated code, the filter !V000001 is applied to the FISCPLCD object, while filter !V000001 is related to document month. A MDX error is generated.

Any clue to solve this issue ?

Thank you for your help.

G.OM.

Edited by: GERALDINE OMNES on Jan 14, 2011 12:44 PM

Accepted Solutions (1)

Accepted Solutions (1)

arijit_das
Active Contributor
0 Kudos

There must be two filter objects generated in the universe corresponding to the variables. Could you please mention here the definitions of those filter objects in the universe?

Former Member
0 Kudos

Ok, I've simplified the case.

Here is the real case :

Object filter =

Based on object =

.[LEVEL01]

Object filter =

Based on object =

.[LEVEL01]

In WBI query result I take these objects :

.[LEVEL01]

.[LEVEL01]

.[4IUX93YJGXQEHGLX0T0GPNA9Y]

In query filters, my 2 filters with same text :

1.

I run the query.

Answer to question 'Single Year Period ?' = Period 06 2010

==> MDX error

A database error occured. The database error text is: Echec de l'exécution de la requête MDX SELECT { .[4IUX93YJGXQEHGLX0T0GPNA9Y] } ON COLUMNS , NON EMPTY CROSSJOIN( .[LEVEL01].MEMBERS, .[LEVEL01].MEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM SAP VARIABLES INCLUDING .[Z12010006] INCLUDING .[Z12010006] avec l'erreur Invalid MDX command with . (WIS 10901)

And you can see that the value sent to both filters is the same and come from the first filter .[Z12010006] instead of applying the value to each filter .[Z12010006] for the first one and .[Z12010006] for the second one.

Please, notice that if I change the order of this 2 filters in the WBI query, the MDX generated become :

SELECT { .[4IUX93YJGXQEHGLX0T0GPNA9Y] } ON COLUMNS , NON EMPTY CROSSJOIN( .[LEVEL01].MEMBERS, .[LEVEL01].MEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM SAP VARIABLES INCLUDING .[Z12010006] INCLUDING .[Z12010006]

Thanks for your help.

G.OM.

arijit_das
Active Contributor
0 Kudos

Hi,

I once had almost similar issue. In my case, the prompts were defined in the webi query panel. It was solved when I unchecked the option select only from List of Values in the prompt definitions.

Can you please try changing the filter definitions as below if feasible :



<FILTER KEY="[!V000002]">
	<CONDITION OPERATORCONDITION="Equal">
		<CONSTANT TECH_NAME="@Prompt('Single Year Period ?','A',,mono,free)"/>
	</CONDITION>
</FILTER>


<FILTER KEY="[!V000001]">
	<CONDITION OPERATORCONDITION="Equal">
		<CONSTANT TECH_NAME="@Prompt('Single Year Period ?','A',,mono,free)"/>
	</CONDITION>
</FILTER>

In the prompt, enter the key manually. This may not satisfy a practical requirement, but just to try if you get data or an error.

Regards

Former Member
0 Kudos

Unbelievable, it's working )

MDX generated :

[CATS_M01A/ZGL_CATS_UTILISATION]: SELECT { [Measures].[4IUX93YJGXQEHGLX0T0GPNA9Y] } ON COLUMNS , NON EMPTY CROSSJOIN( [FISCPLCD].[LEVEL01].MEMBERS, [0FISCPER].[LEVEL01].MEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [CATS_M01A/ZGL_CATS_UTILISATION] SAP VARIABLES [!V000001] INCLUDING 006.2010 [!V000002] INCLUDING 006.2010

It gives me an idea : I can keep the LOV in my first prompt, so that the user would be able to find his value in a list, and not in the second prompt so it can use the response, no need to have a list of value. I'm doing the test.

(Many thanks !)

G.OM.

Former Member
0 Kudos

My last test doesn't work.

In the first prompt, the user picks the value from the LOV ("Period 06 2010"), but exactly the same is sent to the second prompt althought this prompt is waiting for 006.2010 format.

MDX error :

A database error occured. The database error text is: Echec de l'exécution de la requête MDX SELECT { .[4IUX93YJGXQEHGLX0T0GPNA9Y] } ON COLUMNS , NON EMPTY CROSSJOIN( .[LEVEL01].MEMBERS, .[LEVEL01].MEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM SAP VARIABLES INCLUDING .[Z12010006] INCLUDING Period 06 2010 avec l'erreur Invalid MDX command with 06. (WIS 10901)

So, ideas are welcome

G.OM.

arijit_das
Active Contributor
0 Kudos

Hi,

This issue is addressed in SAP Note 1397643.

Cause of the problem: When the prompt text for multiple prompts is identical, the system will only prompt the user once and populate the value all of the prompts with the identical text.. It will use the LOV from the first query in the report.

As a solution, you have to use different text for the prompts if you must prompt the user with LOV.

Regards

Former Member
0 Kudos

Thank you "pumpactionshotgun" !

I had already found some Notes but not this one which is exactly the problem we met.

I prefer your own workaround : not including LOV but making the text more explicit with waited format.

Thanks again.

G.OM.

Answers (0)