Cross-Tab Reports : Dynamically add columns to grid

• Download CrossStar.zip - 4.0 KB
• Download Builder_Design_Pattern.zip - 0.5 KB

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.