Cross Tab Reports

• Download source files and demo project - 49.9 KB

Introduction

I was in need of reporting tools, but the hosting server wouldn’t support it due to the huge investment demanded by reporting tool providers (SQL Server Reporting Services, Crystal Reports...), but still the client needed reports. HTML reports using DataGrid, DataList... are up to some extend useful, but with cross tabs, that takes a lot of code. Component based approach will avoid these coding costs. This scenario really reveals the strength of .NET.

CrossReportWithHead

This is a very simple class, and I don't think it is a beneficiary approach, but one thing, it is very useful for our current project.

Properties:

• GroupColumnName

The name of the field in the source table, and its values that are going to be the side header fields for the report according to which the grouping is done.

• HeaderColumnName

The name of the field in the source table and its values to be put on as header column names for the values obtained from the RepeatColumnName property.

• RepeatColumnName

The name of the field in the source Table that is the data or values for the HeaderColumnName property.

• ItemColumnName

The name of the field in the source table; according to its values (whatever is available in the table), the report is to be generated.

• AvgColumn

It is the boolean property that creates additional fields to obtain the average of the values from the RepeatColumnName property.

Method:

CreateReport which returns a DataTable and the code is depicted below:

Dim crossReportWithHead1 As New _
  crossReportWithHeadcrossReportWithHead1.GroupColumnName = "Month"
crossReportWithHead1.HeaderColumnName = "Year"
crossReportWithHead1.RepeatColumnName = "Price"
crossReportWithHead1.ItemColumnName = "Product"
crossReportWithHead1.AvgColumn = True
crossReportWithHead1.SourceTable = tempTable()

Dim dt As DataTable = crossReportWithHead1.CreateReport
Dim dr As DataRow = dt.NewRow() dt.Rows.Add(dr)
DataGrid1.DataSource = dt DataGrid1.DataBind()
Dim DataGridGrouper1 As New DataGridGrouper DataGridGrouper1.GroupColumn(DataGrid1, 0)