Introduction
In this post, I am going to explain How to implement Drill down functionality in Google Chart in MVC application.Reporting is one of the most important in recent days, But your report should be more attractive and useful and clean.
If you show huge data in your report it will be unreadable, So here in this article I have explained how to implement Drill down functionality in Google Chart.
Google chart tools are powerful, simple to use, and free. Google has provided lots of Example with very good documentation for implement google chart to our application and also provided options for Configure an extensive set of options to perfectly match the look and feel of your website.
Here In this example I would like to explain, how to implement Drill down functionality in Google Chart in mvc application.
Drilldown opens up a new data set to explore with more granularity. Drilldown allow your users to focus in on the “data within the data” within a single chart area.
Steps :
Step - 1: Create New Project.
Go to File > New > Project > Select asp.net MVC4 web application > Entry Application Name > Click OK > Select Basic Application > Select view engine Razor > OKStep-2: Add a Database.
Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.Step-3: Create table for fetch data.
Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.
In this example, I have used one tables as below
Step-4: Add Entity Data Model.
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.
Step-5: Create a Controller.
Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.Here I have created a controller "HomeController"
Step-6: Add new action into your controller for Get View
Here I have added "Index" Action into "Home" Controller. Please write this following codepublic ActionResult Index()
{
return View();
}
Step-7: Add another action into your controller for Get 1st level (here Sales data year wise)
Here I have added "SalesDataYearWise" Action into "Home" Controller. Please write this following codepublic JsonResult SalesDataYearWise()
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var v = (from a in dc.SalesRecords
group a by a.SaleDate.Year into g
select new
{
Year = g.Key,
Electronics = g.Sum(a=>a.Electronics),
BookAndMedia = g.Sum(a=>a.BookAndMedia),
HomeAndKitchen = g.Sum(a=>a.HomeAndKitchen)
});
if (v != null)
{
var chartData = new object[v.Count() + 1];
chartData[0] = new object[]
{
"Year",
"Electronics",
"Book And Media",
"Home And Kitchen"
};
int j = 0;
foreach (var i in v)
{
j++;
chartData[j] = new object[] { i.Year.ToString(), i.Electronics, i.BookAndMedia, i.HomeAndKitchen };
}
return new JsonResult { Data = chartData, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
}
return new JsonResult { Data = null, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
Step-8: Add another action into your controller for Get 2nd level (here Sales data month wise)
Here I have added "SalesDataMonthWise" Action into "Home" Controller. Please write this following codepublic JsonResult SalesDataMonthWise(int year)
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var v = (from a in dc.SalesRecords
where a.SaleDate.Year.Equals(year)
group a by a.SaleDate.Month into g
select new
{
Month = g.Key,
Electronics = g.Sum(a=>a.Electronics),
BookAndMedia = g.Sum(a=>a.BookAndMedia),
HomeAndKitchen = g.Sum(a=>a.HomeAndKitchen)
});
if (v != null)
{
var chartData = new object[12 + 1];
chartData[0] = new object[]
{
"Month",
"Electronics",
"Book And Media",
"Home And Kitchen"
};
for (int i = 1; i <= 12; i++)
{
string monthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i);
var monthData = v.Where(a => a.Month.Equals(i)).FirstOrDefault();
if (monthData != null)
{
chartData[i] = new object[] {monthName, monthData.Electronics, monthData.BookAndMedia, monthData.HomeAndKitchen };
}
else
{
chartData[i] = new object[] {monthName, 0,0,0 };
}
}
return new JsonResult { Data = chartData, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
}
return new JsonResult { Data = null, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
Step-9: Add another action into your controller for Get 3rd level (here Sales data days wise)
Here I have added "SalesDataDayWise" Action into "Home" Controller. Please write this following codepublic JsonResult SalesDataDayWise(int year, string month)
{
int monthNumber = DateTime.ParseExact(month, "MMMM", CultureInfo.InvariantCulture).Month;
int days = DateTime.DaysInMonth(year, monthNumber);
List<SalesRecord> sr = new List<SalesRecord>();
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
sr = (from a in dc.SalesRecords
where a.SaleDate.Year.Equals(year) && a.SaleDate.Month.Equals(monthNumber)
select a).ToList();
if (sr != null)
{
var chartData = new object[days + 1];
chartData[0] = new object[]
{
"Month",
"Electronics",
"Book And Media",
"Home And Kitchen"
};
for (int i = 1; i <= days; i++)
{
var daysData = sr.Where(a => a.SaleDate.Day.Equals(i)).FirstOrDefault();
if (daysData != null)
{
chartData[i] = new object[] { i.ToString(), daysData.Electronics, daysData.BookAndMedia, daysData.HomeAndKitchen };
}
else
{
chartData[i] = new object[] {i.ToString(), 0,0,0 };
}
}
return new JsonResult { Data = chartData, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
}
return new JsonResult { Data = null, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
Step-10: Add view for your Action & design for show google chart with drill down functionality.
Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select View Engine (Razor) >Add.View (Html Code)
@{
ViewBag.Title = "Index";
}
<h2>Google Drill Down Chart</h2>
<div id="chart" style="width:80%; height:300px">
</div>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
@section scripts{
<script>
var chartDataYear;
var chartDataMonth;
var chartDataDays;
var GlobalYear;
var CurrentReport;
google.load("visualization", "1", { packages: ["corechart"] });
//Load Year Wise Report On Load
$(document).ready(function () {
$.ajax({
url: '/home/SalesDataYearWise',
type: 'get',
dataType: 'json',
data: {},
success: function (d) {
chartDataYear = d;
},
error: function () {
}
}).done(function () {
CurrentReport = 'year';
generateChart(chartDataYear, "Sales Report Data", selectYear);
});
});
function generateChart(chartData, title, selectEvent) {
var data = google.visualization.arrayToDataTable(chartData);
var options = {
title: title,
pointSize: 5,
focusTarget: "category",
hAxis: {showTextEvery: 1}
}
var lineChart = new google.visualization.LineChart(document.getElementById('chart'));
if (selectEvent != null) {
google.visualization.events.addListener(lineChart, 'select', function () {
selectEvent(lineChart, data);
});
}
GenerateBack(CurrentReport);
lineChart.draw(data, options);
}
function GenerateBack(currentReport) {
$('.backButtonContainer').remove();
//Create new Back Button
if (currentReport != 'year') { //back button except year
var $back = $('<button/>')
.text('Back')
.addClass('reportBack')
.click(function () {
switch (currentReport) {
case 'month':
CurrentReport = 'year';
generateChart(chartDataYear, "Sales Report Data", selectYear);
break;
case 'days':
CurrentReport = 'month';
generateChart(chartDataMonth, "Sales Report Data of year " + GlobalYear, selectMonth);
break;
default:
}
$(this).parent().remove();
});
var $div = $('<div/>')
.addClass('backButtonContainer')
.css({ 'width': '70%', 'text-align': 'right', 'padding': '5px' })
.append($back);
$('#chart').before($div);
}
}
function selectYear(chart, data) {
//Year Event (after select year data from chart)
var selectedItem = chart.getSelection()[0];
if (selectedItem) {
var rowLevel = parseInt(data.getValue(selectedItem.row, 0));
var chartData = null;
$.ajax({
url: '/Home/SalesDataMonthWise',
type: 'get',
dataType: 'json',
data: {'year': rowLevel},
success: function (d) {
chartDataMonth = d;
},
error: function () {
alert("Error!");
}
}).done(function () {
CurrentReport = 'month';
GlobalYear = rowLevel;
generateChart(chartDataMonth, "Sales Report Data of year " + rowLevel, selectMonth);
});
}
}
function selectMonth(chart, data) {
//Month Event (after select month data from chart)
//Load Chart Day wise
var selectedItem = chart.getSelection()[0];
if (selectedItem) {
var rowLevel = data.getValue(selectedItem.row, 0);
$.ajax({
url: '/home/SalesDataDayWise',
type: 'get',
dataType: 'json',
data: {'year': GlobalYear, 'month': rowLevel},
success: function (d) {
chartDataDays = d;
},
error: function () {
alert('Error!');
}
}).done(function () {
CurrentReport = 'days';
generateChart(chartDataDays, "Sales report data of " + rowLevel + " '" + GlobalYear, null);
});
}
}
</script>
}
Step-11: Here I have Added jquery code into the view for load data and show as chart.
I have added the following function at client side in Jquery ....- generateChart : This is the main function for render google chart.
- GenerateBack : This is the function for generate Back button. As this is a drill-down chart, So if we want to go back to the upper level we must have a back button.
- selectYear : This is a function for fetch & show 2nd level (here month wise sales data) data in google chart. This function will execute after click on chart (1st level chart)
- selectMonth : This is a function for fetch & show 3rd level (here days wise sales data) data in google chart. This function will execute after click on chart (2nd level chart)
Step-12: Run Application.
