How to sort numerically

[Former Member]

I use Crystal Reports 2008 with Visual Studio 2005, .NET 2.0

In a report I have a column for "time until due". Overdue is shown with minus sign.

I want to sort this column, but the result is that it is sorted as text, e.g.

-73

-7

-5

-49

-47

-142

Can I, by using e.g. som conversion formula, make it sort numerically? I want the list above to be displayed as

-142

-73

-49

-47

-7

-5

I tried to use cdbl(sorting_field) but it didn't work.

[former_member183750 · Accepted solution]

Please post to the Crystal Reports Design forum;

Ludek

[Former Member · Accepted solution]

OK, I have done that.

However, I have implemented my solution so that I have a template .rpt that I adapt in software, through the ReportClientDocument model, depending on which report the user is running. The set of reports are defined as .xml files, one pr. report. So I might have to come back to you for help about how to implement the design forum's answer in software. So I leave this thread open just a little while. Thank you for your reply anyway.

[Former Member · Accepted solution]

In the Sort Expert help I see that a field of type "Multiple character string field" is sorted as text. In my .rpt, my field is an IFieldObject, according to the Properties window, and I guess it is a "Multiple character string field". I suppose I have to set the FieldFormat to NumericFormat in software. And then hope it will sort in numeric order.

Do you have any helping hints on how to do this? (I have struggeled a lot with similar things before ...)

[Former Member · Accepted solution]

Hello again

This is an extract of my code:

        Dim sortField As CrystalDecisions.ReportAppServer.DataDefModel.Field = CType(resultFields.FindField(pstrSortField, CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleNorwegianBokmal), Field)

        ' This line is the tricky part:
        sortField.Type = CrFieldValueTypeEnum.crFieldValueTypeInt32sField

        Dim sortController As SortController = dataDefController.SortController
        If sortController.CanSortOn(sortField) Then
            Dim newSort As Sort = New SortClass()
            newSort.SortField = sortField
            Select Case pstrSortDirection
                Case "DESC"
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionDescendingOrder
                Case Else
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionAscendingOrder
            End Select
            sortController.Add(-1, newSort)
        End If

Everything works except the "tricky line".

In the "tricky line" I try to make the field a numeric instead of a String (which is default). However, it doesn't work. Do I need to use some controller to set the Type?

Best regards,

- Kjell Arne

[Former Member · Accepted solution]

The data actually bound to this report field with the ReportDocument.SetDataSource are all text strings.

If I set one of the fields to be a crFieldValueTypeInt32sField and then load it with text strings like '-135',

what will then happen? If there is no implicit conversion, how can I achieve what I am trying to do?

My .rpt is generic for a lot of reports, so I can't set the field to be numeric there. Also, the actual data are

stored in a separate class in Visual Basic (made by me) and bound to the report in run-time.

Must I convert the datavalues from string to number in my code before binding it to the ReportDocument?

(If so, I don't know how to do it, because the datatype in my internal structure is String).

Maybe I must create some kind of CR formula to cause the type conversion when the data are loaded

into the ReportDocument? If so, what kind of formula would that be?

[former_member183750 · Accepted solution]

Well, as these are strings, sorting them as if they were numerics, will certaily require a formula. Use a formula that will convert the string to a number, then sort that.

Ludek

[Former Member · Accepted solution]

Yes, Ludek, I agree. But this is the area where I was hoping to get some help...

You see, in my generic .rpt file, I have the Database Fields and the Formula Fields listed on the left in the report designer. And in code I add sort fields. This makes a lot of options as to what field to update, where, and how.

In the report designer, in one of the Formula Fields (called Field11), just to test, I try to enter the formula

ToNumber ({DataGenericNoPrefix.Field11})

When I run the report, this is the field that gets the strings containing numbers.

(DataGenericNoPrefix is the name of my Visual Basic class that holds the report data, and its members (Field0, Field1, ...) are

shown in the Database Fields section of the report designer).

Then, I get the following exception from Crystal Reports:

Error: Error in File TemplateForPrinting {2F80A116-A37E-4B43-ACDE-9B0855169ACE}.rpt: Invalid object format name.

( The scary part here is that even if I take out the formula again and save, I get the same error. I need to "Undo checkout" from source control before it works again. )

I wonder

1. What field(s) in the report designer should I update (from code) with the ToNumber formula (I assume the field listed in Formula Fields, to be found via DataDefinition)

2. What field(s) in the report designer should I update (from code) with a new Type (or FormatType or ... ?)

3. How to do the update (Using a DataDefController or some other controller, or if it can be set directly).

4. What Type should it be (there are a lot of numeric options, e.g. CrFieldValueTypeEnum.crFieldValueTypeInt32sField)

I also need to take care of the sort fields using the sort controller. What about formula and/or Type here? Must I clone the field before it is added to the DataDefModel.Sort?

I would highly appreciate at least some pseudo code showing the sequence to do things and the method to find the proper object and how to update it correctly. Or maybe you could point me in the right direction in the documentation?

Maybe this scenario could be shown as a case in the CR documentation; how to sort a column numerically (at runtime, using code), when the .rpt is originally set up for text?

Hopeful regards,

- Kjell Arne

[Former Member · Accepted solution]

I still havenu2019t put the pieces together

For column headings, in my .rpt file, I use formula fields (shown under u201CFormula Fieldsu201D in the Field Explorer in the CR Designer) in the Page Header section of the report. In run-time, I set these to proper values by accessing via the DataDefinition class:

rptDoc.DataDefinition.FormulaFields.Item(pstrFieldName).Text = """" & pstrFieldText & """"

where rptDoc is type CrystalDecisions.CrystalReports.Engine.ReportDocument

pstrFieldName above is the name as seen in the u201CFormula Fieldsu201D part of the Field Explorer

In the Details section (where I want to do the sorting), I have fields from the Database Fields section of the Field Explorer. I can get to e.g. conditional formatting formulas through the ReportDefinition class (example for Hyperlink formula):

    Private Sub SetHyperlinkFormula(ByVal pstrFieldName As String, ByVal pstrFormula As String)
        ' crRepDef is type CrystalDecisions.ReportAppServer.ReportDefModel.ISCRReportDefinition
        Dim col As ISCRReportObject = CType(crRepDef.FindObjectByName(pstrFieldName), ISCRReportObject)
        Dim lstr As String = col.Format.ConditionFormulas.Formula(CrObjectFormatConditionFormulaTypeEnum.crObjectFormatConditionFormulaTypeHyperlink).Text
        If lstr Is Nothing Then
            'Create a new formula for this object (i.e. this column in the report)
            Dim cf As ConditionFormula = New ConditionFormula
            Dim colTemp As ISCRReportObject = col
            cf.Text = pstrFormula
            cf.CopyTo(colTemp.Format.ConditionFormulas.Formula(CrObjectFormatConditionFormulaTypeEnum.crObjectFormatConditionFormulaTypeHyperlink))
            rptDoc.ReportClientDocument.ReportDefController.ReportObjectController.Modify(col, colTemp)
        Else
            col.Format.ConditionFormulas.Formula(CrObjectFormatConditionFormulaTypeEnum.crObjectFormatConditionFormulaTypeHyperlink).Text = pstrFormula
        End If
    End Sub

Here, the pstrFieldName corresponds to the Name attribute seen in the Properties window in the CR Designer.

I suppose I need to do a change with such a field.

Questions:

1. Do I need to typecast it to some other type?

2. Or must I access it in a different way?

3. What should I do to make it a numeric field (what should change and how)

4. Where do the ToNumber formula go for converting the data?

Best regards,

- Kjell Arne

[Former Member · Accepted solution]

I think I am close to the goal now, but have a remaining problem: How to set the IsRecurring property of a FormulaField to True.

More details:

My overall concept is:

1. When I want to sort a column as text, I find the field through the DataDefinition.ResultFields and add it to the Sort collection using the DataDefController.SortController

2. When I want to sort a column as numbers, I make a new FormulaField, set its Text property to something like ToNumber({})

Private Sub AddSort(ByRef sf As ReportAdaptation.SortingField)
        ' Add one (more) sorting field to the report
        Dim rcd As ISCDReportClientDocument = CType(rptDoc.ReportClientDocument, ISCDReportClientDocument)
        Dim dataDefController As DataDefController = rcd.DataDefController
        Dim resultFields As Fields = dataDefController.DataDefinition.ResultFields
        Dim sortField As CrystalDecisions.ReportAppServer.DataDefModel.Field
        If Not String.IsNullOrEmpty(sf.Formula) Then
            ' We want to sort using a formula - normally a data conversion formula like ToNumber({my_data.Field})
            Dim ff As New CrystalDecisions.ReportAppServer.DataDefModel.FormulaField
            ff.Text = sf.Formula
            Select Case sf.Format
                Case ReportAdaptation.crystal_field_type.enumNUMERIC
                    ff.Type = CrFieldValueTypeEnum.crFieldValueTypeNumberField
                Case Else
                    ff.Type = CrFieldValueTypeEnum.crFieldValueTypeStringField
            End Select
            sortField = CType(ff, CrystalDecisions.ReportAppServer.DataDefModel.Field)
        Else
            sortField = CType(resultFields.FindField(sf.Field, CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleNorwegianBokmal), Field)
        End If

        Dim sortController As SortController = dataDefController.SortController
        If sortController.CanSortOn(sortField) Then
            Dim newSort As Sort = New SortClass()
            newSort.SortField = sortField
            Select Case sf.Order
                Case "DESC"
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionDescendingOrder
                Case Else
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionAscendingOrder
            End Select
            sortController.Add(-1, newSort)
        End If

    End Sub

[Former Member]

(I had major problems with the formatting of the reply above (because of copy from Microsoft Word?), so I continue here ...)

My problem is that before I can add my FormulaField to the Sort collection, I must test

If sortController.CanSortOn(sortField)

and unfortunately it can not, apparently because my formula field newly created has the IsRecurring property set to False. And it is ReadOnly.

How can I make this property True?

[Former Member]

I keep trying and trying ...

I can achieve what I want using the .rpt file (the Designer). My goal is to achieve the same from code.

This is what I do in the Designer:

1. Add a new formula field by a right-click on the Formula Fields section of the Field Explorer, select New...

2. The Formula Editor comes up, and I "hard-code" a formula like ToNumber(my_field)

3. I use the Sort Expert to add my new formula field to the sorting

I notice that the formula field doesn't have to be a part of the report output (i.e. the Details section). The sorting works anyway.

So my challenge is to replicate this in code. I have looked in a lot of Crystal Reports documentation, but haven't been able to find a direct hit on this subject.

I am able to add a formulafield:

            Dim rcd As ISCDReportClientDocument = CType(rptDoc.ReportClientDocument, ISCDReportClientDocument)
            Dim ff As New CrystalDecisions.ReportAppServer.DataDefModel.FormulaField
            ff.Name = lstrFormulaFieldToUseForSorting
            ff.Text = sf.Formula
            Select Case sf.Format
                Case ReportAdaptation.crystal_field_type.enumNUMERIC
                    ff.Type = CrFieldValueTypeEnum.crFieldValueTypeNumberField
                Case Else
                    ff.Type = CrFieldValueTypeEnum.crFieldValueTypeStringField
            End Select
            rcd.DataDefController.FormulaFieldController.Add(ff)

I assume that the Text property is where the formula should go.

I can also typecast the formulafield:

        Dim sortField As CrystalDecisions.ReportAppServer.DataDefModel.Field
        sortField = CType(ff, CrystalDecisions.ReportAppServer.DataDefModel.Field)

So, the remaininig piece is to add it to the sorting scheme:

 
        Dim sortController As SortController = dataDefController.SortController
        If sortController.CanSortOn(sortField) Then
            Dim newSort As Sort = New SortClass()
            newSort.SortField = sortField
            Select Case sf.Order
                Case "DESC"
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionDescendingOrder
                Case Else
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionAscendingOrder
            End Select
            sortController.Add(-1, newSort)
        End If
 

However, the CanSortOn test fails.

[Former Member]

I finally made it!

(With no help, I might add).

For the benefit of other readers I include the solution I found.

The design thought is that

- for textual sorting, I just find the column to sort on in the details section and add it to the sort structure

- for numeric sorting, I create a formula field with the proper conversion formula and add this to the sort structure

The formula field does not have to be added to any section of the report

Here is the code I have been looking for:

(in my next reply because I have problems with the formatting of the reply)

If I don't make it readable in the next reply, paste it into Visual Studio for auto formatting

Best regards,

Kjell Arne Kamben

[Former Member]

 
    Private mintSpecialSortsInUse As Integer = 0

    Private Sub AddSort(ByRef mySortField As ReportAdaptation.SortingField)
        ' Add one (more) sorting field to the report
        ' rptDoc is the .rpt file loaded in a CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim rcd As ISCDReportClientDocument = CType(rptDoc.ReportClientDocument, ISCDReportClientDocument)
        Dim dataDefController As DataDefController = rcd.DataDefController
        Dim resultFields As Fields = dataDefController.DataDefinition.ResultFields
        Dim sortField As CrystalDecisions.ReportAppServer.DataDefModel.Field
        If Not String.IsNullOrEmpty(mySortField.Formula) Then
            ' We want to sort using a formula - normally a data conversion formula like ToNumber({my_data.Field})
            mintSpecialSortsInUse += 1
            Dim lstrFormulaFieldToUseForSorting As String = "SpecialSort" & mintSpecialSortsInUse.ToString
            ' Create the FormulaField to sort on and add it to the Formula Fields
            rcd.DataDefController.FormulaFieldController.AddByName(lstrFormulaFieldToUseForSorting, mySortField.Formula, CrFormulaSyntaxEnum.crFormulaSyntaxCrystal)
            ' Get the FormulaField out of the Formula Fields again, and add make it a sortField
            sortField = CType(rcd.DataDefController.FindFieldByFormulaForm("{@" & lstrFormulaFieldToUseForSorting & "}"), CrystalDecisions.ReportAppServer.DataDefModel.Field)
        Else
            ' Use one of the regular columns for sorting (mySortField.Field is a field found in the Details section of the .rpt file)
            sortField = CType(resultFields.FindField(mySortField.Field, CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleNorwegianBokmal), Field)
        End If

        ' Add the sortField to the sorting regime
        Dim sortController As SortController = dataDefController.SortController
        If sortController.CanSortOn(sortField) Then
            Dim newSort As Sort = New SortClass()
            newSort.SortField = sortField
            Select Case mySortField.Order
                Case "DESC"
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionDescendingOrder
                Case Else
                    newSort.Direction = CrSortDirectionEnum.crSortDirectionAscendingOrder
            End Select
            sortController.Add(-1, newSort)
        End If

    End Sub