Spice Up Your Ad Reports - Using the ASP.NET Repeater Control to Group Data and Add Totals
The Repeater control is truly amazing. Being template driven, it gives you a lot of flexibility to manipulate and render the final output. In this article, we will exploit this flexibility of the ASP.NET Repeater and build a sample to demonstrate how to use the templates to group and display data. We will also use a little code to ‘total’/sum up our data and display it on the fly. At the end, we will apply css to design a beautiful report from the Repeater control. I have borrowed the css from the blog of a great designer guy called Veerle
I have chosen to take the example of displaying ‘advertisement’ data through the Repeater control. For simplicity sake, I will assume two tables related to website advertisement and then show you how to use the template driven Repeater control to display and group advertisement data and add a total at runtime. I assume you have used the Repeater control and are familiar with it. So here we go…
Database Script
This is a sample db script on which I will base my demonstration. Please note that this script in no way portrays a good DB design. I have kept the design simple so as to keep the focus more on the Repeater control, rather than turning this article into ‘DB Normalization Best Practices’ one. The database of our sample application is called ‘MYWEBSITE’ and the two tables are ‘Ads’ and ‘AdsStats’
Sample Schema
CREATE DATABASE MYWEBSITE
GO
USE [MYWEBSITE]
GO
CREATE TABLE [dbo].[Ads](
[AdId] [int] IDENTITY(1,1) NOT NULL,
[AdName] [nvarchar](100) NULL,
[Comments] [nvarchar](40) NULL
) ON [PRIMARY]
GO
USE [MYWEBSITE]
GO
CREATE TABLE [dbo].[AdsStats](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AdId] [int] NOT NULL,
[AdDate] [datetime] NULL,
[AdType] [int] NULL
) ON [PRIMARY]
GO
Sample Data
INSERT INTO [Ads] VALUES('AD1','Leaderboard');
INSERT INTO [Ads] VALUES('AD2','Banner');
INSERT INTO [Ads] VALUES('AD3','TextAds');
INSERT INTO [AdsStats] VALUES(1,'2008-09-23 04:47:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-23 06:17:33.000',0);
INSERT INTO [AdsStats] VALUES(2,'2008-09-23 07:42:33.000',0);
INSERT INTO [AdsStats] VALUES(2,'2008-09-23 08:32:33.000',1);
INSERT INTO [AdsStats] VALUES(3,'2008-09-23 08:41:33.000',1);
INSERT INTO [AdsStats] VALUES(1,'2008-09-23 19:27:33.000',0);
INSERT INTO [AdsStats] VALUES(2,'2008-09-23 19:37:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-23 19:47:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-24 01:47:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-24 02:17:33.000',1);
INSERT INTO [AdsStats] VALUES(3,'2008-09-24 03:22:33.000',0);
INSERT INTO [AdsStats] VALUES(2,'2008-09-24 04:32:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-24 05:11:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-25 02:27:33.000',0);
INSERT INTO [AdsStats] VALUES(3,'2008-09-25 03:47:33.000',0);
INSERT INTO [AdsStats] VALUES(2,'2008-09-25 04:17:33.000',0);
INSERT INTO [AdsStats] VALUES(3,'2008-09-25 05:12:33.000',1);
INSERT INTO [AdsStats] VALUES(2,'2008-09-25 05:32:33.000',1);
INSERT INTO [AdsStats] VALUES(3,'2008-09-25 06:41:33.000',0);
INSERT INTO [AdsStats] VALUES(1,'2008-09-25 07:47:33.000',0);
Now with the sample schema and data ready, we will move ahead to design our page that will group and display the advertisement data kept in these two tables. To understand what the data is about and what we will be doing with it, here’s a brief up. We will assume that there are 3 different kinds of ads our sample ‘MyWebSite’ serves : Leaderboard, Banner and TextAds. The ‘AdType’ column in AdsStats table displays the impressions and clicks that an advertisement receives. 0 means Impressions and 1 means a Click.
Our purpose is to display a daily report; that is to group the advertisement data based on date. We will also add a total at the end of each date, thereby displaying how many impressions and clicks were received from the different ads displayed, per day. I hope the case scenario is clear. If it isn’t, just wait till the end of this article and it will be. Let us now implement the functionality.
Create an ASP.NET website. Add a web.config file to the website to store the connection string as shown below:
<connectionStrings>
<remove name="AdConnString"/>
<add name="AdConnString" connectionString="Data Source=SUPROTIM; Initial Catalog=MYWEBSITE; Integrated Security = True; "providerName="System.Data.SqlClient"/>
</connectionStrings>
To display a group wise data, we will have to use Nested Repeaters. So our parent repeater will contain a label(lblDate) control to display the Date (for grouping). Based on this grouping, we will then pull related data in our child repeater. The Nested child repeater will be contained in the <ItemTemplate> of the Parent Repeater to replicate a display of hierarchical data.
SQL Queries:
The Repeaters will be bound to two different SQLDataSources. The queries for the two data sources are as follows:
Query for Parent Repeater - The Parent Repeater will display the date, to group the data. The query for it will look similar to the following:
SELECT DISTINCT CONVERT(VARCHAR(12), AdDate, 106) as AdDate
FROM AdsStats ORDER BY AdDate
As shown above, the query selects distinct date from the AdsStats table. Running the query displays the following result:
Query for Child Repeater – The Child Repeater will display the daily advertisement data. This data will be grouped based on the date in our Parent Repeater control. The query for displaying data in the child repeater will look similar to the following:
SELECT DATEADD(dd,DATEDIFF(dd,0,ast.AdDate),0) as AdDate,al.Comments as AdComments,
SUM(CASE WHEN AdType=0 THEN 1 ELSE 0 END) AS AdImpression,
SUM(CASE WHEN AdType=1 THEN 1 ELSE 0 END) AS AdClicks
FROM Ads al INNER JOIN AdsStats ast ON ast.AdId =al.AdId
GROUP BY DATEADD(dd,DATEDIFF(dd,0,AdDate),0), al.Comments
Running the query will display the following result:
With our queries ready, we now need to add markup to display the data. The markup for grouping and displaying data is as shown below. I have also added css to beautify it:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>My Ad Reports</title>
<style type="text/css">
body
{
font: normal 11px auto "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
background-color: #ffffff;
color: #4f6b72;
}
#mytable
{
width: 700px;
padding: 10px;
margin: 10px;
}
th {
font: bold 11px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
color: #4f6b72;
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
border-top: 1px solid #C1DAD7;
letter-spacing: 2px;
text-transform: uppercase;
text-align: left;
padding: 6px 6px 6px 12px;
background: #D5EDEF;
}
td {
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
background: #fff;
padding: 6px 6px 6px 12px;
color: #4f6b72;
}
td.alt
{
background: #F5FAFA;
color: #797268;
}
td.boldtd
{
font: bold 13px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
background: #D5EDEF;
color: #797268;
}
</style>
</head>
<body style="background-color:White">
<form id="form1" runat="server">
<div>
<h3>Daily Activity Reports</h3><br />
<asp:Repeater ID="ParentRepeater" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<p style="margin-left:10px"><b><asp:Label ID="lblDate" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "AdDate", "{0: dd MMM}")%>'></asp:Label></b></p>
<asp:Repeater ID="ChildRepeater" runat="server" DataSourceID="SqlDataSource2">
<HeaderTemplate>
<table id="mytable">
<tr>
<th>Banner Type</th>
<th>Impressions</th>
<th>Clicks</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<%# Eval("AdComments")%></td>
<td> <%# Eval("AdImpression")%> </td>
<td> <%# Eval("AdClicks")%></td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr>
<td class="alt">
<%# Eval("AdComments")%></td>
<td class="alt"> <%# Eval("AdImpression")%> </td>
<td class="alt"> <%# Eval("AdClicks")%></td>
</tr>
</AlternatingItemTemplate>
<FooterTemplate>
</table><br />
</FooterTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdConnString %>"
SelectCommand="SELECT DATEADD(dd,DATEDIFF(dd,0,ast.AdDate),0) as AdDate,al.Comments as AdComments, SUM(CASE WHEN AdType=0 THEN 1 ELSE 0 END) AS AdImpression, SUM(CASE WHEN AdType=1 THEN 1 ELSE 0 END) AS AdClicks FROM Ads al INNER JOIN AdsStats ast ON ast.AdId =al.AdId WHERE AdDate >= DATEADD(dd, DATEDIFF(dd, 0, @AdDate), 0) and AdDate <DATEADD(dd, DATEDIFF(dd, 0, @AdDate), 1) GROUP BY DATEADD(dd,DATEDIFF(dd,0,AdDate),0), al.Comments">
<SelectParameters>
<asp:ControlParameter ControlID="lblDate" Name="AdDate" />
</SelectParameters></asp:SqlDataSource>
</ItemTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdConnString %>"
SelectCommand="SELECT DISTINCT CONVERT(VARCHAR(12), AdDate, 106) as AdDate FROM AdsStats ORDER BY AdDate"></asp:SqlDataSource>
</div>
<br />
</form>
</body>
</html>
The parent repeater contains a label control (lblDate). The lblDate gets its value from the SqlDataSource1 using DataBinder.Eval as shown below:
<asp:Label ID="lblDate" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "AdDate", "{0: dd MMM}")%>'></asp:Label>
Note that the <SelectCommand> query for the child repeater has been modified to accept the date parameter(@AdDate). This parameter takes its value from the label control (lblDate) kept in the Parent Repeater.
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdConnString %>"
SelectCommand="SELECT DATEADD(dd,DATEDIFF(dd,0,ast.AdDate),0) as AdDate,al.Comments as AdComments, SUM(CASE WHEN AdType=0 THEN 1 ELSE 0 END) AS AdImpression, SUM(CASE WHEN AdType=1 THEN 1 ELSE 0 END) AS AdClicks FROM Ads al INNER JOIN AdsStats ast ON ast.AdId =al.AdId WHERE AdDate >= DATEADD(dd, DATEDIFF(dd, 0, @AdDate), 0) and AdDate <DATEADD(dd, DATEDIFF(dd, 0, @AdDate), 1) GROUP BY DATEADD(dd,DATEDIFF(dd,0,AdDate),0), al.Comments">
<SelectParameters>
<asp:ControlParameter ControlID="lblDate" Name="AdDate" />
</SelectParameters>
This is more or less like displaying data in a hierarchy, where the ‘Date’ is the parent and data related to that ‘Date’ becomes the child data.
The <HeaderTemplate> in the Child Repeater displays the headings for the columns. The <ItemTemplate> and <AlternateItemTemplate> displays the advertisement data and receive the values from SQLDataSource2.
Generate a ‘Total’ in the Repeater control at runtime
The last step is to add a running total to display the total number of impressions and clicks received from all the 3 different advertisements on that particular day. To do so, we will modify the <FooterTemplate> of the child repeater control to add two label controls to display a total of the Impressions and Clicks.
<FooterTemplate>
<tr class="boldtr" >
<td class="boldtd">Totals</td>
<td class="boldtd">
<asp:Label ID="lblImpressionCount" runat="server" Text=""></asp:Label></td>
<td class="boldtd"><asp:Label ID="lblClickCount" runat="server" Text=""></asp:Label></td>
</tr>
</table><br />
</FooterTemplate>
We will also add the ItemDataBound event handler to the ChildRepeater
<asp:Repeater ID="ChildRepeater" runat="server" DataSourceID="SqlDataSource2" OnItemDataBound="ChildRepeater_ItemDataBound">
We need to add some code to the ItemDataBound event of the Child repeater to calculate the totals. As a note, the ItemDataBound event of the Repeater control is raised when an item is being data-bound, just before the item gets rendered. This gives us an ideal location to calculate our Total on the fly before the data is rendered. The code contains two class level variables cntView and cntClick which are calculated when the ListItemType is either an Item or an AlternatingItem. Finally when we reach the Footer, the data is displayed in the two label controls (lblImpressionCount and lblClickCount). Once displayed, the variables are reset to 0. This operation is repeated for each day (group) in the AdsStats table. Use the following code:
C#
Int64 cntView;
Int64 cntClick;
public void ChildRepeater_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)
{
ListItemType lt = e.Item.ItemType;
if (lt == ListItemType.Item || lt == ListItemType.AlternatingItem)
{
DataRowView dv = e.Item.DataItem as DataRowView;
if (dv != null)
{
cntView += Convert.ToInt64(dv["AdImpression"]);
cntClick += Convert.ToInt64(dv["AdClicks"]);
}
}
else if (e.Item.ItemType == ListItemType.Footer)
{
Label lblCount = e.Item.FindControl("lblImpressionCount") as Label;
lblCount.Text = cntView.ToString();
Label lblSum = e.Item.FindControl("lblClickCount") as Label;
lblSum.Text = cntClick.ToString();
cntView = 0;
cntClick = 0;
}
}
VB.NET
Private cntView As Int64
Private cntClick As Int64
Public Sub ChildRepeater_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)
Dim lt As ListItemType = e.Item.ItemType
If lt = ListItemType.Item OrElse lt = ListItemType.AlternatingItem Then
Dim dv As DataRowView = TryCast(e.Item.DataItem, DataRowView)
If Not dv Is Nothing Then
cntView += Convert.ToInt64(dv("AdImpression"))
cntClick += Convert.ToInt64(dv("AdClicks"))
End If
ElseIf e.Item.ItemType = ListItemType.Footer Then
Dim lblCount As Label = TryCast(e.Item.FindControl("lblImpressionCount"), Label)
lblCount.Text = cntView.ToString()
Dim lblSum As Label = TryCast(e.Item.FindControl("lblClickCount"), Label)
lblSum.Text = cntClick.ToString()
cntView = 0
cntClick = 0
End If
End Sub
That’s it. A template based, beautifully designed Advertisement Report is ready, all thanks to the wonderful and powerful ASP.NET Repeater control and the nice css designed by Veerle.
When you run the report, the data will be grouped date wise, with a total at the end of each day as shown below:
I hope this article was useful and I thank you for viewing it. The source code of the article in both C# and VB.NET can be downloaded over here.