Problem with RecordSortFields

[SilentRage47]

Hi, everybody i'm trying to do a sort the data to be print with Crystal Report


.Show
Set Report = Crxapp.OpenReport(mypath, 1)
.ReportViewer.ReportSource = Report

Set crFormulaField = Report.Database.Tables(1).Fields(24)
Report.RecordSortFields.Add crFormulaField, crAscendingOrder

.ReportViewer.ViewReport


In my report i've got 1 group that i need to sort.

 

[hilfy]

So you're trying to dynamically set the sort for the report based on something the user selects? Have you looked at trying to do that using a prompt and a formula in Crystal itself? That might be much easier.

I see what looks like VB code (I think....) What version of VB/Visual Studio/Crystal are you using?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SilentRage47]

Ok, sorry for me being not accurate.

I'm using vb6 and cr XI.

I've got a form that create a table that i pass trough a report in order to print it. In my program the user can select in what order sort the data (it's a combobox and a field in my print table).
What i need is to sort the data before showing the report, the user can only select the sorting on my form. I've tried using the recordsortfields but it just doesn't work, no errors at all but my report is always sorted by the group sorting, maybe that problem is my report and not the vb6 code.

 

[hilfy]

Group sorting will ALWAYS come before the sort of the records inside the groups. So, if your report is grouped on field A and field B and sorted on field C for a total sort of A then B then C, changing the recordsortfields to field D will change the sort to A then B then D.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SilentRage47]

So what should i do in order to use the recordsortfields ?

 

[hilfy]

You would have to either take the groups out of the report or change the groups dynamically. I'm not sure whether this can be done using code, but I know it can be done internally in the report by basing the groups on formulas that look at the parameters to determine which field to group on.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SilentRage47]

I only have 1 group but i cannot sort it dynamically with a formula on cr, i tried and i can only change the order (ascending/descending). What can i do to sort it by different items ? For example i need to sort it by OrderNumber, Name, and Date.

 

[hilfy]

The challenge is to create a formula that will convert everything to a string. Numbers will need to include leading zeros and dates will need to be in yyyyMMdd format to sort correctly. So, you need to do something like the following:

1. Create a parameter. I'll call this {?SortOrder}.
2. Create a formula that looks something like this:
If {?SortOrder} = 'Order Number' then
ToText({Table.OrderNumber}, '00000000', 0, '')
else if {?SortOrder} = 'Name' then
{Table.Name}
else
ToText({Table.Date}, 'yyyyMMdd')

3. Use this formula as the group field.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SilentRage47]

I'm trying to do this but i'm facing some big problems...First of all, i have a field on my table that indicates what order to use, can i use that to replace the sort paramater ? Something like this :

If {myTable.sortOrder} = 1 then..

Second question, where do i create the formula ? Is it correct to use it in the group options, where there is "Use a formula as Group Sort Order" ?

Third and final question, what {Table.OrderNumber} stand for ? Is table my database table or what ? I cannot understand what that is. In the formula i can't select my database field so i think it's something else.

Sorry but i'm a newbie trying to learning something, thanks for the help

 

[hilfy]

1. Yes, you can use the data in your table to determine the sort order - similar to how I used the parameter in my example.

2. Create the formula in the formula editor where you would create any other formulas for your report. Then change the group to point to that formula.

3. {Table.OrderNumber} represents the order number field in your report. You'll replace any of the field that I used that start with "Table..." with the actual fields that are in your report. You said you want to sort by Order Number, Name, and Date - so that's what I used in the example. I assumed that the Order Number is a numeric field, Name is a string, and Date is a date and showed you how to convert all of them to a string because the If...then...else statement requires that all results be of the same data type. For Order Number, I assumed that the field was up to 8 digits long. If it can be longer than that, you'll need to increase the number of 0's in the format - the number needs to have leading zeros in order to sort correctly. Dates need to have leading zeros on any month and day that has a value less than 10 in order to sort correctly.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SilentRage47]

Man that was so easy, don't know why it took me so long but finally made it. It works flawlessly, just one more final question : when i sort by "State" it groups all the ones that have the same state, is there any way to sort by 2 fields ? Like State and OrderNumber

 

[hilfy]

Yes. Use the group formula to set the state. Then create a sort formula that is similar to how the group formula is configured, that will sort by order number within a group or by a static value (say 0) when you don't need the sort by order number.

if {myTable.sortOrder} = x <=Number for state sort.
then {myTable.orderNumber}
else 0 <=Value so that data is not sorted.

You would then use this formula in the Sort Expert. So the state will sort first because of the group and then the data will sort on order number within the state.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com