Show Only First N Rows in SAP Design Studio Chart/Table

[Former Member]

Hi,

Is it possible to show only first N rows of data in SAP Design Studio Charts/Cross Tab ?

Eg

Bex Query Output

Here If I mention First 2 rows, my chart looks like below

If I mention First 4 rows, my chart looks like below

Thanks

Anil

[MustafaBensan · Accepted solution]

Hi Anil,

Firstly, if your requirement is to retrieve only a certain number of rows, rather than restricting based on dimension members, you will definitely need to do this in the back-end via the BEx Query because you cannot manipulate the data client-side and update the crosstab/chart via scripting.  If your requirement is the latter, you can restrict the result set using the setFilter() method on the data source via scripting.

To return only the first N rows of data from BEx, you would need to define a Key Figure that a sort order could be based on and then apply a Top N condition on that Key Figure.

If you can provide a more specific example of your data set rather than the generic one, we could provide further guidance.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

Thanks for your reply.

Actually our requirement is, we have one Bex Query where we are using Ranking Function.

We have 15 customers, and we need to know the ranking based on some calculations.

Whenever we execute the query with different time periods, the ranking of Customers will vary, for example if I execute query for year 2014, customer 1 can be Rank 1, If I execute for year 2013, Customer 7 can be Rank 1.

From this Query I need to show only Top 5 Customers in Cross Tab/Chart.

We tried to put condition on CY Ranking in Bex to show only Top 5, but the result was giving some diff customers and also for all Time periods we are getting same Customers In Top 5.

To overcome this, we thought instead of putting condition on Bex side, we are trying to pick only first 5 rows from Bex Query and show them in Chart/Cross Tab, something like we will map only first 5 rows in Xcelcius.

We would like to know is there any functionality in Design Studio to show only first 5 rows of data.

Regards,

Anil

[MustafaBensan · Accepted solution]

Hi Anil,

Thanks for the detailed explanation.  As mentioned before, unfortunately there is no standard functionality in Design Studio to restrict the rows of data retrieved to the First N as in your scenario.  The only way to restrict is to apply a filter based on a dimension but this won't achieve your desired result.  Even if you iterated through the first 5 items of the result set in scripting using the getData() method on the data source, there is no way to then to update a crosstab or chart with this data via scripting.

I would suggest that we look further at your BEx Condition because your requirement sounds like a standard use case for conditions and should achieve the desired outcome.  To help further investigate this, can you do the following?:

1)  Provide screenshots of your BEx Condition definition;

2)  Confirm which characteristics your BEx Condition has been assigned to;

3)  Confirm your approach for selecting the time period in the query.  Are you using a BEx variable for this?

Regards,

Mustafa.

[MustafaBensan · Accepted solution]

Hi Anil,

Although I still think in this case the best approach is to investigate and correct the BEx Condition to achieve the desired result, upon further thought here's an idea that might work for doing this client side in Design Studio as follows:

1.  Install the SCN SDK Development Community Components from the online repository;

2.  Use the Collection Utility Component in your application to retrieve the top 5 customers based on the CY Ranking key figure;

3.  Build a selection string via scripting by iterating over these customers and apply the selection to the data source with the setFilter() method.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

Thanks for your reply.

We will test this and update you the result and also I will post the BEx Query screen shots soon.

Regards,

Anil

[IngoH · Accepted solution]

Hello Anil,

first the fact that in year 2014 Customer 1 is rank 1 and in 2013 another customer is rank 1 - that might not be incorrect.

Clearly what you describing is possible to do already in the Bex query using Conditions and it would be the best option as you filter the data at the backend already.

if you post screenshots from the BEx query (rows, columns, free char) and the condition details I am sure we can get this working

regards

Ingo Hilgefort, Visual BI

[Former Member]

@ Mustafa,

Thanks for your suggestion, we have discussed with our team and we are planning to go for SDK Component.

@Ingo Hilgefort,

Thanks for your reply, we have discussed with our BW team, what we have understood was we are using BEx Ranking function for both CY and LY measures, so if we put condition on one measure, other measure rank getting disturbed.

If there is any other way, please suggest us, so that we will fix this in BW Query itself instead of going for SDK.

Regards,

Anil

[MustafaBensan]

Hi Anil,

Thanks for the update.  I am wondering how you have your BEx Query defined?  You should be able to achieve your requirement for the Design Studio application by defining your BEx query as follows:

1)  Create the CY Ranking and LY Ranking key figures by applying the Rank function to the CY Amt and LY Amt key figures respectively;

2) Apply a Top 5 Condition only to the CY Ranking key figure.

Can you clarify further?

Regards,

Mustafa.

[ksehjpal]

HI ANil

Will you able to figure out the solution to it.
As my requirement is similar to you. where i am looking for top ten customer to display the data.

i know in case of webi we can use Ranking but here its no as such option. though we have option of filter on measure where we can select and give the number of measures but that will work only at the run time of the dashboard.

Regards

karan     

[MustafaBensan]

Hi Karan,

This is a closed thread which has a solution provided.  If this solution does not address your issue you should post a new question specifically describing your requirement and the issue you need help with.

Regards,

Mustafa.

[ksehjpal]

So sorry mustafa

i forgot this point