on 01-14-2011 11:44 AM
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ok, I've simplified the case.
Here is the real case :
In WBI query result I take these objects :
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.
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
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.
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.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.