Change Crystal Report Sort Order Programatically In VB.NET

Example showing how to change the sort order of records in a crystal report from VB.Net.

If you're using VB.Net to drive a Crystal Report, you might want to choose the sort order for the records from within your program. In this case you have to set certain properties of the ReportDocument object. In this example, we have the field name to sort by in variable sSortField, and the table that field belongs to in sSortTable. We are sorting by "customerName" in table "tblCustomers".

Note that we are changing the field the report is already sorted by. I.e. you must already have the report sorted by something, or you will get error "Invalid sort number". If you haven't already, store a default sort order within your report, using Record Sort Expert from the Report menu.

	Dim rd As New CrystalDecisions.CrystalReports.Engine.ReportDocument
	Dim sf As CrystalDecisions.CrystalReports.Engine.FieldDefinition
	Dim tli As New CrystalDecisions.Shared.TableLogOnInfo
	Dim iSortTableIndex as Integer = -1
	Dim sSortField As String = "customerName"
	Dim sSortTable As String = "tblCustomers"

	' Load the report file
	rd.Load("c:\myreport.rpt")

	' Set the connection properties to the database, as usual...
	With tli.ConnectionInfo
		.DatabaseName = "databasename"
		.Password = "password"
		.ServerName = "server"
		.UserID = "user"
		.Type = CrystalDecisions.[Shared].ConnectionInfoType.SQL
	End With

	' Get the index of the sort table while applying db logo info
	For i As Integer = 0 To rd.Database.Tables.Count - 1
		rd.Database.Tables(i).ApplyLogOnInfo(tli)
		If sSortTable <> "" AndAlso _
			sSortTable.ToUpper = rd.Database.Tables(i).Name.ToUpper Then
			iSortTableIndex = i
		End If
	Next

	' Sort records  (The report must already be sorted by something, we are just
	' changing that sort to a different field)
	If iSortTableIndex <> -1 AndAlso sSortField <> "" Then
		sf = rd.Database.Tables(iSortTableIndex).Fields(sSortField)
		rd.DataDefinition.SortFields(0).Field = sf
	End If