Post
Ask Question

Dynamic Pivot Grid Using MVC, AngularJS And WEB API 2

 

In this article we will see in detail how to create a simple MVC Pivot HTML grid using AngularJS. In my previous article, I have explained how to create a Dynamic Project Scheduling. In that article I have 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 inserted in database will look like this. 

Toy Sales Detail Table

 

Here we can see there is total 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



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 kind, 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



In this article we will see 2 kind of Pivot report.
  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

Visual Studio 2015 - You can download it from here.
Code part

Create Database and Table

In the first step, we will create a 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. 

  1. -- =============================================                                
  2. -- Author      : Shanu                                  
  3. -- Create date : 2015-11-20                                
  4. -- Description : To Create Database,Table and Sample Insert Query                              
  5. -- Latest                                 
  6. -- Modifier    : Shanu                                 
  7. -- Modify date : 2015-11-20                              
  8. -- =============================================  
  9. --Script to create DB,Table and sample Insert data  
  10. USE MASTER;  
  11. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB  
  12. IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ToysDB' )  
  13. BEGIN  
  14. ALTER DATABASE ToysDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
  15. DROP DATABASE ToysDB ;  
  16. END  
  17.   
  18.   
  19. CREATE DATABASE ToysDB  
  20. GO  
  21.   
  22. USE ToysDB  
  23. GO  
  24.   
  25. -- 1) //////////// ToysDetails table  
  26.   
  27. -- Create Table  ToysDetails ,This table will be used to store the details like Toys Information  
  28.   
  29. IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ToysSalesDetails' )  
  30. DROP TABLE ToysSalesDetails  
  31. GO  
  32.   
  33. CREATE TABLE ToysSalesDetails  
  34. (  
  35.    Toy_ID int  identity(1,1),  
  36.    Toy_Type VARCHAR(100)  NOT NULL,  
  37.    Toy_Name VARCHAR(100)  NOT NULL,   
  38.    Toy_Price int  NOT NULL,  
  39.    Image_Name VARCHAR(100)  NOT NULL,  
  40.    SalesDate DateTime  NOT NULL,  
  41.    AddedBy VARCHAR(100)  NOT NULL,  
  42. CONSTRAINT [PK_ToysSalesDetails] PRIMARY KEY CLUSTERED       
  43. (      
  44.   [Toy_ID] ASC      
  45. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]      
  46. ) ON [PRIMARY]    
  47.   
  48. GO  
  49.   
  50. --delete from ToysSalesDetails  
  51. -- Insert the sample records to the ToysDetails Table  
  52. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Spiderman',1650,'ASpiderman.png',getdate(),'Shanu')  
  53. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Spiderman',1250,'ASpiderman.png',getdate()-6,'Shanu')  
  54. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Superman',1450,'ASuperman.png',getdate(),'Shanu')  
  55. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Superman',850,'ASuperman.png',getdate()-4,'Shanu')  
  56. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Thor',1350,'AThor.png',getdate(),'Shanu')  
  57. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Thor',950,'AThor.png',getdate()-8,'Shanu')  
  58. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Wolverine',1250,'AWolverine.png',getdate(),'Shanu')  
  59. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Wolverine',450,'AWolverine.png',getdate()-3,'Shanu')  
  60. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','CaptainAmerica',1100,'ACaptainAmerica.png',getdate(),'Shanu')  
  61. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Spiderman',250,'ASpiderman.png',getdate()-120,'Shanu')  
  62. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Spiderman',1950,'ASpiderman.png',getdate()-40,'Shanu')  
  63. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Superman',1750,'ASuperman.png',getdate()-40,'Shanu')  
  64. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Thor',900,'AThor.png',getdate()-100,'Shanu')  
  65. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Thor',850,'AThor.png',getdate()-50,'Shanu')  
  66. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Wolverine',250,'AWolverine.png',getdate()-80,'Shanu')  
  67. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','CaptainAmerica',800,'ACaptainAmerica.png',getdate()-60,'Shanu')  
  68. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Superman',1950,'ASuperman.png',getdate()-80,'Shanu')  
  69. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Thor',1250,'AThor.png',getdate()-30,'Shanu')  
  70. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Action','Wolverine',850,'AWolverine.png',getdate()-20,'Shanu')  
  71.   
  72. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Lion',1250,'Lion.png',getdate(),'Shanu')  
  73. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Lion',950,'Lion.png',getdate()-4,'Shanu')  
  74. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Tiger',1900,'Tiger.png',getdate(),'Shanu')  
  75. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Tiger',600,'Tiger.png',getdate()-2,'Shanu')  
  76. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Panda',650,'Panda.png',getdate(),'Shanu')  
  77. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Panda',1450,'Panda.png',getdate()-1,'Shanu')  
  78. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Dog',200,'Dog.png',getdate(),'Shanu')  
  79.   
  80. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Lion',450,'Lion.png',getdate()-20,'Shanu')  
  81. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Tiger',400,'Tiger.png',getdate()-90,'Shanu')  
  82. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Panda',550,'Panda.png',getdate()-120,'Shanu')  
  83. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Dog',1200,'Dog.png',getdate()-60,'Shanu')  
  84. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Lion',450,'Lion.png',getdate()-90,'Shanu')  
  85. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Animal','Tiger',400,'Tiger.png',getdate()-30,'Shanu')  
  86.   
  87.   
  88. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Bird','Owl',600,'BOwl.png',getdate(),'Shanu')  
  89. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Bird','Greenbird',180,'BGreenbird.png',getdate(),'Shanu')  
  90. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Bird','Thunderbird',550,'BThunderbird-v2.png',getdate(),'Shanu')  
  91.   
  92. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Bird','Owl',600,'BOwl.png',getdate()-50,'Shanu')  
  93. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Bird','Greenbird',180,'BGreenbird.png',getdate()-90,'Shanu')  
  94. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Bird','Thunderbird',550,'BThunderbird-v2.png',getdate()-120,'Shanu')  
  95.   
  96. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Car','SingleSeater',1600,'CSingleSeater.png',getdate(),'Shanu')  
  97. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Car','Mercedes',2400,'CMercedes.png',getdate(),'Shanu')  
  98. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Car','FordGT',1550,'CFordGT.png',getdate(),'Shanu')  
  99. Insert into ToysSalesDetails(Toy_Type,Toy_Name,Toy_Price,Image_Name,SalesDate,AddedBy) values('Car','Bus',700,'CBus.png',getdate(),'Shanu')  
  100.   
  101. select *,  
  102. SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (DATENAME(month, SalesDate)  * 4) - 3, 3) as 'Month'  
  103.  from ToysSalesDetails   
  104.   Where YEAR(SalesDate)=YEAR(getdate())  
  105.   Order by Toy_Type,Toy_Name,Image_Name,SalesDate  
  106.   
  107. -- 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 
  1. -- 1) Stored procedure to Select ToysSalesDetails  
  2. -- Author      : Shanu                                                               
  3. -- Create date : 2015-11-20                                                                
  4. -- Description : Toy Sales Details                                                
  5. -- Tables used :  ToysSalesDetails                                                                
  6. -- Modifier    : Shanu                                                                 
  7. -- Modify date : 2015-11-20                                                                    
  8. -- =============================================    
  9. -- exec USP_ToySales_Select '',''  
  10. -- =============================================                                                            
  11. CREATE PROCEDURE [dbo].[USP_ToySales_Select]                                              
  12.    (                            
  13.      @Toy_Type           VARCHAR(100)     = '',  
  14.      @Toy_Name               VARCHAR(100)     = ''    
  15.       )                                                        
  16. AS                                                                
  17. BEGIN        
  18.          select  Toy_Type as ToyType  
  19.                 ,Toy_Name as ToyName  
  20.                 ,Image_Name as ImageName  
  21.                 ,Toy_Price as Price  
  22.                 ,AddedBy as 'User'  
  23.                 ,DATENAME(month, SalesDate) as 'Month'  
  24.                   
  25.          FROM ToysSalesDetails   
  26.           Where     
  27.                     Toy_Type like  @Toy_Type +'%'  
  28.                 AND Toy_Name like @Toy_Name +'%'  
  29.                 AND YEAR(SalesDate)=YEAR(getdate())  
  30.           ORDER BY  
  31.               Toy_Type,Toy_Name,SalesDate  
  32.            
  33. 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.



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



Add Database using ADO.NET Entity Data Model

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

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



Select EF Designer from the database and click Next.

 

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



Here we can see 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.

 

Click next and select the tables and all Stored Procedures need to be used and click finish.

 

Here we can see now we have created our ToySalesModel.

 
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.



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 2 different controller for Order master and order Detail.



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. 

  1. public class ToyController: ApiController  
  2. {  
  3.     ToysDBEntities objAPI = new ToysDBEntities();  
  4.     // to Search Student Details and display the result    
  5.     [HttpGet]  
  6.     public IEnumerable < USP_ToySales_Select_Result > Get(string ToyType, string ToyName)  
  7.     {  
  8.         if(ToyType == null) ToyType = "";  
  9.         if(ToyName == null) ToyName = "";  
  10.         return objAPI.USP_ToySales_Select(ToyType, ToyName)  
  11.             .AsEnumerable();  
  12.     }  
  13. }   
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”.

 

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”.



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



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.



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”. 
  1. // <reference path="../angular.js" />    
  2. /// <reference path="../angular.min.js" />     
  3. /// <reference path="../angular-animate.js" />     
  4. /// <reference path="../angular-animate.min.js" />    
  5. var app;  
  6.   
  7. (function () {  
  8.     app = angular.module("OrderModule", ['ngAnimate']);  
  9. })();  
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 need to be used. 
  1. app.controller("AngularJsOrderController", function ($scope,$sce, $timeout, $rootScope, $window, $http) {  
  2.     $scope.date = new Date();  
  3.     $scope.MyName = "shanu";  
  4.   
  5.     //For Order Master Search   
  6.     $scope.ToyType = "";  
  7.     $scope.ToyName = "";  
  8.   
  9.     // 1) Item List Arrays.This arrays will be used to display .  
  10.     $scope.itemType = [];  
  11.     $scope.ColNames = [];  
  12.   
  13.     // 2) Item List Arrays.This arrays will be used to display .  
  14.     $scope.items = [];    
  15.     $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 I have passed the search parameter of OrderNO and TableID using:
  1. { 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. 

  1. // To get all details from Database    
  2. selectToySalesDetails($scope.ToyType, $scope.ToyName);  
  3. // To get all details from Database    
  4. function selectToySalesDetails(ToyType, ToyName)  
  5. {  
  6.     $http.get('/api/Toy/',  
  7.         {  
  8.             params:  
  9.             {  
  10.                 ToyType: ToyType,  
  11.                 ToyName: ToyName  
  12.             }  
  13.         })  
  14.         .success(function (data)  
  15.         {  
  16.             $scope.ToyDetails = data;  
  17.             if($scope.ToyDetails.length > 0)  
  18.             {  
  19.                 //alert($scope.ToyDetails.length);    
  20.                 var uniqueMonth = {},  
  21.                     uniqueToyName = {},  
  22.                     i;  
  23.                 for(i = 0; i < $scope.ToyDetails.length; i += 1)  
  24.                 {  
  25.                     // For Column wise Month add    
  26.                     uniqueMonth[$scope.ToyDetails[i].Month] = $scope.ToyDetails[i];  
  27.                     //For column wise Toy Name add    
  28.                     uniqueToyName[$scope.ToyDetails[i].ToyName] = $scope.ToyDetails[i];  
  29.                 }  
  30.                 // For Column wise Month add    
  31.                 for(i in uniqueMonth)  
  32.                 {  
  33.                     $scope.ColMonths.push(uniqueMonth[i]);  
  34.                 }  
  35.                 // For Column wise ToyName add    
  36.                 for(i in uniqueToyName)  
  37.                 {  
  38.                     $scope.ColNames.push(uniqueToyName[i]);  
  39.                 }  
  40.                 // To disply the  Month wise Pivot result    
  41.                 $scope.getMonthDetails();  
  42.                 // To disply the  Month wise Pivot result    
  43.                 $scope.getToyNameDetails();  
  44.             }  
  45.         })  
  46.         .error(function ()  
  47.         {  
  48.             $scope.error = "An Error has occured while loading posts!";  
  49.         });  
  50. }   
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.

 

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. 
  1. // To Display Toy Details as Toy Name Pivot Cols       
  2.     $scope.getToyNameDetails = function () {    
  3.     
  4.         var UniqueItemName = {}, i    
  5.     
  6.         for (i = 0; i < $scope.ToyDetails.length; i += 1) {    
  7.     
  8.             UniqueItemName[$scope.ToyDetails[i].ToyType] = $scope.ToyDetails[i];    
  9.         }    
  10.         for (i in UniqueItemName) {    
  11.     
  12.             var ItmDetails = {    
  13.                 ToyType: UniqueItemName[i].ToyType    
  14.             };    
  15.             $scope.itemType.push(ItmDetails);    
  16.         }    
  17.     }   
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: 
  1. <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;border: solid 1px #659EC7;">  
  2.     <td width="20"></td>  
  3.     <td width="200" align="center"><b>ToyType</b></td>  
  4.     <td align="center" data-ng-repeat="Cols in ColNames | orderBy:'ToyName':false" style="border: solid 1px #FFFFFF; ">  
  5.         <table>  
  6.             <tr>  
  7.                 <td width="80"><b>{{Cols.ToyName}}</b></td>  
  8.             </tr>  
  9.         </table>  
  10.     </td>  
  11.     <td width="60" align="center"><b>Total</b></td>  
  12. </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
  1. <tbody data-ng-repeat="itm in itemType">  
  2.     <tr>  
  3.         <td width="20">{{$index+1}}</td>  
  4.         <td align="left" style="border: solid 1px #659EC7; padding: 5px;"> <span style="color:#9F000F">{{itm.ToyType}}</span> </td>  
  5.         <td align="center" data-ng-repeat="ColsNew in ColNames | orderBy:'ToyName':false" align="right" style="border: solid 1px #659EC7; padding: 5px;table-layout:fixed;">  
  6.             <table>  
  7.                 <tr>  
  8.                     <td align="right"> <span ng-bind-html="showToyItemDetails(itm.ToyType,ColsNew.ToyName)"></span> </td>  
  9.                 </tr>  
  10.             </table>  
  11.         </td>  
  12.         <td align="right"> <span ng-bind-html="showToyColumnGrandTotal(itm.ToyType,ColsNew.ToyName)"></span> </td>  
  13.     </tr>  
  14. </tbody>  
AngularJS part

From MVC page I will call this method to bind the resultant summary price in each row after calculation. 
  1. // To Display Toy Details as Toy Name wise Pivot Price Sum calculate     
  2. $scope.showToyItemDetails = function (colToyType, colToyName)  
  3. {  
  4.     $scope.getItemPrices = 0;  
  5.     for(i = 0; i < $scope.ToyDetails.length; i++)  
  6.     {  
  7.         if(colToyType == $scope.ToyDetails[i].ToyType)  
  8.         {  
  9.             if(colToyName == $scope.ToyDetails[i].ToyName)  
  10.             {  
  11.                 $scope.getItemPrices = parseInt($scope.getItemPrices) + parseInt($scope.ToyDetails[i].Price);  
  12.             }  
  13.         }  
  14.     }  
  15.     if(parseInt($scope.getItemPrices) > 0)  
  16.     {  
  17.         return $sce.trustAsHtml("<font color='red'><b>" + $scope.getItemPrices.toString()  
  18.             .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b></font>");  
  19.     }  
  20.     else  
  21.     {  
  22.         return $sce.trustAsHtml("<b>" + $scope.getItemPrices.toString()  
  23.             .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b>");  
  24.     }  
  25. }  
 
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. 
  1. // To Display Toy Details as Toy Name wise Pivot Column wise Total  
  2.     $scope.showToyColumnGrandTotal = function (colToyType, colToyName) {  
  3.   
  4.         $scope.getColumTots = 0;  
  5.          
  6.         for (i = 0; i < $scope.ToyDetails.length; i++) {  
  7.             if (colToyType == $scope.ToyDetails[i].ToyType) {  
  8.                 $scope.getColumTots = parseInt($scope.getColumTots) + parseInt($scope.ToyDetails[i].Price);  
  9.             }  
  10.         }  
  11.         return $sce.trustAsHtml("<font color='#203e5a'><b>" + $scope.getColumTots.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b></font>");  
  12.     }  
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. 
  1. // To Display Toy Details as Month wise Pivot Row wise Total    
  2. $scope.showToyRowTotal = function (colToyType, colToyName)  
  3. {  
  4.     $scope.getrowTotals = 0;  
  5.     for(i = 0; i < $scope.ToyDetails.length; i++)  
  6.     {  
  7.         if(colToyName == $scope.ToyDetails[i].ToyName)  
  8.         {  
  9.             $scope.getrowTotals = parseInt($scope.getrowTotals) + parseInt($scope.ToyDetails[i].Price);  
  10.         }  
  11.     }  
  12.     return $sce.trustAsHtml("<font color='#203e5a'><b>" + $scope.getrowTotals.toString()  
  13.         .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b></font>");  
  14. }  
Row and Column Grand Total: To Calculate both Row and Column Grand Total. 
  1. // To Display Toy Details as Month wise Pivot Row & Column Grand Total    
  2. $scope.showToyGrandTotals = function (colToyType, colToyName)  
  3. {  
  4.     $scope.getGrandTotals = 0;  
  5.     if($scope.ToyDetails && $scope.ToyDetails.length)  
  6.     {  
  7.         for(i = 0; i < $scope.ToyDetails.length; i++)  
  8.         {  
  9.             $scope.getGrandTotals = parseInt($scope.getGrandTotals) + parseInt($scope.ToyDetails[i].Price);  
  10.         }  
  11.     }  
  12.     return $sce.trustAsHtml("<b>" + $scope.getGrandTotals.toString()  
  13.         .replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "</b>");  
  14. }  
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. Same like 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.



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. 
  1. <input type="text" name="txtToyType" ng-model="ToyType" value="" />  
  2. <input type="text" name="txtToyName" ng-model="ToyName" />  
  3. <input type="submit" value="Search" style="background-color:#336699;color:#FFFFFF" ng-click="searchToySales()" />  
  4. //Search  
  5.     $scope.searchToySales = function () {  
  6.         // 1) Item List Arrays.This arrays will be used to display .  
  7.         $scope.itemType = [];  
  8.         $scope.ColNames = [];  
  9.   
  10.         // 2) Item List Arrays.This arrays will be used to display .  
  11.         $scope.items = [];  
  12.         $scope.ColMonths = [];  
  13.   
  14.         selectToySalesDetails($scope.ToyType, $scope.ToyName);  
  15.     }  
 

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


Similar Articles