Click here to Skip to main content
13,259,771 members (54,307 online)
Click here to Skip to main content
Add your own
alternative version

Stats

17.6K views
12 bookmarked
Posted 10 Jan 2008

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

, 10 Jan 2008
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

               <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

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.


You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralA lot of duplicate code Pin
Mufaka10-Jan-08 14:47
memberMufaka10-Jan-08 14:47 

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

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

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