Print Page | Close Window

Sorting reports dynamically

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1389
Printed Date: 29 Apr 2024 at 2:15am


Topic: Sorting reports dynamically
Posted By: Ontario
Subject: Sorting reports dynamically
Date Posted: 27 Sep 2007 at 2:28am
My application, written in VB.NET 2005, uses reports designed in CR XI.
 
The user should be able to re-sort certain areas of the reports. Thus opens up 2 important questions:
 
1. How can I add SortFields programatically? The SortFields-Collection doesn't provide any Add-method...
 
2. Using predefined sortfields (defined in CR XI) in subreports reveals another problem: The subreports are resorted in background but aren't updated in the CRViewer. How do I force the subreports to be updated?
 
I tied something like this:
 

Dim rptSubBericht As ReportDocument = rptBericht.OpenSubreport(rptBericht.Subreports(intReport).Name)

If rptSubBericht.Database.Tables(0).Location.Substring(rptSubBericht.Database.Tables(0).Location.LastIndexOf(".") + 1) = View Then

For intFeld As Integer = 0 To 2

Dim sfdFeld As SortField = rptSubBericht.DataDefinition.SortFields(intFeld)

With sfdFeld

If Not SortierFelder(intFeld) Is Nothing Then

.Field = rptSubBericht.Database.Tables(0).Fields(SortierFelder(intFeld))

End If

.SortDirection = SortDirection.AscendingOrder

End With

Next

CrystalReportViewer1.ReportSource = rptBericht

This does not(!) update the subreports within the Viewer.
 
Thx
Ontario


Replies:
Posted By: hilfy
Date Posted: 27 Sep 2007 at 2:43pm

When I need this type of functionality, I set up a parameter that says which field is sorted.  This can be a plain text parameter that gets translated in a formula.  I then sort on the formula in the report.  It looks something like this:

If {?SortOrder} = 'Loan Number' then
  {LOAN.LOAN_NUMBER}
else if {?SortOrder} = 'Posting Date' then
  ToText({LOAN.LOAN_POSTING_DATE}, 'yyyyMMdd')
else if {?SortOrder} = 'Loan Rematch Date' then
  ToText({LOAN.REMATCH_DATE}, 'yyyyMMdd')
else //default value
  {LOAN.LOAN_NUMBER}

If your users are going to be able to specifiy ascending or descending order, you'll have to set the SortDirection in your code, though.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Ontario
Date Posted: 01 Oct 2007 at 1:03am
Thanks for that idea, but this wont work in my case. Users should be able to select up to 3 out of 99 properties to sort. Neither your mentioned if block nor a case structure would be suitable for that. And in addition my reports contain up to 3 subreports - I would get lost in handling all those parameters.
 
Isn't there any way to get a subreport rebuild after binding it to a Viewer.ReportSource? (If I sort the subreport before(!) setting the ReportSource property it's perfectly sorted, though.)

Posted By: Ontario
Date Posted: 01 Oct 2007 at 7:48am
Ok, the workaround I found myself to rebuild subreports during runtime as well is to use a second, new report temprorarily:
 
Dim rptTmp As New ReportDocument
rptTmp.Load(strReportPath & "\ReportWithSubReports.rpt")
 
'Code to configure the report and its subreports goes here
 
rptDisplayedReport = rptTmp
CrystalReportViewer1.ReportSource = rptDisplayedReport
 
This isn't a nice coding style but helps for the moment.

Posted By: Joe_G
Date Posted: 03 Feb 2008 at 9:06pm

' VB 2005/Crystal XI 2008

' my reports has three fields Lastname, Firstname, address

' in my report I have added the address as my default sort field

' the following code allows me to change the sort field to another field.

' if I try to sort on address I get an error "sort field already exists.

' if I remove the default sort filed my code gives me an error.

' my problem now is how can I sort on multiple fields ????

 

 

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

Dim sDatabaseFile As String = "c:\Data\DatabaseFile.mdb"

Dim sTableName As String = "myTable"

Dim crDatabaseFieldDefinition As CrystalDecisions.CrystalReports.Engine.DatabaseFieldDefinition

crReportDocument.Load("C:\reports\myReport.rpt")

crReportDocument.DataSourceConnections.Item(0).SetConnection(sDatabaseFile, sTableName, False)

crDatabaseFieldDefinition = crReportDocument.Database.Tables.Item(0).Fields.Item("LastName")

crReportDocument.DataDefinition.SortFields.Item(0).Field = crDatabaseFieldDefinition

crReportDocument.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.Shared.SortDirection.DescendingOrder

CrystalReportViewer1.ReportSource = crReportDocument


Print Page | Close Window