Click here to Skip to main content
15,112,285 members
Articles / Web Development / HTML
Article
Posted 1 Dec 2015

Stats

28.9K views
660 downloads
22 bookmarked

Dynamic Pivot Grid Using MVC, AngularJS and WEB API 2

Rate me:
Please Sign up or sign in to vote.
4.97/5 (11 votes)
6 Dec 2015CPOL10 min read
Simple MVC Pivot HTML Grid using AngularJS and Web API 2
In this article, we will see in detail how to create a simple MVC Pivot HTML Grid using AngularJS and Web API 2.

Image 1

Introduction

In this article, we will see in detail how to create a simple MVC Pivot HTML grid using AngularJS. In my previous article, I explained how to create a Dynamic Project Scheduling. In that article, I used Stored Procedure to display the Pivot result from SQL Query.

In real time projects, we need to generate many type of reports and we need to display the row wise data to be displayed column wise. In this article, I will explain how to create a Pivot Grid to display from actual data in front end using AngularJS.

For example, let’s consider the following example here. I have Toy Type (Category) and Toys Name with sales price per day.

In our database, we insert every record of toy details with price details. The raw data which is inserted in the database will look like this.

Toy Sales Detail Table

Image 2

Here, we can see that there are a total of 11 Records. There is repetition of Toy Name and Toy Type for each date. Now, if I want to see the total sales for each Toy Name of Toy Type, then I need to create a pivot result to display the record with total sum of each Toy Name per Toy Type. The required output will look like the following:

Pivot with Price Sum by Toy Name

Image 3

Here, we can see this is much easier to view the Total Sales per Toy Name. Here in Pivot, we can also add the Column and row Total. By adding the Total, it will be easy to find which item has the highest sales.

Pivot result has many kinds, we can see one more pivot report with Toy Sales Monthly per year. Here, we display the pivot result Monthly starting from 07 (July) to 11 (November).

Pivot with Price Sum by Monthly

Image 4

In this article, we will see two kinds of Pivot reports.

  1. Pivot result to display the Price Sum by Toy Name for each Toy Type
  2. Pivot result to display the Price Sum by Monthly for each Toy Name

Prerequisites

You can also view my previous articles related to AngularJS using MVC and the WCF Rest Service:

Previous articles related to Angular JS, MVC and WEB API:

Using the Code

Create Database and Table

In the first step, we will create a sample database and table to be used in our project. The following is the script to create a database, table and sample insert query.

Run the following script in your SQL Server. I have used SQL Server 2014.

SQL
-- Author      : Shanu                                  
-- Create date : 2015-11-20                                
-- Description : To Create Database,Table and Sample Insert Query                              
-- Latest                                 
-- Modifier    : Shanu                                 
-- Modify date : 2015-11-20                              
-- =============================================  
--Script to create DB,Table and sample Insert data  
USE MASTER;  
-- 1) Check for the Database Exists .If the database is exist then drop and create new DB  
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ToysDB' )  
BEGIN  
ALTER DATABASE ToysDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
DROP DATABASE ToysDB ;  
END  
  
CREATE DATABASE ToysDB  
GO  
  
USE ToysDB  
GO  
  
-- 1) //////////// ToysDetails table  
  
-- Create Table  ToysDetails, This table will be used to store the 
-- details like Toys Information  
  
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ToysSalesDetails' )  
DROP TABLE ToysSalesDetails  
GO  
  
CREATE TABLE ToysSalesDetails  
(  
   Toy_ID int  identity(1,1),  
   Toy_Type VARCHAR(100)  NOT NULL,  
   Toy_Name VARCHAR(100)  NOT NULL,   
   Toy_Price int  NOT NULL,  
   Image_Name VARCHAR(100)  NOT NULL,  
   SalesDate DateTime  NOT NULL,  
   AddedBy VARCHAR(100)  NOT NULL,  
CONSTRAINT [PK_ToysSalesDetails] PRIMARY KEY CLUSTERED       
(      
  [Toy_ID] ASC      
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, _
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]      
) ON [PRIMARY]    
  
GO  
  
-- delete from ToysSalesDetails  
-- Insert the sample records to the ToysDetails Table  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Spiderman',1650,'ASpiderman.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Spiderman',1250,'ASpiderman.png',getdate()-6,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Superman',1450,'ASuperman.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Superman',850,'ASuperman.png',getdate()-4,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Thor',1350,'AThor.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Thor',950,'AThor.png',getdate()-8,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Wolverine',1250,'AWolverine.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Wolverine',450,'AWolverine.png',getdate()-3,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','CaptainAmerica',1100,'ACaptainAmerica.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Spiderman',250,'ASpiderman.png',getdate()-120,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Spiderman',1950,'ASpiderman.png',getdate()-40,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Superman',1750,'ASuperman.png',getdate()-40,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Thor',900,'AThor.png',getdate()-100,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Thor',850,'AThor.png',getdate()-50,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Wolverine',250,'AWolverine.png',getdate()-80,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','CaptainAmerica',800,'ACaptainAmerica.png',getdate()-60,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Superman',1950,'ASuperman.png',getdate()-80,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Thor',1250,'AThor.png',getdate()-30,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Action','Wolverine',850,'AWolverine.png',getdate()-20,'Shanu')  
  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Lion',1250,'Lion.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Lion',950,'Lion.png',getdate()-4,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Tiger',1900,'Tiger.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Tiger',600,'Tiger.png',getdate()-2,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Panda',650,'Panda.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Panda',1450,'Panda.png',getdate()-1,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Dog',200,'Dog.png',getdate(),'Shanu')  
  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Lion',450,'Lion.png',getdate()-20,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Tiger',400,'Tiger.png',getdate()-90,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Panda',550,'Panda.png',getdate()-120,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Dog',1200,'Dog.png',getdate()-60,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Lion',450,'Lion.png',getdate()-90,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Animal','Tiger',400,'Tiger.png',getdate()-30,'Shanu')  
  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Bird','Owl',600,'BOwl.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Bird','Greenbird',180,'BGreenbird.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Bird','Thunderbird',550,'BThunderbird-v2.png',getdate(),'Shanu')  
  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Bird','Owl',600,'BOwl.png',getdate()-50,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Bird','Greenbird',180,'BGreenbird.png',getdate()-90,'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Bird','Thunderbird',550,'BThunderbird-v2.png',getdate()-120,'Shanu')  
  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Car','SingleSeater',1600,'CSingleSeater.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Car','Mercedes',2400,'CMercedes.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Car','FordGT',1550,'CFordGT.png',getdate(),'Shanu')  
Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) _
values('Car','Bus',700,'CBus.png',getdate(),'Shanu')  
  
select *,  
SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', _
(DATENAME(month, SalesDate)  * 4) - 3, 3) as 'Month'  
 from ToysSalesDetails   
  Where YEAR(SalesDate)=YEAR(getdate())  
  Order by Toy_Type,Toy_Name,Image_Name,SalesDate  
  
-- 1) END //  

After creating our table, we will create a Stored Procedure to get all data from the database to create our Pivot Grid from our MVC application using AngularJS and Web API.

1. Script to Create Stored Procedure

SQL
-- 1) Stored procedure to Select ToysSalesDetails  
-- Author      : Shanu                                                               
-- Create date : 2015-11-20                                                                
-- Description : Toy Sales Details                                                
-- Tables used :  ToysSalesDetails
-- Modifier    : Shanu                                                                 
-- Modify date : 2015-11-20
-- =============================================    
-- exec USP_ToySales_Select '',''  
-- =============================================
CREATE PROCEDURE [dbo].[USP_ToySales_Select]                                              
   (                            
     @Toy_Type           VARCHAR(100)     = '',  
     @Toy_Name               VARCHAR(100)     = ''    
      )                                                        
AS                                                                
BEGIN        
         select  Toy_Type as ToyType  
                ,Toy_Name as ToyName  
                ,Image_Name as ImageName  
                ,Toy_Price as Price  
                ,AddedBy as 'User'  
                ,DATENAME(month, SalesDate) as 'Month'  
                  
         FROM ToysSalesDetails   
          Where     
                    Toy_Type like  @Toy_Type +'%'  
                AND Toy_Name like @Toy_Name +'%'  
                AND YEAR(SalesDate)=YEAR(getdate())  
          ORDER BY  
              Toy_Type,Toy_Name,SalesDate  
           
END  

2. Create Your MVC Web Application in Visual Studio 2015

After installing our Visual Studio 2015, click Start, then Programs and select Visual Studio 2015. Click Visual Studio 2015, then New, Project and select Web and click ASP.NET Web Application. Select your project location and enter your web application name.

Image 5

Select MVC and in Add Folders and Core reference for, select the Web API and click OK.

Image 6

Add Database using ADO.NET Entity Data Model

Right click our project and click Add, then New Item.

Image 7

Select Data, then ADO.NET Entity Data Model and give the name for our EF and click Add.

Image 8

Select EF Designer from the database and click Next.

Image 9

Here, click New Connection and provide your SQL Server - Server Name and connect to your database.

Image 10

Here, we can see that I have given my SQL server name, Id and PWD and after it got connected. I selected the database as ToysDB as we have created the database using my SQL Script.

Image 11

Click Next and select the tables and all Stored Procedures need to be used and click Finish.

Image 12

Here, we can see now we have created our ToySalesModel.

Image 13

Once the Entity has been created, the next step is to add a Web API to our controller and write function to Select/Insert/Update and Delete.

Procedure to Add our Web API Controller

Right-click the Controllers folder, click Add and then click Controller.

Image 14

Select Controller and add an Empty Web API 2 Controller. Provide your name to the Web API controller and click OK. Here for my Web API Controller, I have given the name “ToyController”. In this demo project, I have created two different controllers for Order Master and Order Detail.

Image 15

As we have created Web API controller, we can see our controller has been inherited with ApiController.

As we all know, Web API is a simple and easy way to build HTTP Services for Browsers and Mobiles.

Web API has the following four methods as Get/Post/Put and Delete where:

  • Get is to request for the data (Select)
  • Post is to create a data (Insert)
  • Put is to update the data.
  • Delete is to delete data.
Get Method

In our example, I have used only a Get method since I am using only a Stored Procedure to get the data and bind to our MVC page using AngularJS.

Select Operation

We use a get method to get all the details of the ToysSalesDetails table using an entity object and we return the result as an IEnumerable. We use this method in our AngularJS and display the result in an MVC page from the AngularJS controller. Using Ng-Repeat, we can bind the details.

Here, we can see in the get method I have passed the search parameter to the USP_ToySales_Select Stored Procedure. In the Stored Procedure, I used like "%" to return all the records if the search parameter is empty.

C#
public class ToyController: ApiController  
{  
    ToysDBEntities objAPI = new ToysDBEntities();  
    // to Search Student Details and display the result    
    [HttpGet]  
    public IEnumerable < USP_ToySales_Select_Result > Get(string ToyType, string ToyName)  
    {  
        if(ToyType == null) ToyType = "";  
        if(ToyName == null) ToyName = "";  
        return objAPI.USP_ToySales_Select(ToyType, ToyName)  
            .AsEnumerable();  
    }  
}   

Now we have created our Web API Controller Class. The next step is to create our AngularJS Module and Controller. Let's see how to create our AngularJS Controller. In Visual Studio 2015, it is much easier to add our AngularJS Controller. Let's see step-by-step how to create and write our AngularJS Controller.

Creating AngularJs Controller

Firstly, create a folder inside the script folder and I have given the folder name as “MyAngular”.

Image 16

Now add your Angular Controller inside the folder.

Right-click the MyAngular folder and click Add and New Item. Select Web and then AngularJS Controller and provide a name for the Controller. I have named my AngularJS Controller “Controller.js”.

Image 17

Once the AngularJS Controller is created, we can see by default the controller will have the code with the default module definition and all.

Image 18

I have changed the preceding code like adding a Module and controller as in the following.

If the AngularJS package is missing, then add the package to your project.

Right-click your MVC project and click Manage NuGet Packages. Search for AngularJS and click Install.

Image 19

Now we can see all the AngularJs packages have been installed and we can see all the files in the Script folder.

Procedure to Create AngularJs Script Files

Modules.js: Here, we will add the reference to the AngularJS JavaScript and create an Angular Module named “OrderModule”.

JavaScript
// <reference path="../angular.js" />    
/// <reference path="../angular.min.js" />     
/// <reference path="../angular-animate.js" />     
/// <reference path="../angular-animate.min.js" />    
var app;  
  
(function () {  
    app = angular.module("OrderModule", ['ngAnimate']);  
})();  

Controllers: In AngularJS controller, I have done all the business logic and returned the data from Web API to our MVC HTML page.

1. Variable Declarations

Firstly, I declared all the local variables that need to be used.

JavaScript
app.controller("AngularJsOrderController", 
                function ($scope,$sce, $timeout, $rootScope, $window, $http) {  
    $scope.date = new Date();  
    $scope.MyName = "shanu";  
  
    //For Order Master Search   
    $scope.ToyType = "";  
    $scope.ToyName = "";  
  
    // 1) Item List Arrays.This arrays will be used to display .  
    $scope.itemType = [];  
    $scope.ColNames = [];  
  
    // 2) Item List Arrays.This arrays will be used to display .  
    $scope.items = [];    
    $scope.ColMonths = [];  

2. Methods

Select Method

In the select method, I have used $http.get to get the details from Web API. In the get method, I will provide our API Controller name and method to get the details. Here, we can see that I have passed the search parameter of OrderNO and TableID using:

JavaScript
{ params: { ToyType: ToyType, ToyName: ToyName }  

The function will be called during each page load. During the page load, I will get all the details and to create our Pivot result first I will store each Unique Toy name in Array to display the Pivot report by Toy Name as Column and Month Number in Array to display the Pivot report by Monthly sum.

After storing the Unique Values of Toy Name and Month Number, I will call the $scope.getMonthDetails(); and $scope.getToyNameDetails(); to generate Pivot report and bind the result.

JavaScript
// To get all details from Database    
selectToySalesDetails($scope.ToyType, $scope.ToyName);  
// To get all details from Database    
function selectToySalesDetails(ToyType, ToyName)  
{  
    $http.get('/api/Toy/',  
        {  
            params:  
            {  
                ToyType: ToyType,  
                ToyName: ToyName  
            }  
        })  
        .success(function (data)  
        {  
            $scope.ToyDetails = data;  
            if($scope.ToyDetails.length > 0)  
            {  
                //alert($scope.ToyDetails.length);    
                var uniqueMonth = {},  
                    uniqueToyName = {},  
                    i;  
                for(i = 0; i < $scope.ToyDetails.length; i += 1)  
                {  
                    // For Column wise Month add    
                    uniqueMonth[$scope.ToyDetails[i].Month] = $scope.ToyDetails[i];  
                    //For column wise Toy Name add    
                    uniqueToyName[$scope.ToyDetails[i].ToyName] = $scope.ToyDetails[i];  
                }  
                // For Column wise Month add    
                for(i in uniqueMonth)  
                {  
                    $scope.ColMonths.push(uniqueMonth[i]);  
                }  
                // For Column wise ToyName add    
                for(i in uniqueToyName)  
                {  
                    $scope.ColNames.push(uniqueToyName[i]);  
                }  
                // To display the Month wise Pivot result    
                $scope.getMonthDetails();  
                // To display the Month wise Pivot result    
                $scope.getToyNameDetails();  
            }  
        })  
        .error(function ()  
        {  
            $scope.error = "An Error has occurred while loading posts!";  
        });  
}   

Firstly, I will bind all the actual data from the database. Here, we can see all the data from database has been displayed total of nearly 43 records. We will create a Dynamic Pivot report from this actual data.

Image 20

Pivot Result to Display the Price Sum by Toy Name for each Toy Type

In this pivot report, I will display the Toy Type in rows and Toy Name as Columns. In our form load method, we already stored all the Unique Toy Name in Array which will be bind as Column. Now in this method, I will add the Unique Toy Type to be displayed as rows.

JavaScript
// To Display Toy Details as Toy Name Pivot Cols       
    $scope.getToyNameDetails = function () {    
    
        var UniqueItemName = {}, i    
    
        for (i = 0; i < $scope.ToyDetails.length; i += 1) {    
    
            UniqueItemName[$scope.ToyDetails[i].ToyType] = $scope.ToyDetails[i];    
        }    
        for (i in UniqueItemName) {    
    
            var ItmDetails = {    
                ToyType: UniqueItemName[i].ToyType    
            };    
            $scope.itemType.push(ItmDetails);    
        }    
    }   

Here, we can see now I have added all the Unique ToyType icon Arrays which will bind in our MVC page.

Here in HTML table creation, we can see that first I will create the Grid Header. In Grid header, I displayed the Toy Type and all other Toy Name as column dynamically using data-ng-repeat="Cols in ColNames | orderBy:'ToyName':false".

HTML Part

HTML
<tr style="height: 30px; background-color:#336699 ; 
    color:#FFFFFF ;border: solid 1px #659EC7;">  
    <td width="20"></td>  
    <td width="200" align="center"><b>ToyType</b></td>  
    <td align="center" data-ng-repeat="Cols in ColNames | 
    orderBy:'ToyName':false" style="border: solid 1px #FFFFFF; ">  
        <table>  
            <tr>  
                <td width="80"><b>{{Cols.ToyName}}</b></td>  
            </tr>  
        </table>  
    </td>  
    <td width="60" align="center"><b>Total</b></td>  
</tr>  

After binding the columns, I will bind all the Toy Type as rows and for each Type type and Toy name, I will display the summary of price in each appropriate column.

HTML Part

HTML
<tbody data-ng-repeat="itm in itemType">  
    <tr>  
        <td width="20">{{$index+1}}</td>  
        <td align="left" style="border: solid 1px #659EC7; padding: 5px;"> 
        <span style="color:#9F000F">{{itm.ToyType}}</span> </td>  
        <td align="center" data-ng-repeat="ColsNew in ColNames | 
        orderBy:'ToyName':false" align="right" 
        style="border: solid 1px #659EC7; padding: 5px;table-layout:fixed;">  
            <table>  
                <tr>  
                    <td align="right"> <span ng-bind-html="showToyItemDetails
                    (itm.ToyType,ColsNew.ToyName)"></span> </td>  
                </tr>  
            </table>  
        </td>  
        <td align="right"> <span ng-bind-html="showToyColumnGrandTotal
        (itm.ToyType,ColsNew.ToyName)"></span> </td>  
    </tr>  
</tbody>  

AngularJS Part

From MVC page, I will call this method to bind the resultant summary price in each row after calculation.

JavaScript
// To display Toy Details as Toy Name wise Pivot Price Sum calculate     
$scope.showToyItemDetails = function (colToyType, colToyName)  
{  
    $scope.getItemPrices = 0;  
    for(i = 0; i < $scope.ToyDetails.length; i++)  
    {  
        if(colToyType == $scope.ToyDetails[i].ToyType)  
        {  
            if(colToyName == $scope.ToyDetails[i].ToyName)  
            {  
                $scope.getItemPrices = parseInt($scope.getItemPrices) + 
                                       parseInt($scope.ToyDetails[i].Price);  
            }  
        }  
    }  
    if(parseInt($scope.getItemPrices) > 0)  
    {  
        return $sce.trustAsHtml("<font color='red'><b>" + $scope.getItemPrices.toString()  
            .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b></font>");  
    }  
    else  
    {  
        return $sce.trustAsHtml("<b>" + $scope.getItemPrices.toString()  
            .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b>");  
    }  
}  

Image 21

Column Total

To display the Column Total at each row end, in this method, I will calculate each row result and return the value to bind in MVC page.

JavaScript
// To Display Toy Details as Toy Name wise Pivot Column wise Total  
    $scope.showToyColumnGrandTotal = function (colToyType, colToyName) {  
  
        $scope.getColumTots = 0;  
         
        for (i = 0; i < $scope.ToyDetails.length; i++) {  
            if (colToyType == $scope.ToyDetails[i].ToyType) {  
                $scope.getColumTots = parseInt($scope.getColumTots) + 
                                      parseInt($scope.ToyDetails[i].Price);  
            }  
        }  
        return $sce.trustAsHtml("<font color='#203e5a'><b>" + 
        $scope.getColumTots.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b></font>");  
    }  

Row Total

To display the Row Total at each Column end, in this method, I will calculate each column result and return the value to bind in MVC page.

JavaScript
// To display Toy Details as Month wise Pivot Row wise Total    
$scope.showToyRowTotal = function (colToyType, colToyName)  
{  
    $scope.getrowTotals = 0;  
    for(i = 0; i < $scope.ToyDetails.length; i++)  
    {  
        if(colToyName == $scope.ToyDetails[i].ToyName)  
        {  
            $scope.getrowTotals = parseInt($scope.getrowTotals) + 
                                  parseInt($scope.ToyDetails[i].Price);  
        }  
    }  
    return $sce.trustAsHtml("<font color='#203e5a'><b>" + $scope.getrowTotals.toString()  
        .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b></font>");  
}  

Row and Column Grand Total

To calculate both Row and Column Grand Total:

JavaScript
// To Display Toy Details as Month wise Pivot Row & Column Grand Total    
$scope.showToyGrandTotals = function (colToyType, colToyName)  
{  
    $scope.getGrandTotals = 0;  
    if($scope.ToyDetails && $scope.ToyDetails.length)  
    {  
        for(i = 0; i < $scope.ToyDetails.length; i++)  
        {  
            $scope.getGrandTotals = parseInt($scope.getGrandTotals) + 
                                    parseInt($scope.ToyDetails[i].Price);  
        }  
    }  
    return $sce.trustAsHtml("<b>" + $scope.getGrandTotals.toString()  
        .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b>");  
}  

Pivot Result to Display the Price Sum by Monthly for Each Toy Name

The same logic as above has been used to calculate and bind the Pivot report for Monthly Toy Name summary details. Here, we can see that it will look like this as in Rows, I will bind Toy Type (Toy Category) Toy Name, Toy Image as static and all the Month Number in Columns dynamically. Similar to the above function, I will calculate all the Toy Summary price per Month and display in each row with Row Total, Column Total and Grand Total.

Image 22

Search Button Click

In the search button click, I will call the SearchMethod to bind the result. In Search method, I will clear all the array values and rebind all the Pivot Grid with new result.

HTML
<input type="text" name="txtToyType" ng-model="ToyType" value="" />  
<input type="text" name="txtToyName" ng-model="ToyName" />  
<input type="submit" value="Search" 
style="background-color:#336699;color:#FFFFFF" ng-click="searchToySales()" />  
//Search  
    $scope.searchToySales = function () {  
        // 1) Item List Arrays.This arrays will be used to display .  
        $scope.itemType = [];  
        $scope.ColNames = [];  
  
        // 2) Item List Arrays.This arrays will be used to display .  
        $scope.items = [];  
        $scope.ColMonths = [];  
  
        selectToySalesDetails($scope.ToyType, $scope.ToyName);  
    }  

Image 23

Points of Interest

Note: Download the code and run all the SQL script files. In the WebConfig, change the connection String with your SQL Server connection.

History

  • 2nd December, 2015: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

syed shanu
Team Leader
India India
Microsoft MVP | Code Project MVP | CSharp Corner MVP | Author | Blogger and always happy to Share what he knows to others. MyBlog

My Interview on Microsoft TechNet Wiki Ninja Link

Comments and Discussions

 
PraiseGreat Application Pin
Ankit Bansal MVP2-May-16 20:21
professionalAnkit Bansal MVP2-May-16 20:21 
GeneralMy vote of 4 Pin
Omar Nasri22-Dec-15 6:46
professionalOmar Nasri22-Dec-15 6:46 
GeneralRe: My vote of 4 Pin
syed shanu22-Dec-15 14:15
mvasyed shanu22-Dec-15 14:15 
GeneralMy vote of 5 Pin
Anil Kumar @AnilAwadh2-Dec-15 22:00
professionalAnil Kumar @AnilAwadh2-Dec-15 22:00 
so nicely explained!
GeneralRe: My vote of 5 Pin
syed shanu2-Dec-15 22:08
mvasyed shanu2-Dec-15 22:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.