Grouping on one field and sorting on another in Crystal Reports

Today I was stuck with this problem in Crystal Reports. I have the report grouped on some field but don’t want the records to be sorted on that field. Instead I wanted to have it sorted on another field.

The Problem

When you have a group field in the report, crystal report automatically adds the “group by” field in the sort list, and there is no way of removing it. If you click the Record Sort Expert button on toolbar and open the Record Sort dialog, you will see what I mean. You will find your “group by” field already there, and buttons to remove it disabled. Also you can’t lower its sort priority by specifying any other sort condition above it; so this is the first sort condition forcefully, whether you like it or not. I have no idea why Crystal report does this. But in my case I didn’t want the records to be sorted by this field.

The Solution

I couldn’t find anything inside Crystal Reports to solve my problem. Fortunately, since I was using datatable anyways, this is how I resolved my problem.

This was a two step approach:

1. Some changes in the Crystal Report file.
2. Some changes in the way we connect it to dataset/datatable.

Setting up things in Crystal Report file

1. Click the Group Expert button on toolbar. This will open the Group Expert dialog box.

2. In the Group Expert dialog box, click the Options… button. This will open Change Group Options dialog box.

 

3. Here in the second dropdown, select “in original order” option. Click OK button to close the dialog box.

4. Click OK button in Group Expert dialog box to save changes and close it.

5. Save the report file and close it.

Changes to How we use the DataTable

I modified my VB.NET code to sort the datatable there instead of letting Crystal Reports do it for me. So we pass it a pre-sorted datatable according to our needs.

This is the scaled down version of what I did:

'create datatable

Dim myDataTable As New DataTable

'TODO: fill datatable with appropriate data here

'set the sort

myDataTable.DefaultView.Sort = "City, CustomerName"

'create report document

Dim myReport As New ReportDocument

myReport.Load("C:\ReportTemplates\CustomerReport.rpt")

'set datasource

myReport.SetDataSource(myDataTable.DefaultView.ToTable)

'now do whatever you want to do with this report.

'e.g. I exported it to disk file, but you may want to show on screen etc.

myReport.ExportToDisk(ExportFormatType.PortableDocFormat, "C:\Reports\CustomerReport.pdf")

Your requirements may not be exactly same as mine. But this is the general approach to follow. (Pay more attention the comments than the code itself)

How This Works

What we basically did is not let Crystal Reports apply its brain for sorting the data. Instead we passed it the sorted data itself. The code is self-explanatory.