Sort not working

[pmanildi]

I have a report that sorts by store then buyer. When I grap the SQL Query and run it, the data comes back sorted perfectly. If I browse the buyer field, it shows in order 1 - , 10 -, 100-, etc.

But the report does not.

I was going to create a sort order field, but when I did, it was even worse!

Is this a bug? I have SAP CR Developer, @2011, Version 14.0.4.738

any help would be appreciated.

[abhilash_kumar · Accepted solution]

Hi Paula,

This is Not a Bug!

The issue is alphanumeric fields always have sorting issues. CR doesn't treat the numbers in there as 'number' datatype, it treats them as string. Hence "10" treated as string will always appear before "1".

To sort based on the numbers in that string field, you need to extract them and group on the numbers. Here's something that you might want to try:

1) Create a formula with the code below:

local numbervar i;

local stringvar num;

for i :=  1 to len({vw_Writeoffquery.Buyer}) do

(

     if isnumeric({vw_Writeoffquery.Buyer} [i]) then

          num := num + {vw_Writeoffquery.Buyer}[i];

);

ToNumber(num);

2) Next, go to the Group Expert > Remove the {vw_Writeoffquery.Buyer} field from the group list and add the formula we created above in its place

3) While in the same window, highlight the new formula field in the Group List > Click the Options button > Options tab > Check the option 'Customize Group Name field' > Choose the {vw_Writeoffquery.Buyer} field from the drop-down

You should now have the groups sorted in the right order.

-Abhilash

[pmanildi · Accepted solution]

Actually, I created a separate, numeric field in my view which just contained the numbers, and it still didn't sort right. You can see that they are number fields in the images. Are you sure it isn't a bug?

[abhilash_kumar · Accepted solution]

Hi Paula,

The 'Browse Data' option returns limited rows (approx 500). It returns Distinct or Duplicate records depending on what you've selected in the File > Options > Database tab (option is 'Select Distinct Data for Browsing').

This option is supposed to return values "as it reads from the database" - in the same order it is entered in the database. However, I've also seen that the list shows in Ascending Order sometimes.

If you have an issue with the sorting of the groups, you could use the method I mentioned above.

-Abhilash

[Former Member · Accepted solution]

Dear Paula ,

What u r doing is correct . your grouping buyer below store no .  As per your attached file group 3 is sorting correctly in the order of Asc (410,417,148....). Just check  group header 1 .

In Design tab - right click group header 1 - select change group - in common tab select in which order u want to display . 

Hope this will solve your query .

[pmanildi · Accepted solution]

Group 3 is sorting fine inside group 2. but group 2 isn't sorting A. I've suppressed group 3 to show you group 2 sorting which is killing me! Also the options Database tab in case something else needs to be set. The bottom line though is that the SQL query returns the data in the right order, why isn't the report using THAT?

. Why is 999 first if it is in A order? And then, 427, 419, 442, 440, 428, ...

[abhilash_kumar · Accepted solution]

Have you applied a Group Sort based on a Summary?

That could be the only way these groups can be arranged assuming the group is now on a Number field.

Would you be able to attach the report with saved data if that's possible? To attach the report, change its extension from .rpt to .txt and click the 'use advanced editor' option in the reply window.

-Abhilash

[pmanildi · Accepted solution]

I've attached the report with data as a txt-named file.

When I look at Group Sort, I don't see a way to do it. There aren't options available that make sense there.

WriteOffSummaryALLSTORES_ver8.txt.zip

[abhilash_kumar · Accepted solution]

As I guessed, you've sorted 'Group #2' in Descending Order based on a Summary called 'WriteOffAmnt'.

If you don't wish to sort the group by summary, just go to the Group Sort Expert ( Report > Group Sort Expert).

Select the 2nd tab in the window (vw_WriteOffQuery.sortOrder) > From the drop-down that now says 'All', choose 'No Sort' > Click OK.

That's it. Group # 2 will now be sorted in Ascending or Descending order based on the values.

-Abhilash