GetMembers function does not respect sorting

[Former Member]

Hi,

I have a small script the selects 5 members and assign each of their texts to a text component. I have then made a sorting in the DS, in order to get the Top 5 based on a key figure.

My issue is that when I use the below code, I get the top 5 from the dimension sorted alphabetically, not as I sorted it in the DS.

     var arr_group=DS_TOP_5.getMembers("0COMP_CODE__ZCOMPCOTE",5);

I've tried googling and going searching here on SCN without finding the solution.

Can someone help out here?

/Kris

[Former Member · Accepted solution]

So, then I have the million dollar question...

How to I get the first 5 dimension values into my array?

regards,

Kris

[IngoH · Accepted solution]

HI Kris,

is the underlying source BW ?

if so why not using the condition in the BEx query and if that needs to be flexible - us it with a variable ?

ingo

[mike_howles4 · Accepted solution]

Because getMembers still wouldn't return the values in ranked order?

[Former Member · Accepted solution]

I am using a BW source yes.

Well, my issue is not to get the top5, but to assign them to 10 text objects (one for each dimension and one for each measure). In order to solve this, I got some help yesterday (), and now I run into the sorting issue.

/Kris

[Former Member · Accepted solution]

Hi,

there is no direct solution in current version.

you can try following workaround

// Top 5 Values

var val_1 = 0.0;var val_2 = 0.0;var val_3 = 0.0;var val_4 = 0.0;var val_5 = 0.0;
var Desc_1 = "";var Desc_2 = "";var Desc_3 = "";var Desc_4 = "";var Desc_5 = "";

var val = 0.0;var Desc = "";

var Dim_For_Top_5 = DS_M.getMembers("YOUR_DIMENSION", 1000);

Dim_For_Top_5.forEach(function(Dim_Desc, Dim_i) {
Desc = Dim_Desc.text;
val = DS_M.getData("YOUR_MEASURE",{"YOUR_DIMENSION":Dim_Desc.text}).value;


if ( val > val_1 ) {
    APPLICATION.alert(Desc + "-1-"+Convert.floatToString(val));
    val_5 = val_4;
    val_4 = val_3;
    val_3 = val_2;
    val_2 = val_1;
    val_1 = val;

    Desc_5 = Desc_4;
    Desc_4 = Desc_3;
    Desc_3 = Desc_2;
    Desc_2 = Desc_1;
    Desc_1 = Desc;       
}  else if ( val > val_2 ) {
    APPLICATION.alert(Desc + "-2-"+Convert.floatToString(val));
    val_5 = val_4;
    val_4 = val_3;
    val_3 = val_2;
    val_2 = val;
    val_1 = val_1;

    Desc_5 = Desc_4;
    Desc_4 = Desc_3;
    Desc_3 = Desc_2;
    Desc_2 = Desc;
    Desc_1 = Desc_1;       
}  else if ( val > val_3 ) {
    APPLICATION.alert(Desc + "-3-"+Convert.floatToString(val));
    val_5 = val_4;
    val_4 = val_3;
    val_3 = val;
    val_2 = val_2;
    val_1 = val_1;

    Desc_5 = Desc_4;
    Desc_4 = Desc_3;
    Desc_3 = Desc;
    Desc_2 = Desc_2;
    Desc_1 = Desc_1;       
}  else if ( val > val_4 ) {
    APPLICATION.alert(Desc + "-5-"+Convert.floatToString(val));
    val_5 = val_4;
    val_4 = val;
    val_3 = val_3;
    val_2 = val_2;
    val_1 = val_1;

    Desc_5 = Desc_4;
    Desc_4 = Desc;
    Desc_3 = Desc_3;
    Desc_2 = Desc_2;
    Desc_1 = Desc_1;       
}  else if ( val > val_5 ) {
    APPLICATION.alert(Desc + "-6-"+Convert.floatToString(val));
    val_5 = val;
    val_4 = val_4;
    val_3 = val_3;
    val_2 = val_2;
    val_1 = val_1;

    Desc_5 = Desc;
    Desc_4 = Desc_4;
    Desc_3 = Desc_3;
    Desc_2 = Desc_2;
    Desc_1 = Desc_1;       
} 

});

    TX_R1C1.setText(Desc_1);
    TX_R2C1.setText(Desc_2);
    TX_R3C1.setText(Desc_3);
    TX_R4C1.setText(Desc_4);
    TX_R5C1.setText(Desc_5);

    TX_R1C2.setText(Convert.floatToString(val_1));
    TX_R2C2.setText(Convert.floatToString(val_2));
    TX_R3C2.setText(Convert.floatToString(val_3));
    TX_R4C2.setText(Convert.floatToString(val_4));
    TX_R5C2.setText(Convert.floatToString(val_5));

[Karol-K · Accepted solution]

Hi Kris,

first question is - are you ok with use of SDK components?

If yes, I think a easy solution can be coded quickly (SDK data bound component with selection on one dimension which just counts the top members, basically a half of my component Design Studio SDK: Data Bound Leader Board (Top/Bottom Selection) which is doing this internally already). Then we can pass this information to some scripting methods which will allow you a loop.

If not, the solution from Nikhil should be a good one.

Karol

[Former Member]

Hei Mustafa,

thanks for the advice. But our customer restricts us to use SDK Extensions to avoid dependency to the SDK developer. In this case this SDK even is not supported by SAP. So for all who are in the same situation can use my template.

I had to correct some things in the coding, but now it works fine. Please ignore my posting before and use this coding as template:

var l_a_kpi_id = DS_META_QUERY_KPI.getMembers("M3MDSMT01", 0);

// Umwandlung von Member Array in formatierten String
var l_kpi_id_string = "";
l_a_kpi_id.forEach(function(element, index) {
if (element.externalKey != "#") {
// Lokale String Variable für die Sortierung zusammensetzen
l_kpi_id_string = l_kpi_id_string +
element.externalKey + "/" + element.getAttributeMember("M3MDSMT20").externalKey + ";";
}
});

// Bereinigung des letzten Semikolons
l_kpi_id_string = l_kpi_id_string.substring(0,l_kpi_id_string.length-1);

// Initialisierung der Sortierung
var l_a_outer = l_kpi_id_string.split(";");
var l_a_inner = l_a_outer;
var l_kpi_id_sorted = "";
var l_act_minimum = -1;

// Sortierlogik
l_a_outer.forEach(function(element_outer, index_outer) {
var l_search_minimum = 1000; // Sortierindex kann maximal dreistellig sein
var l_act_kpi_id = "";

l_a_inner.forEach(function(element_inner, index_inner) {
var l_a_act_kpi = element_inner.split("/"); // Schlüssel und Sortierindex splitten und in Array auflösen
var l_act_kpi_sort_index = Convert.stringToInt(l_a_act_kpi[1]);

// Das aktuelle Minimum in Abhängigkeit zum bereits gefundenen suchen
if (l_act_kpi_sort_index > l_act_minimum) {
if (l_act_kpi_sort_index < l_search_minimum) {
l_act_kpi_id = l_a_act_kpi[0] + "/" + index_inner;
l_search_minimum = l_act_kpi_sort_index;
// Handling falls der Indexwert mehrfach vergeben wird
} else if (l_act_kpi_sort_index == l_search_minimum) {
l_act_kpi_id = l_act_kpi_id + ";" + l_a_act_kpi[0] + "/" + index_inner;
}
}
});
// Das aktuelle Minimum aktualisieren
l_act_minimum = l_search_minimum;
// Nur falls was gefunden wurde, das Ergebnis aktualisieren
if (l_act_kpi_id != "") {
l_kpi_id_sorted = l_kpi_id_sorted + l_act_kpi_id + ";";
}
});

// Bereinigung des letzten Semikolons
l_kpi_id_sorted = l_kpi_id_sorted.substring(0,l_kpi_id_sorted.length - 1);

Regards

Helder Matias

[Former Member]

Thanks for the update Helder!

I will definitely use this in the future, as we are also not allowed to use SDK in our dashboards.

/Kris

[Former Member]

Hello Kris,

I have a similar requirement at a customer, and I have implemented a workaround for that.

We have added a numeric attribute to our InfoObject in BW, which is responsible for the sort order - so now we have the flexibility to change this attribute and the sorting will affect Design Studio automatically. Maybe you could use this template and adapt to your specific requirement.

var l_a_kpi_id = DS_META_QUERY_KPI.getMembers("M3MDSMT01", 0);
// Sortierlogik
// Umwandlung von Member Array in formatierten String
var l_kpi_id_string = "";
l_a_kpi_id.forEach(function(element, index) {
if (element.externalKey != "#") {
l_kpi_id_string = l_kpi_id_string + element.externalKey + "/" + element.getAttributeMember("M3MDSMT20").externalKey + ";";
}
});

// Bereinigung des letzten Semikolons
l_kpi_id_string = l_kpi_id_string.substring(0,l_kpi_id_string.length-1);

var l_a_outer = l_kpi_id_string.split(";");
var l_a_inner = l_a_outer;
var l_kpi_id_sorted = "";
var l_act_minimum_sort_index = 0;
var l_act_minimum = -1; // Sortierindex kann maximal dreistellig sein

l_a_outer.forEach(function(element_outer, index_outer) {
var l_search_minimum = 1000;
var l_act_kpi_id = "";

l_a_inner.forEach(function(element_inner, index_inner) {
var l_a_act_kpi = element_inner.split("/");
var l_act_kpi_sort_index = Convert.stringToInt(l_a_act_kpi[1]);

if (l_act_kpi_sort_index > l_act_minimum_sort_index) {
if (l_act_kpi_sort_index < l_search_minimum) {
l_act_kpi_id = l_a_act_kpi[0];
l_act_minimum_sort_index = Convert.stringToInt(l_a_act_kpi[1]);
l_search_minimum = l_act_kpi_sort_index;
} else if (l_act_kpi_sort_index == l_search_minimum) {
l_act_kpi_id = l_act_kpi_id + ";" + l_a_act_kpi[0];
}
}
});
l_act_minimum = l_act_minimum_sort_index;
if (l_act_kpi_id != "") {
l_kpi_id_sorted = l_kpi_id_sorted + l_act_kpi_id + ";";
}
});

l_kpi_id_sorted = l_kpi_id_sorted.substring(0,l_kpi_id_sorted.length - 1);

Regards,

Helder Matias

[MustafaBensan]

Hi Helder,

I am familiar with this issue too.  If you want to save yourself all that sorting code in the script, you can simply load the members and numeric attribute into the SCN Collection Utility SDK Component and apply the sort method.

Regards,

Mustafa.

[Former Member]

Just sharing an interesting note here:

2067424  - Method getMemberList / getMembers returns too many dimension members

[IngoH]

Hi,

the getMembers is asking for the MasterData and you receive the list from the master data or from the InfoProvider or from the Navigation - but it would be at max sorted by the Key or sorted by the text but it will be sorted based on the member list as you are receiving master data.

regards

Ingo Hilgefort, Visual BI

[MustafaBensan]

Hi Kris,

When you display the preview of data source DS_TOP_5 in the Initial View Editor, do the Company Code members appear in the top 5 sort order you are expecting?  Can you provide a screenshot of the Initial View window?

Regards,

Mustafa.

[Former Member]

Hi Mustafa,

I am not allowed to provide you a screenshot, as it is containing confidential data.

But the Initial view is as I want it.

It has an object in the rows box, and a measure in the columns box. No filters.

In the live preview i see a table with 2 columns. Here I have set a desc. sort on the measure.

/Kris

[MustafaBensan]

Hi Kris,

That's interesting.  I can confirm that I have been able to replicate the issue with a BW data source.

My data source Initial View looks like this with the Scheduled Flights measure sorted in descending order:

I have the following script for the On Result Set Changed event of the data source:

And the result with populated text boxes is as follows:

I'm inclined to think this is a Design Studio bug with the getMembers() method.  I would expect the sort order of the data source to be respected instead of returning members in alphabetical order.  You may wish to open a support ticket for this.

Regards,

Mustafa.

[mike_howles4]

I ran into this same scenario as well a few weeks back. 

[Former Member]

Did you find a workaround or how did you solve it?

[MustafaBensan]

Yes, it's a shame it doesn't work as intended since this would be a good approach for achieving Top N functionality on the client side.  Surely this is a bug?

[mike_howles4]

I figured I'd have to resort to an SDK solution and I decided that there were other things more important for me and I forgot about it and moved on.

I'll page to this thread to see if his Array Utility component can save the day

Perhaps a .sort method would be a good addition to help sort a list of Member Key/Texts here?

[mike_howles4]

I think both results (sorted or not) could be the expected result.  Probably a case of either way the other camp will think it's a bug

[Karol-K]

Hi All,

I would need to double check... but my current understanding is...

getMembers does provide all Members (respecting the read mode) from MASTER DATA, therefore the sorting which is applied on RESULT SET is not affecting getMembers call.

this means, what you get is not myTop5 but myFirst5 of master data.

Btw, you could make getMembers(maximum: as much you can) and then you could ask result set by getData call what is the value and sort it by value.

for TOP 5, you can use also the component or which are now available in the community package.

Karol

[MustafaBensan]

Well based on this precedent set by Karol, I'd place it in the "it's a bug camp"

[MustafaBensan]

Or maybe not, after Karol's explanation

[IngoH]

Hi,

the getMembers is asking for the MasterData and you receive the list from the master data or from the InfoProvider or from the Navigation - but it would be at max sorted by the Key or sorted by the text but it will be sorted based on the member list as you are receiving master data.

regards

Ingo Hilgefort, Visual BI

[mike_howles4]

Sorry, no coffee this morning, I had it backwards anyway, it's currently alpha sorting and you want it to be ranking.  My similar scenario was similar where I had a sort characteristic that I was sorting my results with, but the characteristic that I wanted Members for was actually still just alpha sorting.  So an array sort won't do anything good.

Probably a more feasible approach and more general-purposed anyway would be to have a component that:

1) Flattens the results into a 2-dimensional array based on the resultset (honoring sorting and ranking)

2) Provides BIAL methods to iterate over that resultset to access in order of their occurance in said resultset:

• Dimension key/texts
• Measure Values

This would address my sort scenario and the ranking scenario which both in essence suffer from the same symptom.

[Karol-K]

Hi all,

there are 2 cases:

one - member sort in result set, this one can be set by the method DS.sortByMeasure().

Then, the result set should be sorted according to the measure. and this should work for BW and other sources.

second - sequence of members by method getMembers(), this one cannot be sorted by measure as this is master data read - and the sort is as backend system is giving it back.

the case here - - was in my understanding incorrect sort of members in result set.

Karol

[Former Member]

Hi Karel,

It does sort as desired if i use sortByMeasure(), but the question now if how to get the top 5 dimension values from the DS, as the getMembers() is not the solution.

/Kris

[Former Member]

Hi Kris,

Have you tried using the script DS_1.sortByMeasure(measure, isSortAscending); ??

Regards,

Kriti

[Former Member]

Hi Kriti,

Yes, I've tried adding this right before the above mentioned code. Unfortunately this did not change the output of the getMembers function.

/Kris