Traditional web-based reporting with SAS BI tools

The bad sorting function in group break.

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

The bad sorting function in group break.


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


Accepted Solutions
Solution
‎10-08-2013 09:20 AM
Contributor
Posts: 28

Re: The bad sorting function in group break.

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!

1.png

View solution in original post


All Replies
Frequent Contributor
Posts: 120

Re: The bad sorting function in group break.

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.

Contributor
Posts: 28

Re: The bad sorting function in group break.

Hi again, I now have a numeric value, but still it seems to sort 10 before 2...

Frequent Contributor
Posts: 120

Re: The bad sorting function in group break.

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....

Contributor
Posts: 28

Re: The bad sorting function in group break.

The dimension in cube studio:

1.png

The details in infomap studio (no option to set any format):

2.png

The group break in wrs (still wrong sorting):

3.png

BR

Daniel

Frequent Contributor
Posts: 120

Re: The bad sorting function in group break.

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!

Contributor
Posts: 28

Re: The bad sorting function in group break.

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...

1.png

Frequent Contributor
Posts: 120

Re: The bad sorting function in group break.

ok, final tip.....

Set sort order on the dimension to Ascending unformatted.....

Contributor
Posts: 28

Re: The bad sorting function in group break.

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!

Frequent Contributor
Posts: 120

Re: The bad sorting function in group break.

not gooood at all....Smiley Happy

I have no cube to test with, but it looks like you already tried everything.....Smiley Happy

Contributor
Posts: 28

Re: The bad sorting function in group break.

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.

Contributor
Posts: 28

Re: The bad sorting function in group break.

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...

Solution
‎10-08-2013 09:20 AM
Contributor
Posts: 28

Re: The bad sorting function in group break.

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!

1.png

Frequent Contributor
Posts: 120

Re: The bad sorting function in group break.

Great work!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1091 views
  • 2 likes
  • 2 in conversation