Cross-Tab Reports : Dynamically add columns to grid





2.00/5 (5 votes)
Sep 10, 2007
2 min read

39262

495
Article describing how to work of girds which needs to add columns dynamically at run time.
Introduction
All of us might have used grids for showing reports in asp .net. Normally when we show a report in grid the no of columns will be fixed. Rows that can come in the reports, may very depends on a no of factors. Cross tab reports are something where the row as well as the no of columns will very as per the condition user provided with. So when you use grids for showing those types of reports, you have to build the columns dynamically.
Background
For a more clear explanation let us work with an example. In this you have a simple database where you have got 'n' no of students from different departments in a table and their marks in another table. When I say they are from different department I mean that the no of paper they will study will also very. Lets say department1 have got 2 paper and department2 got 1 paper. So in the mark sheet of a student from department 1 you can see the mark of both paper1 and paper2. While in the mark sheet of a student from department2 you can see only mark for a single paper.
Using the code
Let's see how we can build our business object for this. We need to have a 'subject' business object for saving his mark, and a 'Student' business object with his name and list of marks. It can be like
So for filling the object we need to fill the data for student first and then make objects of subject and add it to the list in mark list. For cross tab implantation we can give this object as the data source to the grid. Then before binding we can add columns that are needed dynamically.
For bound field we can directly assign its header text and data field.
Private Function GetBoundColumn(ByVal hText As String, ByVal datafield As String) As BoundField Dim newColumn As BoundField newColumn = New BoundField() With newColumn .HeaderText = hText .DataField = datafield End With Return newColumn End Function
For template columns we need to make use of a class implementing ITemplate interface.
Private Function GetTemplateColumn(ByVal dSource As List(Of Types), ByVal colId As Integer) As TemplateField Dim newColumn As TemplateField newColumn = New TemplateField() With newColumn .HeaderTemplate = New TemplateFactory("M" & colId, DataControlRowType.Header, 0) .ItemTemplate = New TemplateFactory("", DataControlRowType.DataRow, colId) End With Return newColumn End Function Public Class TemplateFactory Implements ITemplate Dim _hdrName As String Dim _rowType As DataControlRowType Dim _colId As Integer Public Sub New(ByVal hdrName As String, ByVal rowType As DataControlRowType, ByVal colId As Integer) _hdrName = hdrName _rowType = rowType _colId = colId End Sub Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) Implements System.Web.UI.ITemplate.InstantiateIn If (_rowType = DataControlRowType.Header) Then Dim oLabel As New Label() oLabel.Text = _hdrName oLabel.ForeColor = Drawing.Color.White container.Controls.Add(oLabel) ElseIf (_rowType = DataControlRowType.DataRow) Then Dim oTextBox As New TextBox() oTextBox.ID = _colId AddHandler oTextBox.DataBinding, AddressOf TextBox1_DataBinding container.Controls.Add(oTextBox) End If End Sub Protected Sub TextBox1_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Dim txt As TextBox Dim gvRow As GridViewRow Dim i As Integer txt = CType(sender, TextBox) gvRow = CType(txt.NamingContainer, GridViewRow) i = CType(txt.ID, Integer) If (CType(gvRow.DataItem, TypesList).TypeList.Count > i) Then txt.Text = DataBinder.Eval(CType(gvRow.DataItem, TypesList).TypeList(i), "Name") End If End Sub End Class
Points of Interest
If we have many reports, that needs to have a cross-tab implantation, then we have go for a 'Builder' design pattern for easy implementation. More details about this can be find in the attachment.
History
Keep a running update of any changes or improvements you've made here.