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.
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()
.HeaderText = hText
.DataField = datafield
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()
.HeaderTemplate = New TemplateFactory("M" & colId, DataControlRowType.Header, 0)
.ItemTemplate = New TemplateFactory("", DataControlRowType.DataRow, colId)
Public Class TemplateFactory
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
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
ElseIf (_rowType = DataControlRowType.DataRow) Then
Dim oTextBox As New TextBox()
oTextBox.ID = _colId
AddHandler oTextBox.DataBinding, AddressOf TextBox1_DataBinding
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")
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.
Keep a running update of any changes or improvements you've made here.