MDX Expression for TOPN condition in Universe

[Former Member]

Hello,

I want to replicate the TOP(N) condition from the BEx query in my universe, so that I can use this to build my QWAAS query ( to be used later in an Xcelsius model to display top 20 customers).

I got my MDX statement working using the following syntax,

Step 1:

Created a new calculated expression,

<EXPRESSION>

Rank([0DEBITOR].CurrentMember,

Order([0DEBITOR].CurrentMember.Level.Members, [Measures].[4D52K2M4THSLZN44EESUNFZGE], @Prompt('Top','N',{'BDESC'},mono,constrained,,{'BDESC'})))

</EXPRESSION>

and created a Filter for the above expression,

<FILTER EXPRESSION = "Rank( [0DEBITOR].CurrentMember,

Order([0DEBITOR].CurrentMember.Level.Members,

[Measures].[4D52K2M4THSLZN44EESUNFZGE],

@Prompt('Top','N',{'BDESC'},mono,constrained,,{'BDESC'}) ) ) ">

<CONDITION OPERATORCONDITION="LessOrEqual">

<CONSTANT CAPTION="@Prompt('Enter value for Top/Bottom N','N',,mono,free)"/></CONDITION></FILTER>

The integrity check for the Universe was ok.

But when I try to create a QWAAS query, the query times out.

I run my QWAAS query for DEBITOR, the new RANKED Expression, for a given key date.

Is there something I am missing?

Any help would be greatly appreciated.

Thanks,

Bindu.

[Former Member · Accepted solution]

Hello again,

I tested the syntax with a smaller set of data instead, the RANK returns results. So it is a performance issue.

Since I need only the top 10 customers, using the TOPCOUNT function seems to make more sense?

I got the TOPCOUNT function working in MDXTEST in SAP BI, but for some reason not able to use the same syntax in the universe.

Can somebody help me with the syntax to use the TOPCOUNT function in the Universe?

Thanks in advance,

Bindu

[Former Member · Accepted solution]

I am also interested in creating a Universe object using the TopCount MDX function.

More generally, is there a way to put objects using functions into the select clause of an MDX query. Without a XML tag surrounding the function text, the objects fails to parse. You use the <Expression> tag to allow calculated measures to parse, but this puts the object in the WITH clause of the MDX query.

Surely it would be possible to have an xml element that would allow functions in object to be placed into the select clause. Does this tag already exist and is just not documented?

Thanks

[Former Member · Accepted solution]

Hi,

The only way to define calculations in Universes is to defined them enclosed with <EXPRESSION> tags.

We support calvculated measures only.

We do not provide the ability to change the Select statement.

In MDX, if you want to create calculated measures or calculated members, it is always through the WITH MEMBER clause before the Select statement.

If you want to create a named set, it is always through the WITH SET clause before the Select statement.

We plan in the next major release to support calculated measures, calculated members and named sets.

Didier

[Former Member · Accepted solution]

Hi Didier,

Thanks for your reply. So basically for now we will not be able to use the TOPCOUNT function in the universe?

When is the next major release?

For now I am using a Webi report, using the RANK function, then using this in my XCelsius via live-office.

What is the recommended approach from BOBJ, to use QWAAS in XCelsius, or to use Webi in XCelsius? Which approach is better for performance?

Thanks,

Bindu

[Former Member · Accepted solution]

Didier,

Thanks for your prompt reply. In regards to functions (not calculated measures or members) in the select clause ...

>

> In MDX, if you want to create calculated measures or calculated members, it is always through the WITH MEMBER clause before the Select statement. 
> If you want to create a named set, it is always through the WITH SET clause before the Select statement. 

Here is an example of a function that goes in the Select clause:

 
SELECT 
TopCount({[0BUS_AREA].MEMBERS}, 5, [Measures].[4D4Y0T4MGX95BBGKJ07G80D2I]) ON ROWS, 
{[Measures].[4D4Y0T4MGX95BBGKJ07G80D2I]} ON COLUMNS 
FROM ZBE_M01/ZBE_M01_B0002 
SAP VARIABLES 
[ZS_BA_ME] INCLUDING [0BUS_AREA].[10] 
[ZS_BA_ME] INCLUDING [0BUS_AREA].[11] 
[ZS_BA_ME] INCLUDING [0BUS_AREA].[12] 

The ability to put the TopCount function into an object that is just dropped verbatim into the select clause would be incredibly useful (i.e. a <SELECT> tag). It seems like it might not be all that hard to implement, but I have been on the other side of the fence and know how hard the easy stuff can be. Do you think there is any interest in making a feature like this happen sooner rather than later?

Thanks again,

Henry

[Former Member · Accepted solution]

Hi Bindhu ,

Am trying to do the same like creating the WEBI report and connecting through xcelsius. but when am integrating webi report in xcelsius am getting unknown error. Can u help me

Am trying to do top 5 materials by state

[Former Member · Accepted solution]

Hi,

We plan to support calculated measures, calculated members and named sets in BOE XI 4.0.

So it would be possible to write TOP n in named sets and reuse named sets in queries.

By the way there is workaround to address TOP n in BOE XI 3.x.

You have to write a predefined filter that contains calculated measure computing the ranking for a given dimension and a given measure such as following.

Example - Rank company code over Open orders net value:

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

In this sample the user can choose the top n value. You can also replace BDESC by BASC to have the bottom n, you could also let the user to choose from TOP or BOTTOM using a prompt.

Regards

Didier