public AggregateNode(ITupleSource leftSource, IObjectSource rightSource, string name, ExpressionMap expressionMap, IAggregatorFactory aggregatorFactory) : base(leftSource, rightSource) { _name = name; _expressionMap = expressionMap; _aggregatorFactory = aggregatorFactory; }
/// <summary> /// Initialises a new instance of the <see cref="CensusProcessor" /> /// class. /// </summary> /// <param name="aggregationFieldsCache"> /// An instance of <see cref="AggregationFieldsCache" />. /// </param> /// <param name="aggregatorFactory"> /// An instance of type <see cref="IAggregatorFactory" />. /// </param> /// <param name="censusAdapter"> /// An instance of type <see cref="ICensusAdapter" />. /// </param> /// <param name="censusProcessorSettingsProvider"> /// An instance of type /// <see cref="ICensusProcessorSettingsProvider" />. /// </param> /// <param name="datasetQueryFilesStorageAdapter"> /// An instance of type /// <see cref="IDatasetQueryFilesStorageAdapter" />. /// </param> /// <param name="loggerWrapper"> /// An instance of type <see cref="ILoggerWrapper" />. /// </param> /// <param name="translationApiAdapter"> /// An instance of type <see cref="ITranslationApiAdapter" />. /// </param> public CensusProcessor( AggregationFieldsCache aggregationFieldsCache, IAggregatorFactory aggregatorFactory, ICensusAdapter censusAdapter, ICensusProcessorSettingsProvider censusProcessorSettingsProvider, IDatasetQueryFilesStorageAdapter datasetQueryFilesStorageAdapter, ILoggerWrapper loggerWrapper, ITranslationApiAdapter translationApiAdapter) { if (censusProcessorSettingsProvider == null) { throw new ArgumentNullException( nameof(censusProcessorSettingsProvider)); } this.aggregatorFactory = aggregatorFactory; this.aggregationFieldsCache = aggregationFieldsCache; this.censusAdapter = censusAdapter; this.datasetQueryFilesStorageAdapter = datasetQueryFilesStorageAdapter; this.loggerWrapper = loggerWrapper; this.translationApiAdapter = translationApiAdapter; this.aggregationFieldsAdapterName = censusProcessorSettingsProvider.AggregationFieldsAdapterName; this.aggregationFieldsEnumerationName = censusProcessorSettingsProvider.AggregationFieldsEnumerationName; }
internal AggregateElement(IEnumerable <Declaration> declarations, Type resultType, string name, ExpressionMap expressionMap, IAggregatorFactory factory, PatternElement source) : base(declarations, resultType) { _name = name; _expressionMap = expressionMap; _factory = factory; _source = source; }
public AggregateNode(ITupleSource leftSource, IObjectSource rightSource, string name, ExpressionMap expressionMap, IAggregatorFactory aggregatorFactory, bool isSubnetJoin) : base(leftSource, rightSource) { Name = name; ExpressionMap = expressionMap; _aggregatorFactory = aggregatorFactory; _isSubnetJoin = isSubnetJoin; }
/// <summary> /// Define new measure aggregator calculated from existing cube measure(s). /// </summary> /// <param name="aggrFactory">new measure aggregator factory</param> /// <param name="createMeasure">handler that creates new measure aggregator.</param> public SliceQuery Measure(IAggregatorFactory aggrFactory, Func <IAggregator, IAggregator> createMeasure) { AggrSelectors.Add(new AggregatorSelector() { GetFactory = (factory) => aggrFactory, GetAggregator = (dp) => createMeasure(dp.Value) }); return(this); }
/// <summary> /// Define new measure aggregator calculated from existing cube measure(s). /// </summary> /// <param name="aggrFactory">new measure aggregator factory</param> /// <param name="createMeasure">handler that creates new measure aggregator by the data point (key and value).</param> public SliceQuery Measure(IAggregatorFactory aggrFactory, Func <KeyValuePair <object[], IAggregator>, IAggregator> createMeasure) { AggrSelectors.Add(new AggregatorSelector() { GetFactory = (factory) => aggrFactory, GetAggregator = createMeasure }); return(this); }
public OlapCube(IAggregatorFactory aggregatorFactory) { AggregatorFactory = aggregatorFactory; GroupBuilders = new List <IGroupBuilder>(); Aggregators = new List <IAggregator>(); GroupTitles = new List <string>(); ValueTitles = new List <string>(); ShowTotal = ShowTotal.None; TotalName = ""; }
public FilterPivotData(string[] dims, IAggregatorFactory aggr, IPivotData origPvtData, Func <KeyValuePair <object[], IAggregator>, bool> filterHandler, Func <KeyValuePair <object[], IAggregator>, object[]> keyHandler, Func <KeyValuePair <object[], IAggregator>, IAggregator> aggrHandler) { Dims = dims; Aggr = aggr; PvtData = origPvtData; FilterHandler = filterHandler; KeyHandler = keyHandler; AggrHandler = aggrHandler; }
/// <summary> /// Initializes a new instance of the <see cref="PivotData"/> with specified dimensions, aggregator and lazy totals mode. /// </summary> /// <param name="dimensions">dimensions configuration (array of dimension elements)</param> /// <param name="aggregator">measure aggregators factory</param> /// <param name="lazyTotals">if true totals are calculated on-the-fly when accessed for the first time; otherwise they are calculated during data processing</param> public PivotData(string[] dimensions, IAggregatorFactory aggregator, bool lazyTotals) { this.dimensions = dimensions ?? new string[0]; if (this.dimensions.Length > 63 && !lazyTotals) { throw new ArgumentOutOfRangeException("dimensions", "Too many dimensions for non-lazy totals: max=63"); } aggregatorFactory = aggregator; values = new Dictionary <object[], IAggregator>(new ValueKeyEqualityComparer()); totalValues = new Dictionary <object[], IAggregator>(new ValueKeyEqualityComparer()); this.lazyTotals = lazyTotals; }
public AggregateNode(ITupleSource leftSource, IObjectSource rightSource, string name, List <Declaration> declarations, ExpressionCollection expressions, IAggregatorFactory aggregatorFactory, bool isSubnetJoin) : base(leftSource, rightSource, isSubnetJoin) { Name = name; Declarations = declarations; Expressions = expressions; _aggregatorFactory = aggregatorFactory; _isSubnetJoin = isSubnetJoin; }
OfficeOpenXml.Table.PivotTable.DataFieldFunctions SuggestFunction(IAggregatorFactory aggrFactory) { if (aggrFactory is MinAggregatorFactory) { return(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Min); } else if (aggrFactory is MaxAggregatorFactory) { return(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Max); } else if (aggrFactory is AverageAggregatorFactory) { return(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Average); } return(OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Sum); // by default }
/// <summary> /// Initializes new instance of <see cref="PivotData"/> with specified dimensions configuration and calculates values for specified data /// </summary> /// <param name="dimensions">dimensions configuration (array of dimension fields)</param> /// <param name="aggregator">measure aggregators factory</param> /// <param name="data">data enumeration</param> /// <param name="getValue">record field value accessor delegate</param> /// <param name="lazyTotals">if true totals are calculated on-the-fly when accessed for the first time</param> public PivotData(string[] dimensions, IAggregatorFactory aggregator, IEnumerable data, Func <object, string, object> getValue, bool lazyTotals) : this(dimensions, aggregator, lazyTotals) { ProcessData(data, getValue); }
/// <summary> /// Initializes a new instance of the <see cref="PivotData"/> with specified dimensions configuration, aggregator, lazy totals mode and and processes data from data reader. /// </summary> /// <param name="dimensions">dimensions configuration (array of dimension fields)</param> /// <param name="aggregator">measure aggregators factory</param> /// <param name="dataReader">data represented by <see cref="IDataReader"/> instance</param> /// <param name="lazyTotals">if true totals are calculated on-the-fly when accessed for the first time</param> public PivotData(string[] dimensions, IAggregatorFactory aggregator, IDataReader dataReader, bool lazyTotals) : this(dimensions, aggregator, GetDataReaderEnumeration(dataReader), GetDataRecordValue, lazyTotals) { }
/// <summary> /// Initializes new instance of <see cref="PivotData"/> with specified dimensions, aggregator, lazy totals mode and processes specified data. /// </summary> /// <param name="dimensions">dimensions configuration (array of dimension elements)</param> /// <param name="aggregator">measure aggregators factory</param> /// <param name="data">data represented by dictionary enumeration</param> /// <param name="lazyTotals">if true totals are calculated on-the-fly when accessed for the first time</param> public PivotData(string[] dimensions, IAggregatorFactory aggregator, IEnumerable <IDictionary <string, object> > data, bool lazyTotals) : this(dimensions, aggregator, data, GetDictionaryValue, lazyTotals) { }
/// <summary> /// Initializes a new instance of the <see cref="PivotData"/> with specified dimensions and aggregator. /// </summary> /// <param name="dimensions">dimensions configuration (array of fields)</param> /// <param name="aggregator">measure aggregators factory</param> public PivotData(string[] dimensions, IAggregatorFactory aggregator) : this(dimensions, aggregator, true) { }
//This takes a DataTable and returns a PivotTable on a given field (var_to_process) by a list of Rows and Cols public static PivotTable DataTable_to_PivotTable(DataTable dtStacked, List <string> pivotRows, List <string> pivotCols, string fldname, List <AggrFx> requested_stats) { if (dtStacked.Rows.Count > 0) { List <string> keepCols = new List <string>(); keepCols.AddRange(pivotRows); keepCols.AddRange(pivotCols); keepCols.Add(fldname); IAggregatorFactory[] aggs = new IAggregatorFactory[requested_stats.Count]; CountAggregatorFactory aggrN = new CountAggregatorFactory(); AverageAggregatorFactory aggrM = new AverageAggregatorFactory(fldname); VarianceAggregatorFactory aggrSD = new VarianceAggregatorFactory(fldname, VarianceAggregatorValueType.StandardDeviation); MinAggregatorFactory aggrMin = new MinAggregatorFactory(fldname); MaxAggregatorFactory aggrMax = new MaxAggregatorFactory(fldname); SumAggregatorFactory aggrSum = new SumAggregatorFactory(fldname); ConcatAggregatorFactory aggrConcat = new ConcatAggregatorFactory(fldname); CountUniqueAggregatorFactory aggrNunq = new CountUniqueAggregatorFactory(fldname); Concat2AggregatorFactory aggrConcat2 = new Concat2AggregatorFactory(fldname); int counter = 0; foreach (AggrFx s in requested_stats) { if (s == AggrFx.M) { aggs[counter] = aggrM; } else if (s == AggrFx.SD) { aggs[counter] = aggrSD; } else if (s == AggrFx.N) { aggs[counter] = aggrN; } else if (s == AggrFx.Nunq) { aggs[counter] = aggrNunq; } else if (s == AggrFx.Min) { aggs[counter] = aggrMin; } else if (s == AggrFx.Max) { aggs[counter] = aggrMax; } else if (s == AggrFx.Sum) { aggs[counter] = aggrSum; } else if (s == AggrFx.Concat) { aggs[counter] = aggrConcat; } else if (s == AggrFx.Concat2) { aggs[counter] = aggrConcat2; } counter++; } if (aggs.Length > 1) { CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs); var cube = new PivotData(keepCols.ToArray(), compositeFactory); cube.ProcessData(new DataTableReader(dtStacked)); var allKeys = cube.GetDimensionKeys(); // returns array of keys for each dimension var pivotTable = new PivotTable( pivotRows.ToArray(), // row dimension(s) pivotCols.ToArray(), // column dimension(s) cube); return(pivotTable); } else { var cube = new PivotData(keepCols.ToArray(), aggs[0]); cube.ProcessData(new DataTableReader(dtStacked)); var allKeys = cube.GetDimensionKeys(); // returns array of keys for each dimension var pivotTable = new PivotTable( pivotRows.ToArray(), // row dimension(s) pivotCols.ToArray(), // column dimension(s) cube); return(pivotTable); } } else { return(null); } }
protected void LoadProjections_FTE(List <string> list_budgetNbrs, List <string> list_fy) { SQL_utils sql = new SQL_utils("backend"); DataTable dt = new DataTable(); string sql_code; if (1 == 1) { sql_code = "select * from fin.vwFTE_Previous_and_Projections " + " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and FY in ( '" + String.Join("','", list_fy) + "')"; } //else if (ddlFY.SelectedValue.ToString() == "Custom") //{ // sql_code = "select * from fin.vwFTE_Previous_and_Projections " + // " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and mo >= '" + txtFrom.Text + "' and mo <= '" + txtTo.Text + "'"; //} //else if (list_fy.Contains("Current Budget Yr")) //{ // sql_code = "select * from fin.vwFTE_Previous_and_Projections " + // " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and mo >= begdate and mo <= enddate "; //} dt = sql.DataTable_from_SQLstring(sql_code); sql.Close(); if (dt.Rows.Count == 0) { } else { //Name, budget, a.budgetNbr, futuremo, Salary, FringeRate List <string> rows = new List <string> { "Name" }; //List<string> cols = new List<string> { "budgetNbr", "budgetName", "BegDate", "EndDate", "PastProj" }; List <string> cols = new List <string> { "budgetNbr", "budgetName", "FY", "PastProj" }; List <string> stats = new List <string> { "Sum", "Concat" }; #region by hand List <string> keepCols = new List <string>(); keepCols.AddRange(rows); keepCols.AddRange(cols); SumAggregatorFactory sumAggr = new SumAggregatorFactory("Total_prev_mo"); ConcatAggregatorFactory concatAggr = new ConcatAggregatorFactory("FTEcode"); CountAggregatorFactory countAggr = new CountAggregatorFactory(); IAggregatorFactory[] aggs = new IAggregatorFactory[3]; aggs[0] = sumAggr; aggs[1] = concatAggr; aggs[2] = countAggr; CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs); var cube = new PivotData(keepCols.ToArray(), compositeFactory); cube.ProcessData(new DataTableReader(dt)); PivotTable pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); #endregion var htmlResult = new StringWriter(); var pvtHtmlWr = new MyHtmlWriter2(htmlResult); string[] aggr_labels = new string[] { "Sal + Bene", "FTE", "N mos" }; //pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0.##}", aggr.Value); }; pvtHtmlWr.FormatValue = (aggr, idx) => { if (aggr.GetType() == typeof(ConcatAggregator)) { int nchars = aggr.Value.ToString().Distinct().Count(); if (nchars > 1) { string newvalue = String.Format("<div class=\"FTEchange\">{0}</div>", aggr.Value); return(newvalue); } else { return(String.Format("{0}", aggr.Value)); } } else { return(String.Format("{0:0.##}", aggr.Value)); } }; pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); }; pvtHtmlWr.AllowHtml = true; pvtHtmlWr.TotalsColumn = true; pvtHtmlWr.TotalsRow = true; pvtHtmlWr.GrandTotal = true; pvtHtmlWr.Write(pivotTable); Label header = new Label(); header.Text = "<br/><br/>Staff Expenses"; header.Font.Size = 12; header.Font.Bold = true; Literal lit = new Literal(); string processedHTML = htmlResult.ToString().Replace("<", "<"); processedHTML = processedHTML.Replace(">", ">"); processedHTML = processedHTML.Replace("<td class=\" pvtMeasure1\">", "<td class=\"foo2\">"); lit.Text = processedHTML; panel_FTE.Controls.Add(header); panel_FTE.Controls.Add(lit); ToggleVisibility("Totals_FTE"); } }
//protected void LoadYr2(Panel p, List<string> rows, List<string> cols, List<string> functions, string title, bool useGTE0, bool useFromTo) //{ // DataTable dt = new DataTable(); // if (useFromTo) // { // dt = GetValantData_From_To2(rows, cols, functions, useGTE0); // } // else // { // //this branch no longer used // //dt = GetValantData(rows, cols, functions, useGTE0); // } // LoadYr2(dt, p, rows, cols, functions, title, useGTE0, useFromTo); //} protected void LoadYr2(DataTable dt, Panel p, List <string> rows, List <string> cols, List <string> functions, string title, bool useGTE0, bool useFromTo) { //DataTable dt = new DataTable(); //if (useFromTo) //{ // dt = GetValantData_From_To2(rows, cols, functions, useGTE0); //} //else //{ // //this branch no longer used // //dt = GetValantData(rows, cols, functions, useGTE0); //} List <string> keepCols = new List <string>(); keepCols.AddRange(rows); keepCols.AddRange(cols); PivotData cube; PivotTable pivotTable; string[] aggr_labels = new string[functions.Count]; if (functions.Count > 1) { IAggregatorFactory[] aggs = new IAggregatorFactory[functions.Count]; for (int f = 0; f < functions.Count; f++) { if (functions[f] == "Sum") { aggs[f] = new SumAggregatorFactory("n"); aggr_labels[f] = "sum n"; } else if (functions[f] == "Monthly Avg") { aggs[f] = new AverageAggregatorFactory("amt"); aggr_labels[f] = "Monthly Avg"; } else if (functions[f] == "N Patients") { aggs[f] = new CountUniqueAggregatorFactory("patientID"); aggr_labels[f] = "N Patients"; } else if (functions[f] == "N Patients Dx") { aggs[f] = new CountUniqueAggregatorFactory("patientID_DX"); aggr_labels[f] = "N Patients Dx"; } else if (functions[f] == "N Patients Tx") { aggs[f] = new CountUniqueAggregatorFactory("patientID_TX"); aggr_labels[f] = "N Patients Tx"; } else if (functions[f] == "N Visits") { aggs[f] = new SumAggregatorFactory("n_appt"); aggr_labels[f] = "N Visits"; } else if (functions[f] == "N Visits Dx") { aggs[f] = new SumAggregatorFactory("n_appt_dx"); aggr_labels[f] = "N Visits Dx"; } else if (functions[f] == "N Visits Tx") { aggs[f] = new SumAggregatorFactory("n_appt_Tx"); aggr_labels[f] = "N Visits Tx"; } } CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs); cube = new PivotData(keepCols.ToArray(), compositeFactory); cube.ProcessData(new DataTableReader(dt)); pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); } else { switch (functions[0]) { case "Sum": cube = new PivotData(keepCols.ToArray(), new SumAggregatorFactory("n")); break; case "Monthly Avg": cube = new PivotData(keepCols.ToArray(), new AverageAggregatorFactory("amt")); break; case "N Patients": cube = new PivotData(keepCols.ToArray(), new CountUniqueAggregatorFactory("patientID")); break; default: cube = new PivotData(keepCols.ToArray(), new CountAggregatorFactory()); break; } cube.ProcessData(new DataTableReader(dt)); pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); } var htmlResult = new StringWriter(); var pvtHtmlWr = new MyHtmlWriter2(htmlResult); if (functions.Count >= 1) { pvtHtmlWr.FormatValue = (aggr, idx) => { string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>"; return(newval); }; //pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0}", aggr.Value); }; pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); }; } else { //pvtHtmlWr.FormatValue(aggr, idx) => //{ // string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>"; // return newval; //}; } pvtHtmlWr.RowHeaderClass = "row1"; pvtHtmlWr.ColumnHeaderClass = "col1"; pvtHtmlWr.MeasureHeaderClass = "meashead"; pvtHtmlWr.SubtotalDimensions = new string[] { "Direct" }; pvtHtmlWr.SubtotalRows = true; pvtHtmlWr.AllowHtml = true; pvtHtmlWr.TotalsColumn = false; pvtHtmlWr.TotalsRow = true; pvtHtmlWr.GrandTotal = false; pvtHtmlWr.Write(pivotTable); Label header = new Label(); header.Text = "<br/>" + title; header.Font.Size = 12; header.Font.Bold = true; Literal lit = new Literal(); lit.Text = htmlResult.ToString(); p.Controls.Add(header); p.Controls.Add(lit); }
/// <summary> /// Sets aggregator factory. /// </summary> /// <param name="name">Name of the aggregator.</param> /// <param name="aggregatorFactory">Factory to create new aggregators.</param> public void AggregatorFactory(string name, IAggregatorFactory aggregatorFactory) { _name = name; _aggregatorFactory = aggregatorFactory; }
protected void LoadProjections_TOTALS(List <string> list_budgetNbrs, List <string> list_fy, List <string> rows, List <string> cols, List <string> functions) { SQL_utils sql = new SQL_utils("backend"); DataTable dt = new DataTable(); string sql_code; if (1 == 1) { sql_code = "select 1 as const, * from fin.vwProjections " + " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and FY in ( '" + String.Join("','", list_fy) + "')" + " and amttype not in ('Endowment','Revenue','BalancePrior','NonEndowment')"; } //else if (ddlFY.SelectedValue.ToString() == "Custom") //{ // sql_code = "select 1 as const, * from fin.vwOBJ_Previous_and_Projections " + // " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and mo >= '" + txtFrom.Text + "' and mo <= '" + txtTo.Text + "'"; //} //else if (list_fy.Contains("Current Budget Yr")) //{ // sql_code = "select 1 as const, * from fin.vwProjections " + // " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and BY='Current'"; //} dt = sql.DataTable_from_SQLstring(sql_code); sql.Close(); List <string> stats = new List <string> { "Sum", "N" }; List <string> keepCols = new List <string>(); keepCols.AddRange(rows); keepCols.AddRange(cols); //IAggregatorFactory[] aggs = new IAggregatorFactory[2]; //SumAggregatorFactory sumAggr = new SumAggregatorFactory("amt"); //CountAggregatorFactory countAggr = new CountAggregatorFactory(); //aggs[0] = sumAggr; //aggs[1] = countAggr; PivotData cube; PivotTable pivotTable; string[] aggr_labels = new string[functions.Count]; if (functions.Count > 1) { IAggregatorFactory[] aggs = new IAggregatorFactory[functions.Count]; for (int f = 0; f < functions.Count; f++) { if (functions[f] == "Sum") { aggs[f] = new SumAggregatorFactory("amt"); aggr_labels[f] = "Total"; } else if (functions[f] == "Monthly Avg") { aggs[f] = new AverageAggregatorFactory("amt"); aggr_labels[f] = "Monthly Avg"; } else if (functions[f] == "N Months") { aggs[f] = new CountUniqueAggregatorFactory("caldate"); aggr_labels[f] = "N Months"; } else if (functions[f] == "Count") { aggs[f] = new CountAggregatorFactory(); aggr_labels[f] = "Count"; } } CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs); cube = new PivotData(keepCols.ToArray(), compositeFactory); cube.ProcessData(new DataTableReader(dt)); pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); } else { switch (functions[0]) { case "Sum": cube = new PivotData(keepCols.ToArray(), new SumAggregatorFactory("amt")); break; case "Monthly Avg": cube = new PivotData(keepCols.ToArray(), new AverageAggregatorFactory("amt")); break; case "N Months": cube = new PivotData(keepCols.ToArray(), new CountUniqueAggregatorFactory("caldate")); break; default: cube = new PivotData(keepCols.ToArray(), new CountAggregatorFactory()); break; } cube.ProcessData(new DataTableReader(dt)); pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); } //The following assumes that there is a PivotTable object named "pivotTable" that has been created var pvtDataTableWr = new PivotTableDataTableWriter("out"); DataTable dtpivot = pvtDataTableWr.Write(pivotTable); DataColumn colcumsum = new DataColumn("cumsum", typeof(decimal)); dtpivot.Columns.Add(colcumsum); List <decimal> cumsum = new List <decimal>(); decimal runningtotal = 0; for (int i = 0; i < dtpivot.Rows.Count; i++) { string val = dtpivot.Rows[i][2].ToString(); decimal d; if (decimal.TryParse(val, out d)) { d = (d == 0) ? 0 : d; } runningtotal += d; cumsum.Add(runningtotal); dtpivot.Rows[i]["cumsum"] = runningtotal; } var htmlResult = new StringWriter(); var pvtHtmlWr = new MyHtmlWriter2(htmlResult); if (functions.Count >= 1) { pvtHtmlWr.FormatValue = (aggr, idx) => { string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>"; return(newval); }; //pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0}", aggr.Value); }; pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); }; } else { //pvtHtmlWr.FormatValue(aggr, idx) => //{ // string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>"; // return newval; //}; } pvtHtmlWr.RowHeaderClass = "row1"; pvtHtmlWr.ColumnHeaderClass = "col1"; pvtHtmlWr.MeasureHeaderClass = "meashead"; pvtHtmlWr.SubtotalDimensions = new string[] { "Direct" }; pvtHtmlWr.SubtotalRows = true; pvtHtmlWr.AllowHtml = true; pvtHtmlWr.TotalsColumn = true; pvtHtmlWr.TotalsRow = true; pvtHtmlWr.GrandTotal = true; pvtHtmlWr.Write(pivotTable); Label header = new Label(); header.Text = "<br/>Budget Totals"; header.Font.Size = 12; header.Font.Bold = true; Literal lit = new Literal(); lit.Text = htmlResult.ToString(); panel_TOTAL.Controls.Add(header); panel_TOTAL.Controls.Add(lit); ToggleVisibility("Totals"); //plot if (chkPlot.Checked == true) { Literal totplot = PlotStackedCol_TOTAL_byBudgetNbr(dt); panelPlots_TOTAL.Controls.Add(totplot); } }
protected void GetCPT(DataTable dt, List <string> rows, List <string> cols, bool showCount) { // These are all the fields that are going to be used in the cube List <string> keepCols = new List <string>(); keepCols.AddRange(rows); keepCols.AddRange(cols); // Create the cube PivotData cube;// = new PivotData(keepCols.ToArray(), compositeFactory); //Create the aggregators you want (Sum, Avg, Count, Min, Max, etc.) SumAggregatorFactory sumAggr = new SumAggregatorFactory("TotalAmount"); CountAggregatorFactory countAggr = new CountAggregatorFactory(); if (showCount) { // Put them into a composite IAggregatorFactory[] aggs = new IAggregatorFactory[2]; aggs[0] = sumAggr; aggs[1] = countAggr; CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs); // Create the cube cube = new PivotData(keepCols.ToArray(), compositeFactory); } else { SumAggregatorFactory sumFactory = new SumAggregatorFactory("TotalAmount"); // Create the cube cube = new PivotData(keepCols.ToArray(), sumFactory); } cube.ProcessData(new DataTableReader(dt)); //Create pivot table PivotTable pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); //Define how we are going to render the pivot table - here as HTML (could be Excel, CSV, etc.) var htmlResult = new StringWriter(); //var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult); var pvtHtmlWr = new MyHtmlWriter(htmlResult); // Define the labels for the dimensions //string[] aggr_labels = new string[] { "Sum", "Count" }; string[] aggr_labels = new string[] { "Sum" }; // Various properties pvtHtmlWr.SubtotalDimensions = new string[] { "PaymentOther" }; pvtHtmlWr.SubtotalRows = true; pvtHtmlWr.AllowHtml = true; pvtHtmlWr.TotalsColumn = true; pvtHtmlWr.TotalsRow = true; pvtHtmlWr.GrandTotal = true; //create the actual HTML code pvtHtmlWr.Write(pivotTable); //Place the HTML code into literal control and add it to a Panel so you can place it where you wish on the page. Literal lit = new Literal(); lit.Text = htmlResult.ToString(); panel1.Controls.Add(lit); }