//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 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); }
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); } }