Group formula on sorting with user selection

[DeafBug]

This is a toughy and I have already spend a full day on this. I am not sure what I can do. It works as far as I know but there is a little thing that I am not seeing. I hope you can help.

Here is the specs. The report has 5 groups. All but one is grouped by a recordfield. The only one is a user selection to group the report on that will "sort" the records. It works in every case except in one.

Group1 Part Type
Group2 Store Number
Group3 User selection
Group4 Inventory Number
Group5 Category

The user selection group has 8 selections to sort the record by. All but two are numeric fields. The two is converted by using ASC function to make it numeric as the formula deals with one data type for output.

Here is the formula.

IF {?SortField}=1 THEN
{@TotalLookups}
ELSE IF {?SortField}=2 THEN
{@TotalSales}
ELSE IF {?SortField}=3 THEN
{InvReprice.OutOfStockLookup}
ELSE IF {?SortField}=4 THEN
{InvReprice.InStockLookup}
ELSE IF {?SortField}=5 THEN
{InvReprice.Returns}
ELSE IF {?SortField}=6 THEN
{InvReprice.InvQty}
ELSE IF {?SortField}=7 THEN
(Asc(Mid({InvReprice.LocationCode},1,1))*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},2,1))*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},3,1))*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},4,1))*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},5,1))*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},6,1))*128*128*128
+Asc(Mid({InvReprice.LocationCode},7,1))*128*128
+Asc(Mid({InvReprice.LocationCode},8,1))*128
+Asc(Mid({InvReprice.LocationCode},9,1)))*-1
ELSE IF {?SortField}=8 THEN
(Asc(Mid({InvReprice.InventoryNumber},1,1))*128*128*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},2,1))*128*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},3,1))*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},4,1))*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},5,1))*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},6,1))*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},7,1))*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},8,1))*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},9,1))*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},10,1))*128*128
+Asc(Mid({InvReprice.InventoryNumber},11,1))*128
+Asc(Mid({InvReprice.InventoryNumber},12,1)))*-1

InventoryNumbers and Location is a DB string field. They are always alphanumeric. Examples of inventory number 100-123456AB or 220-654321BA. Examples of Location MM123, BACK01, CA-4AA-04. Both may not always be a max length of 12 for inventory or 9 for locations but always works when less. I even checked pages on Inventory number sort. Even it comes out beautiful regardless of having 12 characters. Location is only 9 and has a problem.

The formula was written by a consultant back when we were using CR7. Now we are on CR10 with VB6 and SQL Server 2000. I don't know CR10 so well.

I do know that you can trick the sort by using two Sort formulas for having two data types. I have SortString and SortNumber that I just add to Record Sort Expert. But this time it is a group and you can only have one group formula as you could only select one thing to group on. So that is why the formula above is written that way.

The client is complaining that the Location is not sorting correctly. It is showing (notice first two)
A-46-C-08
A-46-C-07
A-46-C-08
A-46-C-09

This is just one example. There are many like that where the last digit is throw off of the sort order. How do I correct that or try some solutions?

Thank you.

 

[DeafBug]

I forgot one thing that you might notice. The last part of both ASC calculations shows *-1 is for the value to be negative as the sort direction is descending for the numeric values to show largest to smallest. By having a negative value, it will shows A-Z instead of Z-A.

 

[Madawc]

My advice is to create a test version, and add 'diagnostic sections' below the regular versions. Break the problem up into parts and see which bit is producing odd results.

Regarding A-46-C-07, could it be O rather than 0? You do get multiple groups that look the same if one has spaces at the end, but they should still sort in the right order.

There can also be special rules for sort order, but I'm not sure when they started.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[DeafBug]

I have tried a variety of different methods. I even am trying to recreate it here with our testing system and I am not getting it to work as it is. I really want to know what the sum is for that formula. I put the formula on the group header. The output is like 4.178e+17. I did the math with Karenware.com Calculator. It shows the that the first 17 digits are the same but the last one or two is different depending on the character. Of course it takes a while to do the math so I only tested on two numbers then assume the last two based on the ASC value.

Is there a way in Crystal to display the entire value like 4183952681134658 instead of 4.183e+16? That will speed up my debugging as only the last few digits are different for locations that are the same except the last character.

 

[DeafBug]

OKay, after spending so much time on this. I have learned a few things but I still can't resolve it. I know what it is doing conflicts with another thing, which I don't know.

What I saw was that the Location data field is on the Detail line while the Inventory Number in most cases is unique on the report. So it throws it off when Group 4 kicks in. I have played around to figure out how to make it work. I took out the Inventory Number from group 4 to group by a formula which returns nothing "" and it still reports that the inventory number is still grouped up. No longer in order.

I am doing whatever I can to make it work. I removed groups 1 and 2 as it has nothing but the information itself. I can't remove Group 4 or 5 as there are lots of other data fields on it. With just two groups, I still can't get the location to work.

The irony thing is that only the Location field is on the Detial line and all the other sort fields are on the group headers. So I just want to make it work and work backwards once I get it.

 

[DeafBug]

Alright. Two things I did. I created my own test data. 6 records, two of each records of same location so I have 3 different locations. In the two records that have the same location, each one has a different category.

Part Type Location Category
850 AA-A-0-01 U
850 AA-A-0-02 U
850 AA-A-0-03 U
850 AA-A-0-01 Y
850 AA-A-0-02 Y
850 AA-A-0-03 Y

I stripped the report down to two groups.
Group 1 User selection
Group 2 Category

I tried without the category and the output is not what it should be with the records lumped up. I put the label for the location on the group header so I can see what is selected.

The sort selected for the formula is correct, number 7. The location in the group header is AA-A-0-01.
There is only one group created for the location. The category group was working fine as it separates the U and Y.

Question, why didn't I get 3 separate groups for location and two separate categories under each group. I am expected one record in each group.

 

[DeafBug]

Alright, Problem solved. I will post what I did here. Darn that consultant we hired as he doesn't really know anything. He has that ASC function in a number of reports. So I will have to clean them up.

Don't bother using the ASC function if you have different data types in a formula. Instead apply what Business Objects KB gave at the group level.

http://support.businessobjects.com/library/kbase/articles/c2008546.asp

The KB mentions about sorting. But you can do the same for grouping. Just make another group and using Supress to hide one or the other, or both if not showing either.

With two formulas for grouping, GroupNumber and GroupString. Change the "Change Group" setting to in both groups to the formulas. One will have GroupNumber and the other will have GroupString. It works just fine.

Hope this will help in the future for anyone and a lesson learned for those who didn't know.