Hi!
I have a dimension in the group break, the values in the dimensions are 1 year, 2 years ... 14 years. In the group break, you can choose ascending or decending order (not default data set order for some reason...) for theese dimension values. The problem is that dimensionvalues 10 years - 14 years sneaks in between 1 year and 2 years... Is there any way to keep the natrual order 1-14 instead of 1, 10, 11, 12, 13, 14, 2, 3.....
Thanks in advance!
Daniel
With anger came the solution! The solution is hidden characters, by pressing ALT+255(numbers on the num-pad) one can create a character that looks like a blank but isn't recognized by WRS or the web browser as a blank. This character comes after numerics in the sorting order. By inserting a hidden character in front of the 2-digit numbers in the dimension source-table (in oracle oracle), the sorting problem was no more!
I think the easiest way is to have a numeric value and then apply a format to get "1 year", "2 years", etc. This can be done in information map studio and common format catalog.
Hi again, I now have a numeric value, but still it seems to sort 10 before 2...
And you have applied the format and it is shown in the report?
I just tried grouping by a formatted column and got the sorting correct....
The dimension in cube studio:
The details in infomap studio (no option to set any format):
The group break in wrs (still wrong sorting):
BR
Daniel
I think you need to apply the format in cube studio, i did my testing with a table....
You have best15. as format now, try your user defined instead!
Done that, still doesn't work..
proc format;
picture arformat
low-high="00 år";
quit;
then I applied the arformat to the dimension directly in the cube, the result, still the same...
ok, final tip.....
Set sort order on the dimension to Ascending unformatted.....
Nope, doesn't work. Seems like the sorting function in the group break doesn't care about the choosen sorting type in the cube or infomap. Always seems to sort the formatted value :smileyangry:
We will go with the "ugly" soulution and use blanks in front of the values with only one digit. Thanks Fredrik! The help is always apprecheated!
not gooood at all....:)
I have no cube to test with, but it looks like you already tried everything.....:)
Yeh, I've played around quite a bit 😃
The best soulution was to insert a zero for the on digit year values, 01 year, 02 years ... 11 years, 12 years. But it was accepted as a plan-B soulution. The result of the ugly plan was preferred (if it works) =). I tried to insert a blank with the prefix function for picture format, but it didn't give any result when used directly in the cube. We will now try to insert blanks in the oracle dimension table.
Wow! WRS is really stubborn! The leading blank soulution does not work because WRS has a built in STRIP function. It's not in place to remove this funtion from WRS either because we already have a great deal of reports and cannot (don't have time/money) research the implecation on thoose... really a tumb down to the sorting function in group break. Why can I have a data set order in the report but not in the group break??
This issue remailns unresolved and we will go with the 01,02,03... soulution...
With anger came the solution! The solution is hidden characters, by pressing ALT+255(numbers on the num-pad) one can create a character that looks like a blank but isn't recognized by WRS or the web browser as a blank. This character comes after numerics in the sorting order. By inserting a hidden character in front of the 2-digit numbers in the dimension source-table (in oracle oracle), the sorting problem was no more!
Great work!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.