SQL Server

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

 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs