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