Crystal Reports: Sorting

I have a complicated Crystal Reports report - Management Report.   The report is based on a single table, which has been built up in Visual Studio, within my VB.NET Project.  The table pulls in data from about 7 different tables, with inner and outer joins... It contains all the fields that I need to calculate the sorting, as well as the display fields, and other fields I will use to calculate display fields.

For example,  ItemUidT1, ItemUidT2 are GUIDs from two tables and ItemRefT1, ItemRefT2 are strings from those two tables.  These two tables link to a main table by their respective ItemUids.

But I have one column in the report for printing ItemRef.  I want to be able to sort the whole table into all values from Table1 and then all values from Table2... but I also want the user to click a button on the form to change the sort order to an Index or Count value for all items irrespective of their Table.  Take it that the schema for Table1 and Table2 are very different, which is why they should not be stored in the same table with a 'flag' field.

This is not about building such a table... but using it in Crystal Reports.  The table could look like this:

fyi:

SELECT MainTable.MainTableUid,
               MainTable.ItemUidT1,
               Table1.ItemRefT1,
               MainTable.ItemUidT2,
               Table2.ItemRefT2,
               MainTable.OtherData
FROM Table2 RIGHT JOIN
             (Table1 RIGHT JOIN MainTable ON Table1.ItemUidT1 = MainTable.ItemUidT1)
                      ON Table2.ItemUidT2 = MainTable.ItemUidT2;

In the report I have created formula fields for displaying... so Formula: {@ItemRef} = [note: code is in VB Syntax not Crystal Syntax]

If Not IsNull({QueryMain.ItemUid1}) Then
    formula = {QueryMain.ItemRef1}
Else
    If Not IsNull({QueryMain.ItemUid2}) Then
        formula = {QueryMain.ItemRef2}
    Else
        formula = "n/a"
    End If
End If

My actual report has 8 sort fields.

SortFields(0):  Not linked to an Item Table

On one report I would like to move all the Items in the list which are not linked to an Item Table down to the bottom of the list.  To do this I create a new Formula called {@IsNotInItemTable1} =

If IsNull({QueryMain.ItemUid1}) And IsNull({QueryMain.ItemUid2}) Then
    formula = 2
Else
    formula = 1
End If

This is added to Report -> Record Sort Expert - sorted Ascending.

SortFields(1):  Alphabetical List of ItemRef

From the first sort, we have two groups of records... this in the Item Tables and those not.  I would like to sort by ItemRef next... the formula is the same as for {@ItemRef} above, but call it {@ItemRef1} for reasons that will be clear below.  And

formula = {@ItemRef}

SortFields(2):  OtherData

I first tried to add {QueryMain.OtherData} as a sort field on its own... but I ran into problems with it.  If I want to assign this field to SortFields(0) in my code, then CrystalReports keeps on throwing an error:

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2147213305
  Message="The sorting already exists"
  Source="RptControllers.dll"
  StackTrace:
       at CrystalDecisions.ReportAppServer.Controllers.SortControllerClass.Add(Int32 IndexToAdd, ISCRSort Sort)
       at CrystalDecisions.CrystalReports.Engine.SortField.set_Field(FieldDefinition value)

I'm not sure how to get around this to allow for assigning random fields... I can't seem to remove one field from the sorting so that I can add it elsewhere in the collection.  Maybe, using Reflection... My workaround however works by creating rearranged sets of SortFields for each sorting.  To prepare for the workaround, create a simple formula: {@SortOtherData1} =

formula = {QueryMain.OtherData}

SortFields(3-7) .. 

Other fields sort on other fields not listed... PriorityOrder1, IsGreen1, TotalScore1, .. etc.

In my code I load up the report...

daMain.Fill(rds.QueryMain)
Dim rpt As New ManagementReport
rpt.SetDataSource(rds)
rpt.SetParameterValue("Param1", paramString1)
rpt.SetParameterValue("Param2", paramDate2)
CrystalReportViewer1.ReportSource = rpt

I have toggle buttons on the form, to select different sort orders:

Sort 1 - is the default sort order.
Sort 2 - By Item Ref
Sort 3 - By Highest Score

The issue I was having was that when I assigned FormulaFieldDefinition {@ItemRef1} to SortFields(0) I would get the COMException (as above) from CrystalReports.  The answer was to make an exact copy of {@ItemRef1}'s code and save as a new formula {@ItemRef2}.  Repeat this for all SortFields.  Now the reason for not using FieldDefinitions for sorting, and converting them to FormulaFieldDefinitions becomes apparent...

If you have assigned a FieldDefinition to a SortField, you cannot assign it to another SortField without removing it first.  But if you create a FormulaFieldDefinition based on the field, then you can have copies of the FormulaFieldDefinition and assign them without the clash.  You simply have to make sure you replace all SortFields in one go...  Something like the following resorts the loaded report without having to reload the data... works like a charm... :-)

[Note: All the SortFields assigned by default end with the number, 1.  And assuming only 5 fields assigned.]

Sub Sort2ByItemRef()
            rpt = CType(CrystalReportViewer1.ReportSource, ManagementReport)
            Dim thisSort As SortFields = rpt.DataDefinition.SortFields
            thisSort(0).Field = rpt.DataDefinition.FormulaFields.Item("ItemRef2")
            thisSort(1).Field = rpt.DataDefinition.FormulaFields.Item("SortOtherData2")
            thisSort(2).Field = rpt.DataDefinition.FormulaFields.Item("IsNotInItemTable2")
            thisSort(3).Field = rpt.DataDefinition.FormulaFields.Item("SortPriority2")
            thisSort(4).Field = rpt.DataDefinition.FormulaFields.Item("SortHighestScore2")
            CrystalReportViewer1 .ReportSource = rpt 
End Sub
Sub Sort3ByItemRef()
            rpt = CType(CrystalReportViewer1.ReportSource, ManagementReport)
            Dim thisSort As SortFields = rpt.DataDefinition.SortFields
            thisSort(0).Field = rpt.DataDefinition.FormulaFields.Item("SortHighestScore3")
            thisSort(1).Field = rpt.DataDefinition.FormulaFields.Item("SortPriority3")
            thisSort(2).Field = rpt.DataDefinition.FormulaFields.Item("IsNotInItemTable3")
            thisSort(3).Field = rpt.DataDefinition.FormulaFields.Item("SortOtherData3")
            thisSort(4).Field = rpt.DataDefinition.FormulaFields.Item("ItemRef3")
            CrystalReportViewer1.ReportSource = rpt
End Sub

I've got it working... Added a new Sub called Sort1Default .. to revert to default sort order (by assigning sort fields ending in '1' in the default order...

And those were the simpler reports... now onto the massive Detailed Customer report...  It's taken a few hours to get my head around these SortFields and dynamically changing them ... no doubt someone is going to tell me I could have done it a simpler way... Do let me know... As it stands, these reports were being run from Access and that was fraught with difficulties because first you have to find the most up-to-date MDB... then check it works with the most up-to-date backend MDB... and then check to see if the queries are up-to-date...  That's many hundreds hours saved in the long term.