Click here to Skip to main content
Click here to Skip to main content

User on click Sortable Columns for Crystal reports in Visual Studio .net,ASP.Net

, 10 Jan 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
In Crystal Reports On Clickig the cloumn name records will be sorted accordingly by Ascending or Descending

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

               

query = "SELECT ""Portal_Module"" AS APPLICATION,

SCR_ID AS ""SCR #"", ""SCR_Type"" AS ""SCR Type"", ""Title"", ""State_col""

AS STATE, ""Sub_Portal_Module"" AS ""Module"", ""Rqst_Rls_Date""

AS ""Request Release

Date"", ""Target_Rls_Date"" AS ""Target Release

Date"", ""Re_planned_Rls_Date"" AS ""Re-Planned Release

Date"", ""Actual_Rls_Date"" AS ""Actual Release Date"", ""config"" v

AS ""knownIssue"" FROM(TEST_MLP.CHRQVTEST_MLP)WHERE (""Actual_Rls_Date""

BETWEEN TO_DATE('9/18/2007', 'MM/DD/YYYY') AND

TO_DATE

'9/20/2007', 'MM/DD/YYYY')) AND (""SCR_Type"" = 'Change Request'

OR ""SCR_Type"" = 'Defect')ORDER BY APPLICATION, ""SCR Type"", ""Module"""

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Prasad Aluru
Software Developer Blue Shield Califronia
United States United States
Hi,

My Name Is Prasad Aluru and I am a .Net Developer.
Education:
MS From Canada
Masters In Computer Applications(From Kakatiya University- India)

I would like to read books and Articles, making friends , watching Movies and listening Music.


Comments and Discussions

 
GeneralA lot of duplicate code PinmemberMufaka10-Jan-08 14:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150414.1 | Last Updated 10 Jan 2008
Article Copyright 2008 by Prasad Aluru
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid