on 12-18-2014 5:49 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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 Label | Sort Key |
---|---|
United Kingdom | 1 |
United States | 2 |
Canada | 3 |
China | 4 |
India | 5 |
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
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
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.