public ActionResult PivotTable(int year)
{
var context = new PivotContext();
var pvtData = GetDataCube();
// in this example filter is applied to in-memory data cube
// for large datasets it may be applied on database level (with SQL WHERE expression)
var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute();
// render pivot table HTML
var pvtTbl = new PivotTable(
new[] { "Country" }, // rows
new[] { "Order Year", "Order Month" }, // cols
filteredPvtData
);
// sort by row total
pvtTbl.SortRowKeys(null,
1, // lets order by measure #1 (sum of unit price)
System.ComponentModel.ListSortDirection.Descending);
var strHtmlWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
pvtHtmlWr.Write(pvtTbl);
context.PivotTableHtml = strHtmlWr.ToString();
// prepare data for pie chart (total sum by country)
var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute();
var chartPvtTbl = new PivotTable(new[] { "Country" }, null, pvtDataForChart);
// sort by row total
chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending);
var strJsonWr = new StringWriter();
var jsonWr = new PivotTableJsonWriter(strJsonWr);
jsonWr.Write(chartPvtTbl);
context.PivotTableJson = strJsonWr.ToString();
return(PartialView(context));
}
// string GetData(IEnumerable<Dictionary<string,object>> inputData) {
// var pvtData = new PivotData(
// new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table
// new SumAggregatorFactory("Count"),
// true);
// pvtData.ProcessData( inputData ); // use appropriate overload for different data sources
// var pvtTbl = new PivotTable(
// new[] {"Tournament","Category"}, // rows
// new[] {"Gender", "Player"},
// pvtData
// );
//
// var strWr = new StringWriter();
// var htmlPvtTblWr = new PivotTableHtmlWriter(strWr);
// htmlPvtTblWr.Write(pvtTbl);
//
// return strWr.ToString();
// }
public override IDisplayResult Display(ChartVisualProfile profile, IUpdateModel updater)
{
var context = new PivotContext();
// var json = new System.Web.Script.Serialization.JavaScriptSerializer();
// var inputData = json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
//var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
var converted = JsonConvert.DeserializeObject(profile.QueryResult.ToString());
var inputData = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(converted);
// in this example filter is applied to in-memory data cube
// for large datasets it may be applied on database level (with SQL WHERE expression)
// var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute();
// var pvtData = new PivotData(
// new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table
// new SumAggregatorFactory("Count"),
// true);
try
{
// lets define derived fields for 'OrderDate' to get separate 'Year' and 'Month'
// var derivedValSource = new DerivedValueSource(inputData);
// derivedValSource.Register("Order Year", new DatePartValue("OrderDate").YearHandler );
// derivedValSource.Register("Order Month", new DatePartValue("OrderDate").MonthNumberHandler );
// configuration of the serialized cube
var pvtData = new PivotData(new[] { "Country", "Order Year", "Order Month" },
// lets define 2 measures: count and sum of UnitPrice
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("UnitPrice")
));
pvtData.ProcessData(inputData); // use appropriate overload for different data sources
var filteredPvtData = pvtData;// new SliceQuery(pvtData).Where("Order Year",1996 ).Execute();
// prepare data for pie chart (total sum by country)
var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute();
var chartPvtTbl = new PivotTable(new[] { "Country" }, null, pvtDataForChart);
// sort by row total
chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending);
var strJsonWr = new StringWriter();
var jsonWr = new PivotTableJsonWriter(strJsonWr);
jsonWr.Write(chartPvtTbl);
context.PivotTableJson = strJsonWr.ToString();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
// var pvtTbl = new PivotTable(
// new[] {"Country"}, // rows
// new[] {"Order Year", "Order Month"}, // cols
// filteredPvtData
// );
// render pivot table HTML
// var pvtTbl = new PivotTable(
// new[] {"Country"}, // rows
// new[] {"Order Year", "Order Month"} , // cols
// pvtData// filteredPvtData
// );
// sort by row total
// pvtTbl.SortRowKeys(null,
// 1, // lets order by measure #1 (sum of unit price)
// System.ComponentModel.ListSortDirection.Descending);
// var strHtmlWr = new StringWriter();
// var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
// pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
// pvtHtmlWr.Write(pvtTbl);
// context.PivotTableHtml = strHtmlWr.ToString();
return(Combine(
Initialize <ChartVisualProfileViewModel>("ChartVisualProfile", model =>
{
model.PivotContext = context;
model.QueryResult = profile.QueryResult;
model.AutoStart = profile.AutoStart;
model.Controls = profile.Controls;
model.Indicators = profile.Indicators;
model.Interval = profile.Interval;
model.Keyboard = profile.Keyboard;
model.Pause = profile.Pause;
model.Wrap = profile.Wrap;
})
.Location("Detail", "Content:1")//,
//Shape<TableLayoutProfileViewModel>("TableLayoutProfile_Summary", model =>
// {
// model.AutoStart = profile.AutoStart;
// model.Controls = profile.Controls;
// model.Indicators = profile.Indicators;
// model.Interval = profile.Interval;
// model.Keyboard = profile.Keyboard;
// model.Pause = profile.Pause;
// model.Wrap = profile.Wrap;
// })
// .Location("Summary", "Meta:5")
));
//return Combine(
// Shape("TableLayoutProfile_SummaryAdmin", model =>
// {
// model.Profile = profile;
// }).Location("Content:5")
// //Shape("BootStrapProfile_Buttons_SummaryAdmin", model =>
// //{
// // model.Profile = profile;
// //}).Location("Actions:2")
//);
}
