Post
Ask Question

Customer Order Monthly Report Using Pivot In SQL Server

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.

  1. 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]  
    9.   
    10. GO  
    11. SET ANSI_PADDING OFF  
    12. GO  
    table design
                                                          Figure 1

    Data in my Customer Table:

    Data in my Customer Table
                                       Figure 2

    Now my Second Table,

  2. 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  
    table
                                         Figure 3

    Data in my Customer_Orders table:

    Customer_Orders table
                                                 Figure 4

Now my Stored Procedure to generate report:

  1. CREATE PROCEDURE GenerateReport   
  2. AS  
  3. BEGIN  
  4.      SELECT * FROM (    
  5.         SELECT c.Customer_Name, d.[Month],    
  6.             ISNULL(d.Unit_Order,0) AS Unit    
  7.             FROM Customer_Orders d RIGHT JOIN Customer c    
  8.             ON d.Customer_ID=c.Customer_ID)    
  9.             AS s  PIVOT ( SUM(Unit)    
  10.             FOR [Month] in (January, February, March, April, May, June, July,  
  11.                              August, September, October, November, December))   
Stored Procedure
                                                                            Figure 5

Now execute this Stored Procedure:

EXEC GenerateReport

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:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Report.aspx.cs" Inherits="Report" %>  
  2.     <!DOCTYPE html>  
  3.     <html xmlns="http://www.w3.org/1999/xhtml">  
  4.   
  5.     <head runat="server">  
  6.         <title></title>  
  7.     </head>  
  8.   
  9.     <body>  
  10.         <form id="form1" runat="server">  
  11.             <table style="width: 100%; text-align: center; border: 15px solid blue;">  
  12.                 <tr style="background-color:orangered; height:25px; color:white; font-size:16pt; font-weight:bold;">  
  13.                     <td>Customer Order Monthly Report Using Pivot In SQL Server </td>  
  14.                 </tr>  
  15.                 <tr style="background-color:yellow;">  
  16.                     <td style="padding:10px;">  
  17.                         <asp:GridView ID="gvCustomerOrder" runat="server" Width="100%" CellPadding="4" ForeColor="#333333" GridLines="Both">  
  18.                             <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
  19.                             <EditRowStyle BackColor="#999999" />  
  20.                             <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  21.                             <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  22.                             <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
  23.                             <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
  24.                             <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />  
  25.                             <SortedAscendingCellStyle BackColor="#E9E7E2" />  
  26.                             <SortedAscendingHeaderStyle BackColor="#506C8C" />  
  27.                             <SortedDescendingCellStyle BackColor="#FFFDF8" />  
  28.                             <SortedDescendingHeaderStyle BackColor="#6F8DAE" /> </asp:GridView>  
  29.                     </td>  
  30.                 </tr>  
  31.             </table>  
  32.         </form>  
  33.     </body>  
  34.   
  35.     </html>  
Here's my aspx.cs code:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.Configuration;  
  10. public partial class Report: System.Web.UI.Page  
  11. {  
  12.     protected void Page_Load(object sender, EventArgs e)  
  13.     {  
  14.         if (!Page.IsPostBack) BindReport();  
  15.     }  
  16.     private void BindReport()  
  17.     {  
  18.         String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;  
  19.         SqlConnection con = new SqlConnection(strConnString);  
  20.         SqlCommand cmd = new SqlCommand();  
  21.         cmd.CommandType = CommandType.StoredProcedure;  
  22.         cmd.CommandText = "GenerateReport";  
  23.         cmd.Connection = con;  
  24.         try  
  25.         {  
  26.             con.Open();  
  27.             gvCustomerOrder.EmptyDataText = "No Records Found";  
  28.             gvCustomerOrder.DataSource = cmd.ExecuteReader();  
  29.             gvCustomerOrder.DataBind();  
  30.         }  
  31.         catch (Exception ex)  
  32.         {  
  33.             throw ex;  
  34.         }  
  35.         finally  
  36.         {  
  37.             con.Close();  
  38.             con.Dispose();  
  39.         }  
  40.     }  
  41. }  
My Connection String in web.config file:
  1. <configuration>  
  2.     <connectionStrings>  
  3.         <add name="conString" connectionString="Data Source=INDIA\MSSQLServer2k8; database=TestDB;uid=sa; pwd=india" /> </connectionStrings>  
  4. </configuration>  
Now run you application:

run you application
                                                                           Figure 7

 


Similar Articles