How does sorting work?

Posted on 2006-04-19
Last Modified: 2008-01-09
I have a collection of sort fields and I want to dynamically sort by a specific field....

Sounds easy right?

i have 21 sort fields in all. (and 4 groupings)

So for example I want to sort by the field at index 10. You would think that:
Me.ctlReportDocument.DataDefinition.SortFields(10).SortDirection = SortDirection.AscendingOrder

would do it but it doesn't. I think that as well as sorting index 10 it sorts all the other fields as well hense overwriting the result that sorting on field 10 had.

So is there a way of telling it only to sort on a specific index and to ignore all the other sortfields?

How does it work, does it sort index 0 then index 1 etc... when you call ctlReportViewer.RefreshReport()?
Question by:se402036se
    LVL 100

    Expert Comment

    The index (10) refers to the 10th sorting criteria in the report

    When you say there are 21 sort fields and 4 groups, do you really have 21 fields under the sort expert?

    Crystal first sorts the data by the groups (so it can be broken out into the groups) then sorts on the first sort field provided additional sort fields then provide further sorting but only when previous sorts resulted in 2 or more records with the same value.

    Think of it like a phone book.

    Generally the first grouping is the phone company office
    If there are several cities/towns near each other the book can group by CITY or simply combine them together
    The next group is First Letter of the last name
    First sort field - Last Name
    Next sort field - First Name
    Next sort field - Middle Initial
    From there you could sort by address or phone number

    If you have that many sort fields, I think you want to delete most of them and use a single sort field that is the one used most of the time

    To change a sort field

                    For k = 1 To crRpt.Database.Tables.Count
                          For m = 1 To crRpt.Database.Tables(k).Fields.Count
                                If (UCase$(crRpt.Database.Tables(k).Fields(m).Name) = _
                                                                                    UCase$("YourFIeldName")) Then
                                    crRpt.RecordSortFields(j).Field = crRpt.Database.Tables(k).Fields(m)
                                    crRpt.RecordSortFields(j).SortDirection = crAscendingOrder
                           '         crRpt.RecordSortFields(j).SortDirection = crDescendingOrder
                                End If
                            Next m
                    Next k


    Author Comment

    Hi mlmcc, thanks for that...

    but when I run the line:
    crRpt.RecordSortFields(j).Field = crRpt.Database.Tables(k).Fields(m)

    I get the error:
    "The sorting already exists"

    So the line is really:
    crRpt.RecordSortFields(4).Field = crRpt.Database.Tables(0).Fields(10)
    as there is 4 group so the first sort field is at index 4, and (e.g.) the field i want to sort by is at index 10 of table 0.

    All the fields in my table are already in my DataDefinition.SortFields collection so if I set the one i want to sort to index 4 it tells me "The sorting already exists".... i.e. that field is already in my collection at e.g. index 15 and if I try set it to index 4 it will exist in two places!

    Am i missing something here?

    Thanks for the help, i really appreciate it, i've been at this for ages!!!!

    LVL 100

    Accepted Solution

    You need to remove all the fields from the sorting.  If you don't then there is no way to change the sorting since the field you are trying to add is already a sort choice.

    For sorting - Add a field that generally you want to sort by then maybe add another which could be the KEY field.  You can then change sort 4 and 5 any way you want.


    Author Comment

    ok mlmcc, i think that is the solution i'll have to take.

    I'll implement it when i get to work tomorrow.

    Can i just run one thing past you though? ->

    Do you use CR with Visual Studio? If no then don't worry about the next bit.

    With VS2003 you do not have to clear all the sort fields. You can always set the field you want to sort to the first sort field index hence sorting on the field you want. So it is actually possible to have index 0 and 1 and etc... all using the same sort field. i.e. you can run the code:
    ctlReportDocument.DataDefinition.SortFields(0).Field = ctlReportDocument.Database.Tables(0).Fields(22)
    ctlReportDocument.DataDefinition.SortFields(1).Field = ctlReportDocument.Database.Tables(0).Fields(22)
    and it won't crash.

    But with VS2005 the above code will crash with "the sorting already exists"

    have you found this yourself?

    Again thanks mlmcc, i'll close the Q tomorrow
    LVL 100

    Expert Comment

    I don't recall.  I use VS 6 (VB 6) the pre .Net versions.  There were a couple of reports where the users wanted the ability to select the sort fields.  They could choose 3 fields and my code reset all the sort fields I had built into the report so I am not sure if I ever had that situation where the sort field already existed.

    Interesting that it works with 2003 but not 2005.  Are you using the same version of Crystal? Or are you using CR.Net 2003 and .Net 2005?  If the versions of Crystal are different then maybe it is the Crystal pieces that catch the problem.


    Author Comment

    using CR.NET 2005 with VB2005....

    It's a strange one alright but the thing that bugs me is that I cannot find anyone that had the same problem or any documentation for it..

    oh, well... thanks a lot for the help mlmcc,

    LVL 100

    Expert Comment

    Glad i could help


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now