Customer Order Monthly Report Using Pivot In SQL Server

Rahul Saxena
9y
4.6k
0
2

Article

I have written something like this earlier but I got a request from one of my follower to write it again by extending functionality like how we can show this report in ASP.NET and change color of Grid View by putting some condition. I will explain this in the following.

I have 2 tables in my SQL Server.

Customer
1. CREATE TABLE [dbo].[Customer](
2. [Customer_ID] [int] IDENTITY(1,1) NOT NULL,
3. [Customer_Name] [varchar](50) NULL,
4. CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
5. (
6. [Customer_ID] ASC
7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
8. ) ON [PRIMARY]
10. GO
11. SET ANSI_PADDING OFF
12. GO
Figure 1

Data in my Customer Table:

Figure 2

Now my Second Table,
Customer_Orders
1. CREATE TABLE [dbo].[Customer_Orders](
2. [OrderID] [int] IDENTITY(1,1) NOT NULL,
3. [Customer_ID] [int] NULL,
4. [Unit_Order] [int] NULL,
5. [Month] [varchar](50) NULL,
6. [Year] [int] NULL,
7. CONSTRAINT [PK_Customer_Orders] PRIMARY KEY CLUSTERED
8. (
9. [OrderID] ASC
10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
11. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
12. ) ON [PRIMARY]
13. GO
14. SET ANSI_PADDING OFF
15. GO
Figure 3

Data in my Customer_Orders table:

Figure 4

Now my Stored Procedure to generate report:

CREATE PROCEDURE GenerateReport
AS
BEGIN
SELECT * FROM (
SELECT c.Customer_Name, d.[Month],
ISNULL(d.Unit_Order,0) AS Unit
FROM Customer_Orders d RIGHT JOIN Customer c
ON d.Customer_ID=c.Customer_ID)
AS s PIVOT ( SUM(Unit)
FOR [Month] in (January, February, March, April, May, June, July,
August, September, October, November, December))

Figure 5

Now execute this Stored Procedure:

EXEC GenerateReport

Figure 6

Now we will show this report in GridView using ASP.NET.

Open Visual Studio -> New Web Site:

Add a GridView on your aspx and do coding in aspx.cs page to call stored procedure:

The following is my aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Report.aspx.cs" Inherits="Report" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<table style="width: 100%; text-align: center; border: 15px solid blue;">
<tr style="background-color:orangered; height:25px; color:white; font-size:16pt; font-weight:bold;">
<td>Customer Order Monthly Report Using Pivot In SQL Server </td>
</tr>
<tr style="background-color:yellow;">
<td style="padding:10px;">
<asp:GridView ID="gvCustomerOrder" runat="server" Width="100%" CellPadding="4" ForeColor="#333333" GridLines="Both">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" /> </asp:GridView>
</td>
</tr>
</table>
</form>
</body>
</html>

Here's my aspx.cs code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class Report: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack) BindReport();
}
private void BindReport()
{
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GenerateReport";
cmd.Connection = con;
try
{
con.Open();
gvCustomerOrder.EmptyDataText = "No Records Found";
gvCustomerOrder.DataSource = cmd.ExecuteReader();
gvCustomerOrder.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
}

My Connection String in web.config file:

<configuration>
<connectionStrings>
<add name="conString" connectionString="Data Source=INDIA\MSSQLServer2k8; database=TestDB;uid=sa; pwd=india" /> </connectionStrings>
</configuration>

Now run you application:

Figure 7

Up Next

Ebook Download

View all

SQL Jobs

Read by 0 people

Download Now!

Learn

View all