Group sort order

[Former Member]

I have a visual studio application that passes in a pre-sorted list of order numbers to print a report for.  However, when the report print, it resorts the orders into ascending order.  I have tried to change the group sort to Original Order, but that did not fix it.  I tried to use specific order, but my data changes each time the report is run, so I cannot find a way to tell it to use the parameter that was passed in.  Is there a way to get this to actually use the sorted order that was passed in?

[DellSC · Accepted solution]

If the list of order numbers is in a DataSet in your application, I would add a field called something like "Sort_Order" to the table.  Set the correct values there and sort on that in your report.

-Dell

[Former Member · Accepted solution]

Hi,

Go in Report Menu-- Group Expert-- now click on Options button, here you can change the group sort by selection the desired option.

To sort your records within the group-- Go in Record Sort expert and select the field and choose the sort order..

I think the problem might be donot pass pre-sorted list of order numbers report will do it correctly. You can pass original list of order number then how this works..

or      try this scenario Insert a summary which is the maximum of order numbers. Then use the group sort expert to sort on this summary.

Thanks,

DJ

[Former Member · Accepted solution]

I am not using datasets.  The list of order numbers is sent into Crystal as an array parameter, plus the sort order is not asc or desc, it is sorted by another field so the order numbers are not in any order.

[Former Member · Accepted solution]

The issue is that I don't want to do any sorting within the report itself.  The list of orders comes across sorted in the proper order, as the logic to sort the orders is handled in Visual Studio due to the complexity of finding records to use.  I need the report to output the group in the order that the orders are sent in (i.e. 111,444,333,666,222,777,...)

[DellSC · Accepted solution]

So you're sending the order numbers in as a parameter?  There is no way to get it to sort based on the order of the value in the parameter array.  If you were to run the SQL for the report in a database tool, such as SSMS or Toad, using the list of order numbers in a specific order in the "where" clause of the query, you would find that the data is NOT returned in that order.  "Original Order" in Crystal means the order that it's returned from the database, not the order of the values in the parameter.  So, you will have to do the sorting in the report.

-Dell

[Former Member · Accepted solution]

Interesting.  I would have thought it could be done this way.

If I pass in a parameter to determine the sort order, how do I use that to sort the data then?

[DellSC · Accepted solution]

First, you'll create a formula that will provide the correct data for the sort.  It will look something like this:

If {?SortOrder} = 'NAME' then

  {myTable.Name}

else if {?SortOrder} = 'ORDER#' then

  right('000' + ToText({myTable.Order_Num}, 0, ''), 4)

else if {?SortOrder} = 'ORDER DATE' then

  ToText({MyTable.Order_Date}, 'yyyyMMdd')

You need to make sure that ALL of the values are of the same data type - the above example converts everything to a string and uses the following logic:

Number: Maximum length of the number is 4 digits, but to sort correctly after converting to a string, we need to add leading zeroes to any number that is less than 4 digits wide, thus we append leading zeroes and take just the right-most 4 characters.  Also, we don't want any decimal points or thousands separators when the number is converted to text, thus the use of the extra parameters in the call to ToText().

Assuming the order numbers we're using are 1, 12, 100, and 1120, just converting them to strings will put them in this order:

1

100

1120

12

However, if you add the leading zeroes, you'll get the correct order which is this:

0001

0012

0100

1120

Date:  Dates will also come out in the wrong sort order if you don't format them correctly for sorting.  Assuming that we have the following dates:

01/01/2013

02/23/2013

01/01/2014

If you convert them to a string in the above format, they'll appear in this order:

01/01/2013

01/01/2014

02/13/2013

However, if you put them in yyyyMMdd format (NOTE:  In Crystal, this format string IS CASE SENSITIVE!) you get the correct order:

20130101

20130213

20140101

-Dell

[Former Member · Accepted solution]

How would you sort by multiple fields then?  In one instance, I will need to do so to get this report formatted the way we need it to be.

[DellSC · Accepted solution]

I would concatenate the two values together in the formula, still using the rules above about numbers and dates.

-Dell

[Former Member · Accepted solution]

So just use a + sign between two fields then?  Also, how do I get the sort order to work?  I added it in as a formula but it is not clear where the formula needs to be referenced so it can sort correctly.

[DellSC · Accepted solution]

Correct on the "+".

If you're not using any groups in the report, you'll click on the "Sort" button - has A over Z with a down arrow - and add the formula there.  If you are grouping your report, you'll create a new group on the formula, drag it up so that it is the outer-most group, and suppress the group header and footer sections.

-Dell

[Former Member · Accepted solution]

I did that, but it still does not come out in the right order.  Since I am using groups, I put this as the outermost group with a sort order of Original Order.  Is that the correct way to do it?

[DellSC · Accepted solution]

Try setting the sort order to Ascending.

-Dell

[Former Member · Accepted solution]

I tried that already, but it still does it in order number.

At this point, I have the groups as follows.

Group 1 - SortOrder (contains formula for sorting)

Group 2 - Order # ( need to it print out a new group header for each order number )

Group 3 - Line # (item line # within each order)

Does my grouping still cause this issue?

[DellSC · Accepted solution]

No matter what, you're still going to need to sort by Order # within whatever sort order you have so that you can show the order lines correctly within each order.

You might try modifying your Sort Order formula and add the Order # at the end of it.  However, this will probably give you the same order that you're getting with the groups that you have.

If the sort you have will be unique for each order, you could also take out the Order # group and just use the Sort Order group.  However, if you have more than one order that matches the same sort criteria, this won't work.

If you can attach a copy of the report that has been saved with data (turn on "Save Data with Report" on the file menu)  I'll take a look at it.  You'll need to change the file extension on the .rpt file to ".txt" in order to upload it from the Advanced menu here.

-Dell

[Former Member · Accepted solution]

The criteria I am using will result in multiple orders in the same group as it stand, hence why I have the order # group still.  Can't the report know that the top level group matters more than the secondary group and go in the order that things were returned in?  I have attached the report with the repot data saved in it.

Ord Note Report.txt.zip

[DellSC · Accepted solution]

The result of the Sort formula in the report you sent is the order number - that's why it's in that order.  This is because you have mis-spelled "ShipTo" when you entered the parameter.

I updated the report to make the Sort parameter a static list, which will guarantee that you have the correct value.  Please try running it again.

-Dell

Ord Note Report - Dell's Update.txt.zip

[Former Member · Accepted solution]

I see that now, but when I spell it correctly, it still results in being in order # order.

[Former Member · Accepted solution]

Nevermind - I think I have it working now.  Thank you.

[DellSC · Accepted solution]

If you create a static list of options for the parameter instead of making it free-form text, then you prevent issues like this if the user doesn't type the option correctly and you give them a specific list of valid options.

-Dell