CODEWITHSUNDEEP
vb.netsortingdatatableado.net
KJM
KJM

Reputation: 83

DataTable And Two Row Sorting

So the boss comes to me and says "I want the value of each agent and the project on one line and the average of all the other agents on the next line so I can easily see if they are above or below average."

the table looks like this:

dt.Columns.Add("AGENT", GetType(String))
        dt.Columns.Add("PROJECT", GetType(String))
        dt.Columns.Add("Sales", GetType(Integer))
        dt.Columns.Add("Declines", GetType(Integer))
        dt.Columns.Add("Margin", GetType(Integer))

Ok its all good. One row in the datatable is the agent and project. The next row is the average of all the other agents and project like so:

row 1: John Smith, ProjectName, (other column values)

row 2: John Smith, ProjectName & " AVERAGE/TOTAL", (other column values)

The project name is removed in the SSRS report on the AVERAGE/TOTAL line because of space constraints on the piece of paper it is printed on.

I do the sorting by our standard way of sorting a datatable.

Dim dataView As New DataView(dt1)
        dataView.Sort = "AGENT,PROJECT"
        dt1 = dataView.ToTable
        Return dt1

But now the boss has a new requirement later on. He wants to be able to sort by other columns in the table but keep the two rows the (agent/project and the agent/project AVERAGE/TOTAL) together. So in essence he wants to be able to sort not by one row but the two rows together but the sort value could be "AGENT,Margin". Obviously to keep the two rows together I have to find a way to sort the Project value too.

So I am stumped and would appreciate any thoughts you might have. C# ideas are welcome as well.LINQ is fine but it is going to have to become a datatable.

Upvotes: 1

Views: 163

Answers (1)

KJM
KJM

Reputation: 83

so you create two tables. One with the row values one one with the average values. Loop through the row values and then do another loop inside that loop to match project names. Its a hack but it worked. 

If SortValue = "Default" Then
            dt1.Merge(dt)
            Dim dataView As New DataView(dt1)
            dataView.Sort = "AGENT,PROJECT"
            dt1 = dataView.ToTable
        Else
            Dim dataView As New DataView(dt)
            dataView.Sort = SortValue
            dt = dataView.ToTable
            Dim dtCopy As New DataTable
            dtCopy = dt.Clone

            For Each row As DataRow In dt.Rows
                dtCopy.ImportRow(row)
                For i = 0 To dt1.Rows.Count - 1
                    If dt1.Rows(i).Item("PROJECT").ToString.Replace(" AVERAGE/TOTAL", "") = row.Item("PROJECT") And dt1.Rows(i).Item("AGENT") = row.Item("AGENT") Then
                        dtCopy.Rows.Add(dt1.Rows(i).Item("AGENT"), dt1.Rows(i).Item("PROJECT"), dt1.Rows(i).Item("SALES"), dt1.Rows(i).Item("Declines"), dt1.Rows(i).Item("Margin"))
                    End If
                Next
            Next
            dt1 = dtCopy
        End If

Upvotes: 1

Related Questions