Custom Sort Order Possible? 1

[ciscowiz]

I am using CR9 with MS SQL Server 2000. The data I am pulling back is for a newspaper and the field I want to sort on has values such as F01, F02, B01, B02, etc. F is for front, B is for back so in reality, the order should be F01, B01, F02, B02, etc. not B01, B02, F01, F02 as it is now. I am just wondering how to set a custom sort order or if it is even possible.

Thanks,
bryan

 

[lbass]

I think you could insert a group on the following formula:

right({table.field},2)

...and set it for ascending. Then add {table.field) itself to report->sort records and choose descending. The group will force the numeric order and then within each group the F will come before B. You can suppress the group header and footer.

-LB

 

[ciscowiz]

Wow I am amazed again at the accuracy and quickness of your response, worked great. Now maybe you can help me with another problem. My data is in the report below as follows:

GH1 - PageRequest (The field you told me to group and suppress)
D - I have a Code Field, the Page Request field, and some counts of the instances of requests. With Data it looks something like:

PH- CODE Page Req. Count1 Count2 Count3
GH1- 01 (Suppressed)
Details- Wireless B01 1 0 0
02
Aluminum F02 0 1 0
Construction B02 0 1 0
Automotice B02 0 1 0

The Page Req. field is shwoing up fine and in proper order, HOWEVER, I would like the 2 rows with the Page Req. of B02 (Construction and Automotive) to be combined into one row which would look something like this

02
Construction, Automotive B02 0 2 0

I know it doesn't look pretty so let me know if you need any more info. Thanks again for helping me with the sort order!

Thanks,
Bryan

 

[lbass]

Your first group would be on the formula above. Then insert a second group on {table.pagerequest} if this is the field that results in "B01", etc. Drag the groupname into the group #2 footer. Then create three formulas:

//{@reset} to be placed in the GH#2 header:
whileprintingrecords;
stringvar code := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar code := code + {table.code} + ", ";

//{@displ} to be placed in the Group #2 footer:
whileprintingrecords;
stringvar code;
left(code,len(code)-2);

Then right click on the count fields in the detail section and insert summaries at the group #2 level. Then suppress the group header and the detail section.

-LB

 

[ciscowiz]

Worked perfectly thanks again LB!