cancel
Showing results for 
Search instead for 
Did you mean: 

SAP BI Query with conditions -- Integration with BO

Former Member
0 Kudos

Hi,

I have designed a Query with conditions(Top N) on Plant & Material dimensions respectively w r t a measure(keyfigure) in the

BEx Query Designer. The output when we have run is the one we desired with conditions applied.

We have built a BO Universe on top of this query. Then, we have built a query using Query as a Web Service(BO) on

top of the built universe such that having the same fields as the original query in Query designer(SAP BI). The query is previewed,

surprisingly the Output has the data without applying the underlying CONDITIONS.

I like to know is that the issue is with BI-BO Integration or something else, or is there any other way to sort it out

I request all of you to let me know the solution for this issue ASAP.*

Thanks in Advance!

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

What if I don't want to prompt the user and know we need the Top 20 ranked elements?

We tried the syntax in this thread and we keep getting the same syntax errors illustrated by flying. but we know the user shouldn't be prompted.

Can someone assist us on the correct syntax for that?

Former Member
0 Kudos

Hi,

This is an OLAP BAPI limitation: MDX is only avialable at this stage through SAP OLAP BAPI.

So we used OLAP BAPI driver to access SAP BW with OLAP universes and OLAP BAPI does not take into account conditions.

The issue is not related to BO products.

Regards,

Didier

Former Member
0 Kudos

Hi Didier Mazoue,

If want to achive TOP 10/Buttom 10 condition in OLAP universe based on SAP BI,THen is there any way to achhive this?

Thanks,

piyush

Former Member
0 Kudos

Hi,

You can wirte a predefined filter in the universe such as following:

<FILTER EXPRESSION="Rank([0D_MATERIAL].CurrentMember, Order([0D_MATERIAL].CurrentMember.Level.Members, [Measures].[0D_NETVAL_S], BDESC))"><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top n','N',,mono,free)"/></CONDITION></FILTER>

Regards,

Didier

Former Member
0 Kudos

HI Didier Mazoue,

tabk of sum po value top 10 datas for vender ;

I use your predefined filter in the universe such as following:

<FILTER EXPRESSION="Rank([0VENDOR].[LEVEL01], Order([Measures].[D7G51I90GYJ8POGFSYVFPX24F], BDESC))"><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top n','N',,mono,free)"/></CONDITION></CONDITION></FILTER>

runat webi error:

A database error occured. The database error text is: MDX Search WITH MEMBER [Measures].[5AE76BAB-DEC8-4C84-A9,18,9F,2D,B1,32,FA,25] AS ' Rank([0VENDOR].[LEVEL01], Order([Measures].[D7G51I90GYJ8POGFSYVFPX24F], BDESC)) ' SELECT { [Measures].[D7G51I90GYJ8POGFSYVFPX24F] } ON COLUMNS , NON EMPTY FILTER( CROSSJOIN( CROSSJOIN( { [0PLANT].[3000] } , { [0PURCH_ORG].[3000] } ), [0VENDOR].[LEVEL01].MEMBERS ), [Measures].[5AE76BAB-DEC8-4C84-A9,18,9F,2D,B1,32,FA,25] <= 2) DIMENSION PROPERTIES [0PLANT].[20PLANT], [0PURCH_ORG].[20PURCH_ORG] ON ROWS FROM [ZPUR_012/ZPUR_S012_TEST] runat error,error unknow. (WIS 10901)

what about the error?

Thank you.

Regards,

Howard.

Former Member
0 Kudos

Hi,

The correct syntax for your MDX expression is:

<FILTER EXPRESSION="Rank([0VENDOR].CurrentMember, Order([0VENDOR].CurrentMember.Level.Members, [Measures].[D7G51I90GYJ8POGFSYVFPX24F], BDESC))"><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top n','N',,mono,free)"/></CONDITION></FILTER>

Regards

Didier

Former Member
0 Kudos

HI Didier Mazoue ,

Thank you very much. That is ok.

<FILTER EXPRESSION="Rank(0VENDOR.CurrentMember, Order(0VENDOR.CurrentMember.Level.Members, Measures.D7G51I90GYJ8POGFSYVFPX24F, BDESC))"><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top n','N',,mono,free)"/></CONDITION></FILTER>

Universe:

Filter must to choosed: Use filter as mandatory in query and Apply on Universe or Apply on Class

Thank you.

Regards,

Howard.

Former Member
0 Kudos

HI Didier Mazoue,

I used

<FILTER EXPRESSION="Rank([0VENDOR].CurrentMember, Order([0VENDOR].CurrentMember.Level.Members, [Measures].[D7G51I90GYJ8POGFSYVFPX24F], BDESC))"><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top n','N',,mono,free)"/></CONDITION></FILTER>

on universer,

But webi Result objects had: *Plant, Pur.Org.,Vendor,Po vlaue * tour objects, runat value that is ok.

but I need Year,Period,Plant, Pur.Org.,Vendor,Po vlaue six objects, ,runat error:

" A database error occured. The database error text is: MDX search WITH MEMBER [Measures].[F38A9BC-C87E-

4AA8-82,3B,6C,6E,34,A2,83,47] AS ' Rank([0VENDOR].CurrentMember, Order

([0VENDOR].CurrentMember.Level.Members, [Measures].[D7G51I90GYJ8POGFSYVFPX24F], BDESC)) ' SELECT {

[Measures].[D7G51I90GYJ8POGFSYVFPX24F], [Measures].[D7G51I90GYK0EAS74IB1SCGE7], [Measures].

[D7G51I90GYKS2X3YG1QNURUNZ] } ON COLUMNS , NON EMPTY FILTER( FILTER( CROSSJOIN( CROSSJOIN( CROSSJOIN(

CROSSJOIN( { [0PLANT].[3000] } , [0VENDOR].[LEVEL01].MEMBERS ), { [0PURCH_ORG].[3000] } ),

[0FISCPER3].[LEVEL01].MEMBERS ), [ZFISCYEAR].[LEVEL01].MEMBERS ), [Measures].

[D7G51I90GYJ8POGFSYVFPX24F] > 0), [Measures].[F38A9BC-C87E-4AA8-82,3B,6C,6E,34,A2,83,47] <= 10)

DIMENSION PROPERTIES [0FISCPER3].[20FISCPER3], [0PLANT].[20PLANT], [0PURCH_ORG].[20PURCH_ORG],

[0VENDOR].[20VENDOR], [ZFISCYEAR].[2ZFISCYEAR] ON ROWS FROM [ZPUR_012/ZPUR_S012_TEST] SAP VARIABLES

[ZYEAR] INCLUDING [ZFISCYEAR].[2009] [!V000002] INCLUDING [0FISCPER3].[009] : [0FISCPER3].[009] runat error

,unknowable. (WIS 10901) .. "

How about the error? Thank you.

Regards

Howard.

Former Member
0 Kudos

Hi,

It is hard to guess where the error is coming from.

I suggest that you activate WebI and ODA traces (see attached file).

Then copy the generated MDX and run it in MDXTEST transaction.

Can you also run the query without the ranking measure.

Last are you sure that the measure definition has not changed and the unique id is the same: D7G51I90GYJ8POGFSYVFPX24F

Regards,

Didier

Former Member
0 Kudos

Hi Didier Mazoue ,

Thank you. I will try to tomorrow.

Regards,

Howard.

Former Member
0 Kudos

HI Didier Mazoue ,

I runat BO_traces.reg, MDX_traces.reg , and copy BO_trace.ini top c:\, and create temp, BOLOG folder.

The issue figured out.

Thank you.

Regards,

Howard.

Edited by: flying on Mar 1, 2010 1:55 PM

0 Kudos

Hi Didier,

i need to rank based on 2 charestrics brand, country and the measure is the NetSales. i want to 10 countrys by brand for the measure netsales. what is the MDX filter criteria for this senario.