public void QueryTest()
{
var pvtData = new PivotData(new[] { "a", "b", "d" },
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("d")
),
false);
pvtData.ProcessData(DataUtils.getSampleData(10000), DataUtils.getProp);
var q = new SliceQuery(pvtData).Dimension("a").Where("a", "val1", "val2").Measure(0);
var pvtDataRes1 = q.Execute(false);
Assert.True(pvtDataRes1.AggregatorFactory is CountAggregatorFactory);
Assert.Equal(1, pvtDataRes1.Dimensions.Length);
Assert.Equal(2, pvtDataRes1.GetDimensionKeys()[0].Length);
Assert.Equal(((object[])pvtData["val1", Key.Empty, Key.Empty].Value)[0], pvtDataRes1["val1"].Value);
var grandTotalCount = Convert.ToInt32(((object[])pvtData[Key.Empty, Key.Empty, Key.Empty].Value)[0]);
var q2 = new SliceQuery(pvtData)
.Dimension("d")
.Measure(
new SumAggregatorFactory("i"), // since this is derived measure field name actually may not match source data fields
(sourceAggr) => {
var cntAggr = sourceAggr.AsComposite().Aggregators[0];
return(new SumAggregator("i", new object[] { cntAggr.Count, Convert.ToDecimal(cntAggr.Value) / grandTotalCount * 100 }));
}
);
var pvtDataRes2 = q2.Execute(true);
Assert.Equal(100M, pvtDataRes2[ValueKey.Empty1D].Value);
Assert.Equal(1M, pvtDataRes2[0].Value);
}
static void Main(string[] args)
{
// sample dataset
var ordersTable = GetOrdersTable();
// build data cube by DataTable
var ordersPvtData = new PivotData(new[] { "product", "year" },
new CompositeAggregatorFactory(
new SumAggregatorFactory("quantity"),
new SumAggregatorFactory("total"),
new AverageAggregatorFactory("total")
));
ordersPvtData.ProcessData(new DataTableReader(ordersTable));
// lets calculate simple expression-based formula measure
var dynFormula = new DynamicFormulaMeasure("sumoftotal / sumofquantity", ordersPvtData);
var resPvtData = new SliceQuery(ordersPvtData)
.Measure("Weighted Total", dynFormula.GetFormulaValue, dynFormula.GetParentMeasureIndexes())
.Execute();
// now resPvtData has only one measure calculated by the formula
foreach (var k in resPvtData.GetDimensionKeys(new[] { "year" })[0])
{
Console.WriteLine("Weighted total for [{0}]: {1:0.##}", k, resPvtData[Key.Empty, k].Value);
}
Console.WriteLine("Weighted Grand Total: {0:0.##}", resPvtData[Key.Empty, Key.Empty].Value);
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
public void FormulaTest()
{
// simple test for 1-measure
var pvtData1 = new PivotData(new[] { "a", "d" },
new SumAggregatorFactory("b"),
false);
pvtData1.ProcessData(DataUtils.getSampleData(1000), DataUtils.getProp);
var q1 = new SliceQuery(pvtData1).Dimension("a").Measure("a*2", (paramAggrs) => {
return(Convert.ToDecimal(paramAggrs[0].Value) * 2);
}, new[] { 0 });
var pvt1Res = q1.Execute();
Assert.Equal(
((decimal)pvtData1["val1", Key.Empty].Value) * 2,
pvt1Res["val1"].Value);
// test for composite aggregator
var pvtData = new PivotData(new[] { "a", "d" },
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("b")
),
false);
pvtData.ProcessData(DataUtils.getSampleData(1000), DataUtils.getProp);
var q = new SliceQuery(pvtData);
q.Dimension("a");
q.Measure(1);
q.Measure("a*2", (paramAggrs) => {
return(Convert.ToDecimal(paramAggrs[0].Value) * 2);
}, new[] { 1 });
var pvtRes = q.Execute();
Assert.Equal(
((decimal)pvtData["val1", Key.Empty].AsComposite().Aggregators[1].Value) * 2,
pvtRes["val1"].AsComposite().Aggregators[1].Value);
// check that calculated measures are merged correctly
pvtRes.ProcessData(DataUtils.getSampleData(10), DataUtils.getProp);
Assert.Equal(
((decimal)pvtRes["val1"].AsComposite().Aggregators[0].Value) * 2,
pvtRes["val1"].AsComposite().Aggregators[1].Value);
}
public ActionResult PivotTable()
{
var pvtData = GetDataCube();
// slice data cube with SliceQuery class
var filterByCategories = new [] { "web", "software", "hardware" };
var slicedPvtData = new SliceQuery(pvtData)
.Dimension("funded_year_quarter")
.Dimension("category")
.Where("category", filterByCategories).Measure(1).Execute();
// illustrates how to build classic 2D pivot table
var pvtTbl = new PivotTable(
new [] { "funded_year_quarter" }, // rows
new [] { "category" },
slicedPvtData);
return(PartialView(pvtTbl));
}
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));
}
static void Main(string[] args)
{
Console.Write("CsvDemo: illustrates how to use CSV file as input for PivotData and use several aggregators at once\nInput data: TechCrunch funds raised facts\n\n");
var csvConfig = new CsvConfiguration()
{
Delimiter = ",",
HasHeaderRecord = true
};
var file = "TechCrunchcontinentalUSA.csv";
using (var fileReader = new StreamReader(file)) {
var csvReader = new CsvReader(fileReader, csvConfig);
var fldToIdx = new Dictionary <string, int>();
// accessor for field values
Func <object, string, object> getValue = (r, f) => {
if (f == "fundedDate-year")
{
var foundedDate = ((string[])r)[fldToIdx["fundedDate"]];
DateTime dt;
if (DateTime.TryParse(foundedDate, out dt))
{
return(dt.Year);
}
else
{
return(null);
}
}
var csvColVal = ((string[])r)[fldToIdx[f]];
if (f == "raisedAmt")
{
return(Decimal.Parse(csvColVal));
}
return(csvColVal); // just return csv value
};
var pivotData = new PivotData(new [] { "category", "fundedDate-year", "round" },
new CompositeAggregatorFactory(
new IAggregatorFactory[] {
new SumAggregatorFactory("raisedAmt"),
new AverageAggregatorFactory("raisedAmt"),
new MaxAggregatorFactory("raisedAmt")
}
),
true // lazy totals
);
// calculate in-memory cube
pivotData.ProcessData(readCsvRows(csvReader, fldToIdx), getValue);
// lets show total raised by round
Console.WriteLine("Total raised $$ by round:");
var byRoundPivotData = new SliceQuery(pivotData).Dimension("round").Execute(); // slice by specific dimension
foreach (var round in byRoundPivotData.GetDimensionKeys()[0])
{
Console.WriteLine("Round '{0}': ${1:0.#}M", round,
GetMln(((object[])byRoundPivotData[round].Value)[0] /* 1st aggregator */));
}
Console.WriteLine("\nMax/Avg raised by year:");
var byYearPivotData = new SliceQuery(pivotData).Dimension("fundedDate-year").Execute();
foreach (var year in byYearPivotData.GetDimensionKeys()[0])
{
Console.WriteLine("Year {0}: max=${1:0.#}M avg=${2:0.#}M", year,
GetMln(((object[])byYearPivotData[year].Value)[2] /* 3rd aggregator */),
GetMln(((object[])byYearPivotData[year].Value)[1] /* 2nd aggregator */)
);
}
Console.WriteLine("\n\nPress any key to exit...");
Console.ReadKey();
}
}
// 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")
//);
}
static void Main(string[] args)
{
// sample dataset
var ordersTable = GetOrdersTable();
// build data cube by DataTable
var ordersPvtData = new PivotData(new[] { "product", "country", "year", "month", "day" },
new CompositeAggregatorFactory(
new SumAggregatorFactory("quantity"),
new SumAggregatorFactory("total"),
new AverageAggregatorFactory("total")
));
ordersPvtData.ProcessData(new DataTableReader(ordersTable));
// query 1: select products from USA and Canada greater than $50
var northAmericaBigOrdersQuery = new SliceQuery(ordersPvtData)
.Dimension("product")
.Dimension("country")
.Where("country", "USA", "Canada")
.Where((dp) => {
// filter by measure value (index #1 => sum of total)
return(ConvertHelper.ConvertToDecimal(dp.Value.AsComposite().Aggregators[1].Value, 0M) > 50);
})
.Measure(1); // include only "sum of total" measure
var northAmericaPvtData = northAmericaBigOrdersQuery.Execute();
// resulting data cube:
// dimensions = {"product", "country"}, country dimension keys = {"USA", "Canada"}
// aggregator = sum of total
Console.WriteLine("North america big orders grand total: ${0:0.00}", northAmericaPvtData[Key.Empty, Key.Empty].Value);
Console.WriteLine("\tUSA grand total: ${0:0.00}", northAmericaPvtData[Key.Empty, "USA"].Value);
Console.WriteLine("\tCanada grand total: ${0:0.00}", northAmericaPvtData[Key.Empty, "Canada"].Value);
Console.WriteLine();
// query 2: calculated (formula) measure
// average item price = sum of total / sum of quantity
var avgItemPriceByYearQuery = new SliceQuery(ordersPvtData)
.Dimension("year")
.Measure("Avg item price",
(aggrArgs) => {
var sumOfTotal = ConvertHelper.ConvertToDecimal(aggrArgs[0].Value, 0M); // value of first argument (from measure #1)
var sumOfQuantity = ConvertHelper.ConvertToDecimal(aggrArgs[1].Value, 0M); // value of second argument (from measure #0)
if (sumOfQuantity == 0)
{
return(0M); // prevent div by zero
}
return(sumOfTotal / sumOfQuantity);
},
new int[] { 1, 0 } // indexes of measures for formula arguments
);
var avgItemPriceByYearPvtData = avgItemPriceByYearQuery.Execute();
Console.WriteLine("Average item price by years:");
foreach (var year in avgItemPriceByYearPvtData.GetDimensionKeys()[0])
{
Console.WriteLine("\t {0}: ${1:0.00}", year, avgItemPriceByYearPvtData[year].Value);
}
Console.WriteLine("\tTotal: ${0:0.00}", avgItemPriceByYearPvtData[Key.Empty].Value);
// query 3: calculated dimension
// lets introduce 'region' (calculated by 'country') with 2 possible values: North America, Europe
var regionQuery = new SliceQuery(ordersPvtData)
.Dimension("year")
.Dimension("region",
(dimKeys) => {
var country = dimKeys[1]; // depends on ordersPvtData configuration: index #1 is 'country'
if (country.Equals("USA") || country.Equals("Canada"))
{
return("North America");
}
return("Europe");
}
); // note that if measure selectors are not defined, measures remains unchanged
var regionPvtData = regionQuery.Execute();
var regionPvtTbl = new PivotTable(
new[] { "year" }, // row dimension
new[] { "region" }, // column dimension
regionPvtData);
Console.WriteLine("\nTotals by region:");
Console.Write("\t\t\t");
foreach (var colKey in regionPvtTbl.ColumnKeys)
{
Console.Write("{0}\t", colKey);
}
Console.WriteLine();
for (int r = 0; r < regionPvtTbl.RowKeys.Length; r++)
{
Console.Write("\t{0}:", regionPvtTbl.RowKeys[r]);
for (int c = 0; c < regionPvtTbl.ColumnKeys.Length; c++)
{
Console.Write("\t${0:######.00}", regionPvtTbl[r, c].AsComposite().Aggregators[1].Value);
}
Console.WriteLine();
}
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
