cancel
Showing results for 
Search instead for 
Did you mean: 

How do you sort months from a UNX universe with Design Studio?

david_lai
Participant
0 Kudos
285

Hello,

I'm trying to figure out how to sort months on a dropdown box component in the correct order.

The months are coming from a UNX universe. 

The 2 month are objects we can grab from the DB are:

Month Text

Month Number

Unfortunately when I add months to a dropdown, it shows up as

April

August

...

October

September

I'm trying to have the dropdown show

January

February

...

November

December

Any help would be appreciated

Thanks

David

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi David,

Were you able to find any solution for sorting the list of values in the dropdown ? If so, could you please share.

Thanks,

Rajesh

Karol-K
Product and Topic Expert
Product and Topic Expert
0 Kudos

and if you cannot change the data but want to make a sort in design studio, in case of months this should be quite easy with simple script. you could create an array of sorted months

var monthsSorted = ["January", "February", ...];

var resultString = "";

monthsSorted.forEach... (monthSortedObject, inxed)

{

     loop on your months here and concatenate the string

    

     if(yourmonth == monthSortedObject) {

          resultString = monthSortedObject + ";";

     {

}

and with another split and loop you have the sorted list.

var splittedMonths = resultString.split(";");

splittedMonths.forEach...

hope this helps a bit, also when you can use SDK, we have Collection Util, where you can pack the months and sort automatically. Of course you need to create initially a binding between the name and number. You can check the community SDK components here:

Karol

david_lai
Participant
0 Kudos

Hi Karol,

Thanks for your solution, however that only works if you hardcode your months.  What if you have a dynamic set of months let's say Jan 2013, ... Dec 2013, Jan 2014, .. Dec 2014?

And let's say we had a code dimension object that could determine the sort order.  ie: Jan 2013 = 1; Dec 2014 = 24

How would we use the code to determine the sort order?

For example if we could do something like this it would be great

Datasource contains Month Number and Month Name

ForEach(DataSource Month Number Array)

{

Dropdown.addItem(Month Name dimension that corresponds to Month Number)

}

Thanks

David

Karol-K
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

here is your code - you need a button and a listbox to run it in your app.

var years = ["2012", "2013", "2014", "2015"];

var months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

var unsorted = ["Apr 2013", "Jan 2013", "Jun 2013", "Sep 2012", "Oct 2014", "Dec 2013"];

// create big string

var allUnsortedMonths = "";

unsorted.forEach(function(element, index) {

  allUnsortedMonths = allUnsortedMonths + ";" + element;

});

years.forEach(function(year, indexYear) {

  months.forEach(function(month, indexMonth) {

   var monthYear = month + " " + year;

  

   // if this combination is there in unsorted array as string

   if(allUnsortedMonths.indexOf(monthYear) > -1) {

    LISTBOX_1.addItem(monthYear, monthYear);

   }

  });

});

this should help you, even w/o any SDK components. the unsorted array needs to be replaced by the method which is returning your months from datasource

IngoH
Active Contributor
0 Kudos

Hi David,

agree with you here, the inability to sort list of values is an issue so far in Design Studio.

For "date" fields there might be an easier option instead of writing lots of script and creating arrays.

Not sure if you are familiar with BW, but in the BW case a date is always stored as a 8 character string YYYMMDD as key value and then there is the description.

The string will always be sorted properly - even when sorted as string.

Perhaps in your case you can add the logic already into the underlying Universe and have a "date" field like that.

regards

Ingo Hilgefort, SAP

david_lai
Participant
0 Kudos

Hi Karol,

Thanks for your input, however var years and var months are still hard-coded.

So let's say I want my dashboard to work until 2010.  Then I would have to either

a) Hardcode the years until 2010, but every time the dashboard runs it will loop empty cycles until 2010.

b) Edit the dashboard each year to add an additional year

Hard-coding is more of a hack and never a best practice, but we'll see if SAP fixes it where a query sort is actually reflected on the datasource.  Unfortunately this is a major limitation right now with Design Studio.

Just imagine if there are other types of dimensions that need to be sorted by key instead of alphabet and let's say there are 300 selections to choose from. Then we would have to hardcode all 300 in our code! 

Regards

David

david_lai
Participant
0 Kudos

Hi Ingo,

Thanks for your input, however a lot of business users do not like to see YYYYMMDD as their selection.

In addition I used the month selection example because it is easy to understand.

Basically I'm looking for the ability to sort lists based on key which is a common requirement among users.

Country LabelSort Key

United Kingdom

1
United States2
Canada3
China4
India5
200 more countries...

When creating the datasource query there is a sort option, however it does not work!

If we didn't have that major UNX limitation, we could easily accomplish sorts and not have to worry about scripting/hardcoding lots of code and going through unnecessary processing loops.

I even tried using the datasource sorting functions to try to sort the datasource by the key field but unfortunately the Datasource.sortByMember(dimension,ascending) does not work on UNX universes.

Regards

David

IngoH
Active Contributor
0 Kudos

Hi David,

understood the underlying problem but there is not much you really can do so far unless you can sort all the items in the underlying source already.

The data source sort - my assumption here - would only impact the actual data but has no impact on the list of members.

regards

Ingo

david_lai
Participant
0 Kudos

Hi Ingo,

The underlying source is actually sorted by key.

Here's the underlying query as you can see that there is an order by.

SELECT

  distinct DimTime.EnglishMonthName,

  DimTime.MonthNumberOfYear

FROM

  DimTime

ORDER BY

  2

It's the Design Studio datasource object that re-sorts it alphabetically which is the problem

Thanks

David

Karol-K
Product and Topic Expert
Product and Topic Expert
0 Kudos

David,

the scripts can help, but in case the order is incorrect in filter panel as well, this seems to be a bug in BICS access to universes. I thought, you could not sotr it on data level - but if this is sorted there, design studio should respect the correct sort order. So, open customer incident on this to check by dev group.

IngoH
Active Contributor
0 Kudos

Hi David,

open a case with the support team and point out that the sort is done in the underlying source already and keep it as a very simple example

If you sorted it already, Design Studio should not change that sort order

Ingo

david_lai
Participant
0 Kudos

Thanks Karol/Ingo for your answers.

I'll open up a ticket.

Furthermore, even though I have it sorted at the query level and try the following code

var month = DS_1.getMembers("_daoVkYZkEeSCr_cj7E95dQ", 12);

month.forEach(function(element,index) {

     APPLICATION.alert(element.text,index);

});

We will see that Design Studio re-sorts the datasource alphabetically which is incorrect.

Thanks

David

former_member197751
Participant
0 Kudos

Hi David,

   

     When designing Query for Dashboard, sort the Month Name based on Month Number. It will be like this, When you are preparing query for data-source from universe after keeping required objects, Press the AZ Sort icon to sort ( as showed (in Yellow mark)  ) and by clicking Plus button as shown in Second Screenshot( to select the object based on which sorting need to be done ) and select the Month Number to Sort according to it.

   Note :For this no need to add Month Number in Query Display Panel.

Hope this Help.

Thanks and Regards

Sateesh

david_lai
Participant
0 Kudos

Hi Sateesh,

Unfortunately your solution does not work with dropdowns, it only works if for example you are showing the result data on a data table.

When you try using the result data on a dropdown, the dropdown component automatically sorts the months alphabetically instead of the order that it came from the query.