Displaying Grouped Records with ListView and LINQ

I came across an interesting problem while enhancing an ASP.NET site, I needed to add a grouped ListView to use it as a query screen with print capabilities. The scenario is as follows: you have a list of records being displayed in a ListView. Records are grouped (in my case, by Project) and you want to have a group footer to total each group.

How can we get this done?

First, we can use the ListView LayoutTemplate to create the desired table structure:

<asp:ListView ID="lvGroupedLabor" runat="server"
    style="text-align: center; font-family: Arial, Helvetica, sans-serif;
    font-size: xx-small" ondatabound="lvGroupedLabor_DataBound">
<LayoutTemplate>
    <table cellspacing="0" cellpadding="5" rules="all" border="1" width="100%">
        <thead class='grouptot'>
        <tr>      
        <th>Project</th>      
        <th>Work Order</th>    
        <th>Date</th>
        <th>Time</th>
        <th>Comment</th>
        </tr>    
        </thead>
        <tbody>
        <asp:PlaceHolder ID="itemPlaceholder" runat="server" />
        </tbody>
        <tfoot>
            <tr class='grouptot'>        
            <td colspan="6"><asp:Literal ID="litSubTotal" runat="server" /></td>    
            </tr>
            <tr class='group'>        
            <td colspan="6"><asp:Literal ID="litGrandTotal" runat="server" /></td>
            </tr>    
        </tfoot>
    </table>
</LayoutTemplate>
<ItemTemplate>
    <%# fTotalbyProject(false) %>
    <%# fGroupingRowIfNewProject() %>
    <tr class='data<%# Container.DataItemIndex % 2 %>'>
    <td><%#Eval("project_cd")%></td>
    <td><%#Eval("woid")%></td>
    <td><%#Eval("labor_date", "{0:M/dd/yyyy}")%></td>
    <td><%#Eval("labor_time")%></td>
    <td><%#Eval("comment")%></td>
    </tr>
</ItemTemplate>
</asp:ListView>

There are several interesting things to point out here:

• Notice how there is a row in the template for the group footer <tfoot>? In my case, I decided to have a row to total per group and another row to total all the values.
• The structure of the template is such that it will insert a header row for the group, followed by the Total of the last group, then the record rows. Therefore we will need to address how to insert a total for the last row and the grand total.
• The method fTotalbyProject(false) is used to display the numeric total of each group.
• The method fGroupingRowIfNewProject() is used to signal a group break per row.
• The style <tr class='data<%# Container.DataItemIndex % 2 %>'> is a nice little trick to zebra the rows in the ListView.

The code assumes that you have a few static variables defined that we'll be using with our class.

static string sLastProject = "";
static string sTotLastProj = "";
static decimal dLastSumTotal = 0;
static decimal dGrandTotal = 0;
static bool bTotalCntSt = false;

Let's review the fTotalbyProject method, it is used to display the "Total for Project (XXX):"

protected string fTotalbyProject(bool aMode)
{
    if (aMode == false)
    {
        //For the first row, set the flag to true so initialization happens only once
        String sCurrentProject = (string)Eval("project_cd");
        if (bTotalCntSt == false)
        {
            sTotLastProj = (string)Eval("project_cd");
            bTotalCntSt = true;
        }
        //No changes, do nothing
        if (sTotLastProj == sCurrentProject)
        {
            return string.Empty;
        }
        else
        {
            //There is a change, therefore compute the total.
            Decimal dGetTotalbyProject = fSumTotalbyProject(sTotLastProj, false);
            String sNewRow = "";
            if (sTotLastProj == " ")
                sNewRow = String.Format("<tr class='grouptot'><td colspan='6'>" + 
                             "Total for tasks with unassigned Project: {1}</td></tr>", 
                             sTotLastProj, dGetTotalbyProject);
            else
                sNewRow = String.Format("<tr class='grouptot'><td colspan='6'>"+
                             "Total for Project {0}: {1}</td></tr>", 
                             sTotLastProj, dGetTotalbyProject);
            sTotLastProj = (string)Eval("project_cd");
            return sNewRow;
        }
    }
    else
    {
        Decimal dGetTotalbyProject = fSumTotalbyProject(sTotLastProj, false);
        String sNewRow = String.Format("Total for Project {0}: {1}", 
               sTotLastProj, dGetTotalbyProject);
        return sNewRow;
    }
}

What we do here is return some HTML so the template displays the appropriate text for each group break.

As you have probably noticed, the fTotalbyProject method calls another method named fSumTotalbyProject, which is the one that does the numeric sum of the records; it takes a second argument to indicate if the total should be done by Project or calculate the grand total.

This function uses LINQ and assumes you have a LINQ DB Model previously defined.

protected Decimal fSumTotalbyProject(string aProject, bool bIsGrandTotal)
{
    string aUserName = aUserNameVariable;
    DateTime dStDate = Convert.ToDateTime(YourTextBox1.Text);
    DateTime dEndDate = Convert.ToDateTime(YourTextBox2.Text);
    TasksLinqDataContext db = new TasksLinqDataContext();
    if (aProject == " ") aProject = "";
    try
    {
        if (!bIsGrandTotal)
        {
            var dbTaskLabor = (from vtlbr in db.tasksTable
                                    where vtlbr.staff_name == aUserName &&
                                    vtlbr.labor_date >= dStDate && 
                                    vtlbr.labor_date <= dEndDate &&
                                    vtlbr.project_cd == aProject
                                    select vtlbr.labor_time).Sum();
            return dbTaskLabor;
        }
        else
        {
            var dbTaskLabor = (from vtlbr in db.tasksTable
                                    where vtlbr.staff_name == aUserName &&
                                            vtlbr.labor_date >= dStDate && 
                                            vtlbr.labor_date <= dEndDate
                                    select vtlbr.labor_time).Sum();
            return dbTaskLabor;
        }
    }
    catch (Exception Exc)
    {
        ErrorLbl.ForeColor = System.Drawing.Color.Red;
        ErrorLbl.Text = Exc.Message;
        return 0;
    }
}

To signal that there is a new Project, we define a row in our template to display the project name each time there is a change. The method fGroupingRowIfNewProject handles that logic. Again, we are just returning some HTML to add a cell to the table and display the text "Project : ". The HTML is placed in the appropriate row, thanks to our template.

protected string fGroupingRowIfNewProject()
{
    //Get the data field value of interest for this row
    String sCurrentProject = (string)Eval("project_cd");
    //Specify name to display if current project is NULL
    if (sCurrentProject == " ")
        sCurrentProject = "Unassigned Project";
    else
        sCurrentProject = "Project: " + sCurrentProject;
    //See if there's been a change in value
    if (sLastProject != sCurrentProject)
    {
        //There's been a change! Record the change and emit the table row
        sLastProject = sCurrentProject;
        return String.Format("<tr class='group'>"+
                                  "<td colspan='6'>{0}</td></tr>", 
                                  sCurrentProject);
    }
    else
    {
        //No Change, return an empty string
        return String.Empty;
    }
}

Finally, the below code in the DataBound event of the ListView is used to invoke the above functions one last time to display and compute the Total for the last group and then display the GrandTotal for all records.

protected void lvGroupedLabor_DataBound(object sender, EventArgs e)
{
    Literal lSubTotal = lvGroupedLabor.FindControl("litSubTotal") as Literal;
    lSubTotal.Text = fTotalbyProject(true);
    Literal lGrandTotal = lvGroupedLabor.FindControl("litGrandTotal") as Literal;
    lGrandTotal.Text = "Grand Total: " + 
                Convert.ToString(fSumTotalbyProject(String.Empty, true));
}

Happy coding!