[SAP BI]--Doing the Top XX in the Universe.

[kalyanswarna]

Hi,

I need a solution to Get the Top Customers from all of them in BO.

I have to give a prompt to the user to choose Top XX then depands on this prompt i have to bring all the chosen top Customers based on the Chosen Measure.#

Ex; Top Customer depands on Sales or Dues any Measure..

Can anyone pleasse tell the MDX Script to develop this requiremnt.

Your help is greayly Appreciated!!!

Thanks,

Kalyan

[kalyanswarna · Accepted solution]

Hi,Here in the above filter( query ) suppose the user entered 10 for Top N.

Where youpass this value to filter the data set. how the above filter will restrict the data.

i didn't understand how does it work. we have to pass the prompt value some where in the report right.

Can you please tell me, where could create this filter and the level(Apply on Universe, Apply on Class).

becuase in the earlier versions i have seen that Pre defined filters in the universe were not working.

Thanks,

Kalyan

Edited by: Kalyan Swarna on Jan 19, 2009 2:17 PM

[Former Member · Accepted solution]

Hi,

You can create a classic standard or a compulsory filter.

It depends on your needs.

If you want to have "Top XX" any time a user select any object then you have to create a universe compulsory filter, if you want to have "Top XX" only when user select an object from a given class then create a class compulsory filter otherwise let the filter as standard filter.

Concerning the filter expression, it is pushed to the database and computed by the server.

Didier

[kalyanswarna]

Hi Didler, Can you please Explain me your solution Provided in the above posts.

[Former Member]

Hi,

The filter I described in my post needs to be created in the universe and then used in the quey panel in the filter pane.

You will be prompted to enter a value for the Top n and the records will be filtered based on the ranking values defined in the filter expression.

You just have to replace the Dimension and the Measure name by the one you want to use.

Didier

[kalyanswarna]

Hi,

I understand your filter query. here you mentioned a prompt for Top N but where you are passing this value to filter the records.

Can you please explain me step by step how could your code (see below) will work in the real time.

 <FILTER EXPRESSION="Rank( [MyDimension].CurrentMember,   Order([MyDimension].CurrentMember.Level.Members, [Measures].[MyMeasure], @Prompt('Top or Bottom','N',{'BDESC','BASC'},mono,constrained,,{'BDESC'}) ) ) "><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top/Bottom N','N',,mono,free)"/></CONDITION></FILTER>

[kalyanswarna]

Hi, Thanks for the solution.

in this solution where can i mention the Top XX.

Suppose if i want to display top 10 customers depands on the sales.

i hope this doesnt meet my requirement, i am thinking about TopCount(). can youhelp me if you have any idea about this function.

[Former Member]

Hi,

Im the sample I provided, you have to replace MyDimension by Customers dimension and MyMeasure by Sales measure.

Then in the filter you have to answer to the prompt question "Enter the value for Top/Bottom N" by entering 10.

Didier

[kalyanswarna]

Hi, Please help meto solve the above issue!!

[Former Member]

Hi Kaylan,

I do not know how to do this in MDX, but there is a Rank option in Web Intelligence which you could use. This would have to be done in each report though

Regards

Alan

[kalyanswarna]

Hi Alan, Thanks

i dont want to do in the report level becuase of performance killing.

Please letif anyone having solution in MDX.

Thanks

Kalyan

[Former Member]

Hi,

You can try to create a universe object containing a calculated expression in MDX.

You can also create a filter based on this calculated expression that will return the Top/Bottom n values.

You can create a prompt on this filter to select the value for "n".

Here is a sample of the calculated expression you can define:

<EXPRESSION> Rank( [MyDimension].CurrentMember,   Order([MyDimension].CurrentMember.Level.Members, [Measures].[MyMeasure], @Prompt('Top or Bottom','N',{'BDESC','BASC'},mono,constrained,,{'BDESC'}) ) ) </EXPRESSION>

Filter example:

<FILTER EXPRESSION="Rank( [MyDimension].CurrentMember,   Order([MyDimension].CurrentMember.Level.Members, [Measures].[MyMeasure], @Prompt('Top or Bottom','N',{'BDESC','BASC'},mono,constrained,,{'BDESC'}) ) ) "><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="@Prompt('Enter value for Top/Bottom N','N',,mono,free)"/></CONDITION></FILTER>

Didier