In this article, we will see in detail how to create a simple MVC Pivot HTML Grid using AngularJS and Web API 2.
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
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
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
In this article, we will see two kinds of Pivot reports.
- Pivot result to display the Price Sum by Toy Name for each Toy Type
- 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.
USE MASTER;
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
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
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 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
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.
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 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.
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 two different controllers 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.
public class ToyController: ApiController
{
ToysDBEntities objAPI = new ToysDBEntities();
[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”.
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”.
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.
app.controller("AngularJsOrderController",
function ($scope,$sce, $timeout, $rootScope, $window, $http) {
$scope.date = new Date();
$scope.MyName = "shanu";
$scope.ToyType = "";
$scope.ToyName = "";
$scope.itemType = [];
$scope.ColNames = [];
$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:
{ 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.
selectToySalesDetails($scope.ToyType, $scope.ToyName);
function selectToySalesDetails(ToyType, ToyName)
{
$http.get('/api/Toy/',
{
params:
{
ToyType: ToyType,
ToyName: ToyName
}
})
.success(function (data)
{
$scope.ToyDetails = data;
if($scope.ToyDetails.length > 0)
{
var uniqueMonth = {},
uniqueToyName = {},
i;
for(i = 0; i < $scope.ToyDetails.length; i += 1)
{
uniqueMonth[$scope.ToyDetails[i].Month] = $scope.ToyDetails[i];
uniqueToyName[$scope.ToyDetails[i].ToyName] = $scope.ToyDetails[i];
}
for(i in uniqueMonth)
{
$scope.ColMonths.push(uniqueMonth[i]);
}
for(i in uniqueToyName)
{
$scope.ColNames.push(uniqueToyName[i]);
}
$scope.getMonthDetails();
$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.
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.
$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
<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
<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.
$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>");
}
}
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.
$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.
$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:
$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.
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.
<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);
}
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
