Dynamic Pivot Grid Using MVC, AngularJS and WEB API
Note: You can download the Source Code from the link ** Source Code Download Link**
**
**
Introduction
https://code.msdn.microsoft.com/site/view/file/145074/1/1.gif
In this Article we will see in detail of how to create a simple MVC Pivot HTML grid using AngularJS. In my previous article I have explained about how to create a Dynamic Project Scheduling 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 as 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 below 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 be look like this.
Toy Sales Detail Table
https://code.msdn.microsoft.com/site/view/file/145075/1/01.PNG
Here we can see there is total 11 Records. There is repeated 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 be looks like this. ** **
Pivot with Price Sum by Toy Name
https://code.msdn.microsoft.com/site/view/file/145076/1/02.PNG
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 he pivot result Monthly starting from 07(July) to 11 (November)
Pivot with Price Sum by Monthly
https://code.msdn.microsoft.com/site/view/file/145077/1/03.PNG
In this article we will see 2 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
Visual Studio 2015 - You can download it from here.
You can also view my previous articles related to AngularJS using MVC and the WCF Rest Service.
- https://code.msdn.microsoft.com/Dynamic-scheduling-using-35328360
- https://code.msdn.microsoft.com/MVC-Angular-JS-CRUD-using-b4845edc
- https://code.msdn.microsoft.com/AngularJS-Shopping-Cart-8d4dde90
- https://code.msdn.microsoft.com/MVC-AngularJS-and-WCF-Rest-27d239b4
- https://code.msdn.microsoft.com/MVC-Web-API-and-Angular-JS-36302919
- https://code.msdn.microsoft.com/MVC-Web-API-and-Angular-JS-36302919
- https://code.msdn.microsoft.com/Image-Preview-using-MVC-792d881c
- https://code.msdn.microsoft.com/MVC-AngularJS-MasterDetail-7598c3a7
Building the Sample
Create Database and Table
First Step we 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 this script in your SQL Server. I have used SQL Server 2014.
-- =============================================
-- 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
After creating our Table we will create a Stored Procedure get all the data from database to create our Pivot Grid from our MVC application using AngularJS and Web API.
Script to create Stored Procedure
-- 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
Description
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. Click New, then Project, select Web and click ASP.NET Web Application. Select your project location and enter your web application name.
https://code.msdn.microsoft.com/site/view/file/145078/1/1.PNG
Select *MVC *and in Add Folders and Core reference for select the Web API and click OK.
https://code.msdn.microsoft.com/site/view/file/145079/1/2.PNG
Add Database using ADO.NET Entity Data Model
Right click our project and click *Add, *then New Item.
https://code.msdn.microsoft.com/site/view/file/145080/1/3.PNG
Select Data, then *ADO.NET Entity Data Model *and give the name for our EF and click Add.
https://code.msdn.microsoft.com/site/view/file/145081/1/4.PNG
Select EF Designer from the database and click Next
https://code.msdn.microsoft.com/site/view/file/145082/1/5.PNG
Here click New Connection and provide your SQL Server - Server Name and connect to your database.
https://code.msdn.microsoft.com/site/view/file/145083/1/6.PNG
Here we can see I have given my SQL Server name, Id and PWD and after it connected I selected the database as ToysDB as we have created the Database using my SQL Script.
https://code.msdn.microsoft.com/site/view/file/145084/1/7.PNG
Click next and select the tables and all Stored Procedures need to be used and click finish.
https://code.msdn.microsoft.com/site/view/file/145085/1/8.PNG
Here we can see now we have created our ToySalesModel.
https://code.msdn.microsoft.com/site/view/file/145086/1/9.PNG
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.
https://code.msdn.microsoft.com/site/view/file/145087/1/10.PNG
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.
https://code.msdn.microsoft.com/site/view/file/145088/1/11.PNG
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.
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's much easier to add our AngularJs Controller. Let's see step-by-step how to create and write our AngularJs Controller.
Creating AngularJs Controller
First create a folder inside the Script Folder and I have given the folder name as “MyAngular”.
https://code.msdn.microsoft.com/site/view/file/145090/1/13.PNG
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”.
https://code.msdn.microsoft.com/site/view/file/145091/1/14.PNG
Once the AngularJs Controller is created, we can see by default the controller will have the code with the default module definition and all.
https://code.msdn.microsoft.com/site/view/file/145092/1/15.PNG
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.
https://code.msdn.microsoft.com/site/view/file/145093/1/16.PNG
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”.
// <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
First I declared all the local variables that need to be used.
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 I have passed the search parameter of OrderNO and TableIDusing:
{ 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 the 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.
// 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 disply the Month wise Pivot result
$scope.getMonthDetails();
// To disply the Month wise Pivot result
$scope.getToyNameDetails();
}
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
}
First I will bind all the Actual data from 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.
https://i1.code.msdn.s-msft.com/dynamic-pivot-grid-using-b7e15f2c/image/file/145094/1/17.png
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.
// 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 in Arrays which will be 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 display the Toy Type and all other Toy Name as column dynamically using data-ng-repeat="Cols in ColNames | orderBy:'ToyName':false"
HTML Part:
<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 summery of price in each appropriate columns ** **
HTML Part:
<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.
// 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>");
}
}
https://i1.code.msdn.s-msft.com/dynamic-pivot-grid-using-b7e15f2c/image/file/145097/1/18.png
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.
// 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.
// 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.
// 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 the final put will be looks 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.
https://i1.code.msdn.s-msft.com/dynamic-pivot-grid-using-b7e15f2c/image/file/145098/1/19.png
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 value and rebind all the Pivot Grid with new result.
<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);
}
https://i1.code.msdn.s-msft.com/dynamic-pivot-grid-using-b7e15f2c/image/file/145099/1/20.png
Note: You can download the Source Code from the link ** Source Code Download Link **
Resources: