Displaying Grouped Records with ListView and LINQ

I came across an interesting problem while enhancing a 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 to get this done?

First, we can use the ListView Layout Template 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 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 to numeric total of each group
• The method fGroupingRowIfNewProject() is used to signal a group break per row
• The sytle <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, this one 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 == "&nbsp")
                    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 to return some HTML so the contained in the template displays the appropiate text for each group break.

As you 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 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 == "&nbsp") 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 defined a row in our template to display the project name each time that 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 appropiate 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 == "&nbsp")
            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!

Will 

CodeProject