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

• Download Article - 403.65 KB

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