User Sortable Columns in Crystal Reports
This article is for user storable columns in crystal reports, I gone through several articles and I could not find similar solution which I am looking for.
Finally I am able to fix it (I gathered some information and coding from internet).
This might not be a good article but some one can use it.
With this article you will know how to create the user sortable columns in crystal reports in Visual Studio .Net application;
the user can click any column with in the report to sort ascending and descending the records.
Here I am using the session state to store the ascending and descending information, it may not be the right way to do it but it works fine for me.
Please Find the attached Zip file for total Functional flow with Print Screens.
Here is the Code:
ASPX.cs:
Dim connectionstring As String = "User Id=appuser;Password=uapp1;Data Source=TRKTEST;"
connection = New OracleConnection(connectionstring)
connection.Open()
Dim query As String
<p> query = "SELECT ""Portal_Module"" AS APPLICATION,</p> <p>SCR_ID AS ""SCR #"", ""SCR_Type"" AS ""SCR Type"", ""Title"", ""State_col""</p> <p>AS STATE, ""Sub_Portal_Module"" AS ""Module"", ""Rqst_Rls_Date"" </p><p>AS ""Request Release </p>Date"", ""Target_Rls_Date"" AS ""Target Release <p>Date"", ""Re_planned_Rls_Date"" AS ""Re-Planned Release </p><p>Date"", ""Actual_Rls_Date"" AS ""Actual Release Date"", ""config"" v</p><p>AS ""knownIssue"" FROM(TEST_MLP.CHRQVTEST_MLP)WHERE (""Actual_Rls_Date"" </p><p>BETWEEN TO_DATE('9/18/2007', 'MM/DD/YYYY') AND </p>TO_DATE<p>'9/20/2007', 'MM/DD/YYYY')) AND (""SCR_Type"" = 'Change Request' </p><p>OR ""SCR_Type"" = 'Defect')ORDER BY APPLICATION, ""SCR Type"", ""Module"""</p>
dataadapter = New OracleDataAdapter(query, connection)
This is the data set the wich we created intially to interact with the datasae.
Dim dSet As New MLP
dataadapter.Fill(dSet, "TEST_MLP.CHRQVTEST_MLP")
Dim i As Integer = 0
conn = New OracleConnection(connectionstring)
reportTblheader.Rows(0).Cells(0).Text = "Release Package Report For Bsc.com"
Dim ReportName As String
ReportName = "MLPReports\ReleaseCategory\ReleasePackage\MLPList.rpt"
This is the method from where you can sort the columns by passing DataSet and ReportName.
Sort(dSet, ReportName)
Public Sub Sort(ByVal ds As DataSet, ByVal ReportName As String)
Dim myReportdoc As CrystalDecisions.CrystalReports.Engine.ReportDocument
myReportdoc = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
myReportdoc.Load(Server.MapPath(ReportName))
Dim FieldDef As FieldDefinition
Dim name As String
If Not Request.QueryString.ToString() = "" Then
name = Request.QueryString.Item(0).ToString()
Else
name = ""
End If
If name = "SCR" Then
name = "SCR #"
End If
If Not IsPostBack = True Then
Dim Val As String
Dim Order As String
If name = "APPLICATION" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Here we are strong session value in page loads by Ascending or Descending for Application
Val = CType(Session("APPLICATION"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("APPLICATION") = Order
End If
If name = "SCR #" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("SCR #"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("SCR #") = Order
End If
If name = "SCR Type" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("SCR Type"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("SCR Type") = Order
End If
If name = "Title" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("Title"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("Title") = Order
End If
If name = "STATE" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("STATE"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("STATE") = Order
End If
If name = "Module" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("Module"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("Module") = Order
End If
If name = "Request Release Date" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("Request Release Date"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("Request Release Date") = Order
End If
If name = "Target Release Date" Then
FieldDef = myReportdoc.Database.Tables(0).Fields("Target Release Date")
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("Target Release Date"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("Target Release Date") = Order
End If
If name = "Actual Release Date" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("Actual Release Date"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("Actual Release Date") = Order
End If
If name = "knownIssue" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("knownIssue"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("knownIssue") = Order
End If
If name = "Re-Planned Release Date" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
Val = CType(Session("Re-Planned Release Date"), String)
If Val = "Asc" Or Val = "" Then
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.DescendingOrder
Order = "Des"
Else
myReportdoc.DataDefinition.SortFields(0).SortDirection = CrystalDecisions.[Shared].SortDirection.AscendingOrder
Order = "Asc"
End If
Session("Re-Planned Release Date") = Order
End If
End If
If Not name = "" Then
FieldDef = myReportdoc.Database.Tables(0).Fields(name)
myReportdoc.DataDefinition.SortFields(0).Field = FieldDef
End If
myReportdoc.SetDataSource(ds.Tables(0))
ProductReportViewer.ReportSource = myReportdoc
To hide the Group tree
ProductReportViewer.DisplayGroupTree = False
ProductReportViewer.HasToggleGroupTreeButton = False
ProductReportViewer.HasCrystalLogo = False
End Sub