Nested GridView Grouping and Displaying SubTotals and GrandTotals

I was working on a project where I needed to display some reports based on the employee's daily work. The report should consist of an employee check in time, check out time, no of hours worked for each day and also the total hours worked. The task can be accomplished in number of ways which includes using the Crystal Reports or SQL SERVER Reporting Services but I thought I should use Nested GridView to achieve the desired result. In this article I will demonstrate how to use the Nested GridView approach to create subtotals and grand total.

Introduction:

I was working on a project where I needed to display some reports based on the employee's daily work. The report should consist of an employee check in time, check out time, no of hours worked for each day and also the total hours worked. The task can be accomplished in number of ways which includes using the Crystal Reports or SQL SERVER Reporting Services but I thought I should use Nested GridView to achieve the desired result. In this article I will demonstrate how to use the Nested GridView approach to create subtotals and grand total.

Database Design:

Let’s first check out the database design. The database consists of a single table called “Reports”. The schema of the Reports table is given below:

ReportID (int) (PK): This is the primary key of the table.

HANumber (varchar(50): This uniquely identifies the employee.

CheckInDate (DateTime): The date and time employee checked in for work.

CheckOutDate (DateTime): The date and time employee checked out for work.

As, you can see the database schema is very simple. Now, let’s create some methods to get the data out of the “Reports” table.

Creating the Entity Classes:

There are two entity classes used in this application namely, Report and Date. The Report class holds all the information about the report which includes the “ReportID”, “CheckInDate”,”CheckOutDate”. The Date class holds the “ReportDate” and the collection of reports. The idea is to get all the unique dates from the database and store it in the Date collection. Then iterate over the collection and retrieve all the reports associated with that date.

Implementing the GetDates Method:

As, I stated before the GetDates method retrieves all the unique dates from the database. This retrieval should be dependent on the ID of the user which, in this case is the HANumber.

public static List<Date> GetDates(string haNumber)

{

string connectionString = "Server=localhost;Database=CWTimeSheetDB;Trusted_Connection=true";

string selectCommand = "SELECT DISTINCT CAST( CONVERT(varchar(10),CheckInDate,110) AS DateTime) AS [ReportDate] FROM Reports WHERE HANumber = @HANumber ";

List<Date> dates = new List<Date>();

using (SqlConnection myConnection = new SqlConnection(connectionString))

{

SqlCommand myCommand = new SqlCommand(selectCommand, myConnection);

myCommand.Parameters.AddWithValue("@HANumber", haNumber);

myConnection.Open();

SqlDataReader reader = myCommand.ExecuteReader();

while (reader.Read())

{

Date d = new Date();

d.ReportDate = Convert.ToDateTime(reader["ReportDate"]);

dates.Add(d);

}

return dates;

}

In the code above I am passing the HANumber to the GetDates method. The method selects the distinct dates from the Reports table using the HANumber. Finally, the List<Date> is populated and returned with all the unique dates associated with that employee.

Implementing the GetReports Method:

Next, we need to implement the GetReports method which takes those unique dates and the HANumber and pull out the check in time, check out time and hours worked for that employee.

public static void GetReports(List<Date> dates,string haNumber)

{

string connectionString = "Server=localhost;Database=CWTimeSheetDB;Trusted_Connection=true";

string selectReportCommand = @"SELECT ReportID, HANumber,CheckInDate,CheckOutDate,

DATEDIFF(mi,CheckInDate,CheckOutDate) / 60 AS [Hours] FROM Reports

WHERE

CONVERT(varchar(10),CheckInDate,110) = CONVERT(varchar(10),@ReportDate,110)

AND HANumber = @HANumber";

foreach (Date d in dates)

{

List<Report> reports = new List<Report>();

using (SqlConnection myConnection = new SqlConnection(connectionString))

{

SqlCommand myCommand = new SqlCommand(selectReportCommand, myConnection);

myCommand.Parameters.AddWithValue("@ReportDate", d.ReportDate);

myCommand.Parameters.AddWithValue("@HANumber", haNumber);

myConnection.Open();

SqlDataReader reader = myCommand.ExecuteReader();

while (reader.Read())

{

Report report = new Report();

report.ReportID = (int)reader["ReportID"];

report.CheckInDate = Convert.ToDateTime(reader["CheckInDate"]);

report.CheckOutDate = Convert.ToDateTime(reader["CheckOutDate"]);

report.Hour = Convert.ToDouble(reader["Hours"]);

reports.Add(report);

}

d.Reports = reports;

}

The GetReports method is used to get all the information about the report. I iterate through the Date collection and retrieve all the reports associated with that date using the date and HANumber as the criteria. Once, I get all the reports I assigned it to the date reports collection and start the loop again. When the loop ends the Date collection will have all the reports which are ready to be rendered on the screen using the Nested GridView.

Creating the Nested GridView:

Let’s first see what format we are going to display the reports.

Day Date

CheckInTime CheckOutTime Hour

[Total Hours = Subtotal]

Day Date

CheckInTime CheckOutTime Hour

[Total Hours = Subtotal]

[Final Total Hours = Grandtotal]

The table above gives us the basic idea what we are trying to achieve and what our report will look like when it is completed. The subtotal is based on the number of hours worked each day since, there can be multiple check in and checkout for the same day. The grand total is calculated by adding all the subtotals.

Now, let’s check out the same table which we created above but from the HTML and GridView point of view (This will make GridView very happy!).

Day Date (Parent GridView Label)

CheckInTime CheckOutTime Hour (Child GridView) Parent ROW 1

[Total Hours = Subtotal] (Child GridView Footer)

Day Date

CheckInTime CheckOutTime Hour Parent ROW 2

[Total Hours = Subtotal]

[Final Total Hours = Grandtotal] (Parent GridView Footer)

The above table is decorated with different labels which indicate the structure of the parent and child GridView.

I am sure you got the idea what we are trying to do. So, let’s get started. Our parent GridView will be known as gvReportDates and the child GridView will be gvReports.

<asp:GridView ID="gvReportDates" ShowFooter="true" AutoGenerateColumns="False" runat="server" OnRowDataBound="GridView1_RowDataBound" CellPadding="4" Font-Size="Large" ForeColor="#333333" GridLines="None">

<asp:TemplateField>

<h2>

<%# ((DateTime)Eval("ReportDate")).DayOfWeek %>

<asp:Label ID="Label1" runat="server" Text = '<%# ((DateTime) Eval("ReportDate")).ToShortDateString() %>' Font-Size="Large" />

</h2>

<asp:GridView ID="gvReports" GridLines="None" ShowFooter="true" AutoGenerateColumns="false" DataSource='<%# Eval("Reports") %>' runat="server" >

<asp:TemplateField HeaderText="Check In Date">

<asp:Label ID="lblCheckInDate" runat="server" Text = '<%# Eval("CheckInDate") %>' />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Check Out Date">

<asp:Label ID="lblCheckOutDate" runat="server" Text = '<%# Eval("CheckOutDate") %>' />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Hour">

<asp:Label ID="lblHour" runat="server" Text = '<%# Eval("Hour") %>' />

</ItemTemplate>

<asp:Label ID="lblTotal" runat="server" />

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ItemTemplate>

<%# GetTotalHours() %>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

If you run the above code you will see the following output:

The display looks nice but the subtotal and the grand total are not yet being calculated. Let’s see how we can do that.

Calculating the Subtotal and the Grand Total:

The calculation of the subtotal and the grand total is performed in the parent GridView’s (gvReportDates) onRowDataBound event. Let’s take a look in more detail.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

GridView gv = e.Row.FindControl("gvReports") as GridView;

GridViewRow footerRow = gv.FooterRow;

foreach (GridViewRow row in gv.Rows)

{

Label labelTotal = footerRow.FindControl("lblTotal") as Label;

dayHour += Double.Parse((row.FindControl("lblHour") as Label).Text);

labelTotal.Text = dayHour.ToString();

}

totalHour += dayHour;

dayHour = 0.0;

}

The totalHour and dayHour are private fields and are declared at class level.

private double dayHour = 0.0;

private double totalHour = 0.0;

Inside the RowDataBound event I check whether we are inside the DataRow or not. Once, a DataRow is found I extract the GridView out of that row which is the child GridView. I get a reference to the footer row of the child GridView. Finally, using the foreach loop I iterate over the rows contained in the child GridView and add the hours. Once, the hours are added I assigned it to the label in the child GridView footer row.

When the foreach loop is over I add the subtotal to the grand total and reset the subtotal for the new calculation.

Now, when you run the application you will get the correct output.

Conclusion:

In this article I demonstrated how to calculate subtotals and the final total using the nested GridView. The article also demonstrates the grouping concept using the nested GridView. If you look carefully I am only calculating the hour difference between the check in and check out time. If you are interested in calculating minutes then please email me and I will send you the stored procedure which calculates the total time between given ranges.

I hope you liked the article, happy coding!

Download Sample