Description: The hierarchical report displays 3 different grids. The first grid displays sales totals, grouped by territory, and filtered by year. The second grid displays sales totals grouped by employee, within a specific territory - namely, the territory selected in the first grid. The third grid shows information on a particular employee - namely, the employee selected in the second grid.
Overview: Three DataGrids are set up on this page, each with their own data source, retrieved from three separate stored procedures. Only the data source for the first grid, "Sales By Territory" is bound at Page Load and does not change; the other 2 grids are hidden until the user selects the Territory, and then the Employee. When the user chooses which Territory to view, the second grid, "Sales for [Territory]" becomes visible, and is bound to the results of the stored procedure "GetEmployeeSalesByTerritory", which accepts the Territory Name as a parameter. Similarly, when the user chooses which Employee to view, the third grid, "Employee Info for [Employee ]" becomes visible, and is bound to the results of the stored procedure "GetEmployeeByID", which accepts the Employee's ID as a parameter. Should the user click on the Territory grid when the Employee grid is visible, the Employee grid will hide, and the Employee Sales by Territory grid will refresh with the new data.
Additionally, this report demonstrates how to implement interactive sorting and paging using ASP.NET.
Sorting is enabled for each column in the first two DataGrids. The sortable columns can be sorted in ascending or descending order. Sortable columns are displayed as red and underlined when the user hovers over the column header.
Paging is also enabled in the first two DataGrids, so that a limited number of records can be displayed on each page, with the ability to navigate through the pages of data. Links to previous and next pages are displayed just above the footer section of the DataGrids. In the footer section, below the navigation links, a text field shows how many pages total there are, as well as which page the user is on.
Implementation Notes:
In order for the user to be able to make a selection from one of the grids, and to be able to pass that information to the codebehind, a Template column is used as the first column in the top two DataGrids. A Template column is used to create a column with a customized control layout, including custom appearances for the heading section, the footer section, and the items section of the column. In this example, a LinkButton within the DataGrid Template column captures the user input - namely, the Territory or Employee selected.
<asp:templatecolumn sortexpression="Territory" headertext="Territory Name"> <itemtemplate> <asp:linkbutton oncommand="TerritoryGrid_Click" commandname="TerritoryDrill" commandargument='<%# DataBinder.Eval(Container, "DataItem.TerritoryDescription") %>' cssclass="hier" runat="server" id="Territorylnk" text='<%# DataBinder.Eval(Container, "DataItem.TerritoryDescription") %>' </asp:linkbutton> </itemtemplate> <itemstyle width="200px"></itemstyle> </asp:templatecolumn>
There are three main parts to the implementation of the LinkButton within the Template column: the OnCommand attribute, the CommandName attribute, and the CommandArgument attribute. The OnCommand attribute specifies which method should be run when the LinkButton's text is pressed. The CommandName and CommandArgument attributes hold data that can be passed as parameters to the method. In the above code snippet, the CommandArgument receives the value of the "TerritoryDescription" field of the item selected by the user. The TerritoryGrid_Click method receives this value and passes it to the BindList_EmpTerr method, which is a method that binds the data for the second grid.
Protected Sub TerritoryGrid_Click(ByVal sender As [Object], ByVal e As CommandEventArgs)
Dim territoryName As String = e.CommandArgument.ToString().Trim()
' Put the second grid back to the first page
EmployeeTerritoryGrid.CurrentPageIndex = 0
' Show the table with the second grid, and update the headers
ETTableColumn.Visible = True
ETHeader.Text = territoryName
BindList_EmpTerr(territoryName)
' Hide the 3rd table if it was visible
EmpTableColumn.Visible = False
End Sub 'TerritoryGrid_Click
In the second grid, both the EmployeeID and the EmployeeName are passed into the function ETGrid_Click. The EmployeeName field is used for display in the third grid's Header section, and the EmployeeID field is used to pass in to the BindList_Emp method, which is a method that binds the data for the third grid.
<asp:templatecolumn sortexpression="EmployeeName" headertext="Employee Name">
<itemtemplate>
<asp:linkbutton oncommand="ETGrid_Click"
commandargument='<%# (String) DataBinder.Eval(Container, "DataItem.EmployeeName") +
"," + DataBinder.Eval(Container, "DataItem.EmployeeID").ToString() %>'
cssclass="hier" runat="server"
id= "lnkEmployeeTerritory" text='<%#
DataBinder.Eval(Container, "DataItem.EmployeeName") %>'> </asp:linkbutton>
</itemtemplate>
<itemstyle
width= "150px"></itemstyle>
</asp:templatecolumn>
Protected Sub ETGrid_Click(ByVal sender As [Object], ByVal e As CommandEventArgs)
Dim cmdArgs As String() = e.CommandArgument.ToString().Split(","c)
Dim emploName As String = cmdArgs(0)
Dim employeeID As Integer = Convert.ToInt32(cmdArgs(1))
' Show the table with the 3rd grid, and update the headers
EmpTableColumn.Visible = True
EmployeeName.Text = emploName
BindList_Emp(employeeID)
End Sub 'ETGrid_Click
Binding the data to the top two DataGrids includes four easy steps to account for paging and sorting - retrieving the data, sorting the data, binding the data to the DataGrid's DataSource, and updating the paging text labels.
Private Sub BindList_EmpTerr(ByVal territoryName As String)
Dim empTerritoryList As HierarchicalReportCollection = HierarchicalReport.GetEmployeeSalesByTerritory(territoryName, Convert.ToInt32(ddlYear.SelectedItem.Value))
' do the sorting if there are data returned
If empTerritoryList.Count > 0 Then
SortGridData(empTerritoryList, SortField_EmpTerr, SortAscending_EmpTerr)
EmployeeTerritoryGrid.DataSource = empTerritoryList
EmployeeTerritoryGrid.DataBind()
End If
' Update paging labels
CurrPage_EmpTerr.Text = Convert.ToString((EmployeeTerritoryGrid.CurrentPageIndex + 1))
TotPages_EmpTerr.Text = EmployeeTerritoryGrid.PageCount.ToString()
End Sub 'BindList_EmpTerr
Sorting is a simple task to set up and implement with ASP.NET. The DataGrid's AllowSorting property is set to True and an event handler for the sorting is added to the DataGrid. The event handler then catches the sorting criteria, where it is then used to sort the appropriate DataGrid's column.
<asp:datagrid id="TerritoryGrid" runat="server" ... AllowSorting="True" OnSortCommand="SortGrid_Terr" ...>
Private Sub SortGridData(ByVal list As HierarchicalReportCollection, ByVal sortField As String, ByVal asc As Boolean)
Dim sortCol As HierarchicalReportCollection.HierarchicalReportFields = HierarchicalReportCollection.HierarchicalReportFields.InitValue
Select Case sortField
Case "Territory"
sortCol = HierarchicalReportCollection.HierarchicalReportFields.Territory
Case "SalesTotals"
sortCol = HierarchicalReportCollection.HierarchicalReportFields.SalesTotals
Case "EmployeeName"
sortCol = HierarchicalReportCollection.HierarchicalReportFields.EmployeeName
End Select
list.Sort(sortCol, asc)
End Sub 'SortGridData
The SortGridData method in the codebehind determines which field to sort on, and passes that value to the Sort method of the HierarchicalReportCollection object. The Sort method in the HierarchicalReportCollection component calls the appropriate IComparer method, which simply compares items in order to sort the list.
Public Overloads Sub Sort(ByVal sortField As HierarchicalReportFields, ByVal isAscending As Boolean)
Select Case sortField
Case HierarchicalReportFields.Territory
MyBase.Sort(New TerritoryNameComparer())
Case HierarchicalReportFields.SalesTotals
MyBase.Sort(New SalesTotalsComparer())
Case HierarchicalReportFields.EmployeeName
MyBase.Sort(New EmployeeNameComparer())
End Select
If Not isAscending Then
MyBase.Reverse()
End If
End Sub 'Sort
Implementing ASP.NET paging is even simpler. The AllowPaging property is set to True, a PageSize is set, and a short event handler is defined. The only thing that typically has to be done in the event handler is to set the DataGrid's CurrentPageIndex to the DataGridPageChangedEventArgs.NewPageIndex, and to re-bind. Here, in addition, the second and third table are hidden when the page is changed.
<asp:datagrid id="TerritoryGrid" runat="server" ... AllowPaging="True" PageSize="5" OnPageIndexChanged="Page_Terr" ... />
Protected Sub Page_Terr(ByVal sender As [Object], ByVal e As DataGridPageChangedEventArgs)
' Hide the 2nd and 3rd table if they were visible
ETTableColumn.Visible = False
EmpTableColumn.Visible = False
TerritoryGrid.CurrentPageIndex = e.NewPageIndex
BindList_Terr()
End Sub 'Page_Terr
Finally, a style can be set for the Previous and Next text labels.
<pagerstyle CssClass="PagerHier" PrevPageText="< Prev" NextPageText="Next >" Height="30px" VerticalAlign="middle"></pagerstyle>