Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Source Code - Cross-tab processing with the information from the stored procedure
(Page 4 of 4 )
The previous section provided the source code for dealing with table data source. Now we shall deal with the stored procedure as the data source. The following is the complete source code for cross-tab engine towards stored procedure.
Private Function getCrossTab_LevelSingle_ForSP() As DataTable
Dim SPName As String = ObjectName
Dim SmryType As OperationType = OperationType.Sum
Dim dtReport As New DataTable
Dim dtSPOutput As DataTable = SPgetDataTable(SPName)
'getting distinct values for columns
Dim dtColumns As New DataTable
dtColumns.Columns.Add(ColFieldName)
Dim dvColumns As New DataView(dtSPOutput)
dvColumns.Sort = ColFieldName
Dim prevColValue As String = "0"
For Each drColumn As DataRowView In dvColumns
If prevColValue <> drcolumn(ColFieldName) Then
dtColumns.Rows.Add(New Object() {drColumn(ColFieldName)})
prevColValue = drColumn(ColFieldName)
End If
Next
'adding columns to the report data table
dtReport.Columns.Add("Details")
For Each drColumn As DataRow In dtColumns.Rows
dtReport.Columns.Add(drColumn(0))
Next
'additional blank columns
For i As Integer = 1 To AdditionalBlankColumns
dtReport.Columns.Add(New DataColumn)
Next
'getting distinct values for Rows
Dim dtRows As New DataTable
dtRows.Columns.Add(RowFieldName)
Dim dvRows As New DataView(dtSPOutput)
dvRows.Sort = RowFieldName
Dim prevRowValue As String = "0"
For Each drRow As DataRowView In dvRows
If prevColValue <> drRow(RowFieldName) Then
dtRows.Rows.Add(New Object() {drRow(RowFieldName)})
prevColValue = drRow(RowFieldName)
End If
Next
'adding rows to the report data table
For Each drRowField As DataRow In dtRows.Rows
Dim drReport As DataRow = dtReport.NewRow
drReport("details") = drRowField(RowFieldName)
For Each drColumn As DataRow In dtColumns.Rows
Dim dvSmryCalc As New DataView(dtSPOutput)
dvSmryCalc.RowFilter = RowFieldName & "='" & drRowField(RowFieldName) & "' and " & ColFieldName & "='" & drcolumn(ColFieldName) & "'"
Dim sum As Double = 0
For Each drSmryCalc As DataRowView In dvSmryCalc
sum += drSmryCalc(SummaryFieldName)
Next
drReport(drColumn(ColFieldName)) = sum
Next
dtReport.Rows.Add(drReport)
Next
Return dtReport
End Function
Summary
An upcoming article of mine will give you a full explanation for both of the above routines, as it would really cross too many sections. Let us discuss some of the issues of this custom control:
- I currently implemented only a few operation types, as it would otherwise go beyond the limits of the article.
- I currently didn’t implement any data grid events. It would be very helpful to you if you really want to work with totals, sub-totals, grand totals or column wise totals.
- The current control works only with SQL Server at the moment. You need to modify all the core database routines to make it flexible enough to work with any database.
- I didn’t implement any styling for the data grid.
- It works only for a single level at the top and a single level at the left. You can expect multiple levels in my upcoming articles.
- I used this parameter cache method just to ease my calling. You can work with your own approach as well.
- If you are familiar with Data Access blocks or Enterprise Application blocks, I request that you replace (or modify) my core database routines with those types of routines in blocks. They are really suitable for enterprise use.
- Note that this is a very small trial of mine which I would like to share the same with you all.
- I strongly suggest you test the control thoroughly before moving it into production use.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
