public string HandleRequest([FromBody] RequestData requestData) { String requestText = Utilities.Utilities.BuildMDXRequest(requestData); using (AdomdConnection mdConn = new AdomdConnection()) { mdConn.ConnectionString = "provider=msolap;Data Source=V1LGORPC\\ASMAIN;initial catalog=AdventureWorksDW2014Multidimensional-EE;"; mdConn.Open(); AdomdCommand mdCommand = mdConn.CreateCommand(); mdCommand.CommandText = requestText; // << MDX Query CellSet cs; try { // work with CellSet cs = mdCommand.ExecuteCellSet(); } catch (AdomdErrorResponseException e) { return(e.Message); } MdxResultDataSet resultDataSet = new MdxResultDataSet(); // our method supports only 2-Axes CellSets if (cs.Axes.Count != 2) { return("Request error: axes count > 2."); } TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; for (int row = 0; row < tuplesOnRows.Count; row++) { resultDataSet.RowNameList.Add(tuplesOnRows[row].Members[0].Caption); } for (int col = 0; col < tuplesOnColumns.Count; col++) { resultDataSet.ColumnNameList.Add(tuplesOnColumns[col].Members[0].Caption); resultDataSet.Cells.Add(new List <String>()); for (int row = 0; row < tuplesOnRows.Count; row++) { if (cs.Cells[col, row].Value == null) { resultDataSet.Cells[col].Add("null"); } else { resultDataSet.Cells[col].Add(cs.Cells[col, row].Value.ToString()); } } } return(JsonConvert.SerializeObject(resultDataSet, Formatting.Indented)); //return requestText; } }
public SCMonth[] GetSalesPrMonth() { StringBuilder result = new StringBuilder(); using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube")) { conn.Open(); AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @"SELECT [Measures].[Fact Sale Count] ON COLUMNS, NONEMPTY( {[Dim Date].[Month].[Month]} ) ON ROWS FROM [F Club DW]"; CellSet cs = cmd.ExecuteCellSet(); TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; TupleCollection tupleCollection = cs.Axes[1].Set.Tuples; SCMonth[] monthSales = new SCMonth[tupleCollection.Count]; for (int row = 0; row < tupleCollection.Count; row++) { SCMonth month = new SCMonth(); month.month = int.Parse(tupleCollection[row].Members[0].Caption); for (int col = 0; col < tuplesOnColumns.Count; col++) { month.saleCount = int.Parse(cs.Cells[col, row].FormattedValue); monthSales[row] = month; } } conn.Close(); return(monthSales); } }
public IEnumerable <Category> getProductCategoriesSaleCountYearMonthDay(int year, int month, int day) { List <Category> categoryList = new List <Category>(); StringBuilder result = new StringBuilder(); string season = checkSeason(month); try { string commandtext = "SELECT {[Measures].[Sale Count]} ON Columns, " + "non empty{[Product].[Main Category].children * [Product].[Sub Category].children * [Product].[Sub Sub Category].children} " + "ON rows From [Fclub DW] where {[Date].[Hierarchy].[Day Number Of Month].&[" + day + "]&[" + year + "]&[" + season + "]&[" + month + "]}"; adomdConnection.Open(); AdomdCommand cmd = new AdomdCommand(commandtext, adomdConnection); CellSet cs = cmd.ExecuteCellSet(); TupleCollection tupleCollection = cs.Axes[0].Set.Tuples; TupleCollection tuplesOnRow = cs.Axes[1].Set.Tuples; int row = 0; foreach (var obj in tuplesOnRow) { for (int members = 0; members < tuplesOnRow[row].Members.Count; members++) { result.Append(tuplesOnRow[row].Members[members].Caption + ": "); } for (int col = 0; col < tupleCollection.Count; col++) { result.Append(cs.Cells[col, row].FormattedValue); if (col < tupleCollection.Count - 1) { result.Append(": "); } } row++; Category cat = new Category(result.ToString()); categoryList.Add(cat); result.Clear(); } } catch (Exception e) { Console.WriteLine("Errormessage: " + e.Message); return(null); } return(categoryList); }
//</snippetDemonstrateDisconnectedCellset> //<snippetReturnCommandUsingCellSet> string ReturnCommandUsingCellSet() { //Create a new string builder to store the results System.Text.StringBuilder result = new System.Text.StringBuilder(); //Connect to the local server using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;")) { conn.Open(); //Create a command, using this connection AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @" WITH MEMBER [Measures].[FreightCostPerOrder] AS [Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity], FORMAT_STRING = 'Currency' SELECT [Geography].[Geography].[Country].&[United States].Children ON ROWS, [Date].[Calendar].[Calendar Year] ON COLUMNS FROM [Adventure Works] WHERE [Measures].[FreightCostPerOrder]"; //Execute the query, returning a cellset CellSet cs = cmd.ExecuteCellSet(); //Output the column captions from the first axis //Note that this procedure assumes a single member exists per column. result.Append("\t"); TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; foreach (Tuple column in tuplesOnColumns) { result.Append(column.Members[0].Caption + "\t"); } result.AppendLine(); //Output the row captions from the second axis and cell data //Note that this procedure assumes a two-dimensional cellset TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; for (int row = 0; row < tuplesOnRows.Count; row++) { result.Append(tuplesOnRows[row].Members[0].Caption + "\t"); for (int col = 0; col < tuplesOnColumns.Count; col++) { result.Append(cs.Cells[col, row].FormattedValue + "\t"); } result.AppendLine(); } conn.Close(); return(result.ToString()); } // using connection }
private int CountTuplesCovered(TestCase test, int dimension, int feature) { int num = 0; TupleCollection tupleCollection = uncoveredTuples[dimension][feature]; for (int i = 0; i < tupleCollection.Count; i++) { if (test.IsTupleCovered(tupleCollection[i])) { num++; } } return(num); }
public IEnumerable <Product> getProductSaleCountMembers() { List <Product> productList = new List <Product>(); StringBuilder result = new StringBuilder(); try { string commandtext = "SELECT {[Measures].[Sale Count]} ON Columns, " + "non empty{[Member].[Member ID].children} ON rows From[Fclub DW]"; adomdConnection.Open(); AdomdCommand cmd = new AdomdCommand(commandtext, adomdConnection); CellSet cs = cmd.ExecuteCellSet(); TupleCollection tupleCollection = cs.Axes[0].Set.Tuples; TupleCollection tuplesOnRow = cs.Axes[1].Set.Tuples; int row = 0; foreach (var obj in tuplesOnRow) { for (int members = 0; members < tuplesOnRow[row].Members.Count; members++) { result.Append(tuplesOnRow[row].Members[members].Caption + ": "); } for (int col = 0; col < tupleCollection.Count; col++) { result.Append(cs.Cells[col, row].FormattedValue); if (col < tupleCollection.Count - 1) { result.Append(": "); } } row++; Product product = new Product(result.ToString()); productList.Add(product); result.Clear(); } } catch (Exception e) { Console.WriteLine("Errormessage: " + e.Message); return(null); } return(productList); }
public IEnumerable <Category> getProductCategories() { List <Category> categoryList = new List <Category>(); StringBuilder result = new StringBuilder(); try { string commandtext = "SELECT {} ON 0, {[Product].[Product Name].[Product Name] *[Product].[Main Category].children*[Product].[Sub Category].children*[Product].[Sub Sub Category].children} " + "on 1 From [Fclub DW]"; adomdConnection.Open(); AdomdCommand cmd = new AdomdCommand(commandtext, adomdConnection); CellSet cs = cmd.ExecuteCellSet(); TupleCollection tupleCollection = cs.Axes[0].Set.Tuples; TupleCollection tuplesOnRow = cs.Axes[1].Set.Tuples; int row = 0; foreach (var obj in tuplesOnRow) { for (int members = 0; members < tuplesOnRow[row].Members.Count; members++) { result.Append(tuplesOnRow[row].Members[members].Caption + ": "); } for (int col = 0; col < tupleCollection.Count; col++) { result.Append(cs.Cells[col, row].FormattedValue); if (col < tupleCollection.Count - 1) { result.Append(": "); } } row++; Category cat = new Category(result.ToString()); categoryList.Add(cat); result.Clear(); } } catch (Exception e) { Console.WriteLine("Errormessage: " + e.Message); return(null); } return(categoryList); }
private int CountTuplesCovered(TestCase test, int dimension, int feature) { int tuplesCovered = 0; TupleCollection tuples = this.uncoveredTuples[dimension][feature]; for (int i = 0; i < tuples.Count; i++) { if (test.IsTupleCovered(tuples[i])) { tuplesCovered++; } } return(tuplesCovered); }
private void RemoveTuplesCoveredBy(TestCase testCase) { for (int i = 0; i < uncoveredTuples.Length; i++) { for (int j = 0; j < uncoveredTuples[i].Length; j++) { TupleCollection tupleCollection = uncoveredTuples[i][j]; for (int num = tupleCollection.Count - 1; num >= 0; num--) { if (testCase.IsTupleCovered(tupleCollection[num])) { tupleCollection.RemoveAt(num); } } } } }
private void RemoveTuplesCoveredBy(TestCase testCase) { for (int d = 0; d < this.uncoveredTuples.Length; d++) { for (int f = 0; f < this.uncoveredTuples[d].Length; f++) { TupleCollection tuples = this.uncoveredTuples[d][f]; for (int i = tuples.Count - 1; i >= 0; i--) { if (testCase.IsTupleCovered(tuples[i])) { tuples.RemoveAt(i); } } } } }
public PairwiseTestCaseGenerator(int[] dimensions) { this.dimensions = dimensions; uncoveredTuples = new TupleCollection[this.dimensions.Length][]; for (int i = 0; i < uncoveredTuples.Length; i++) { uncoveredTuples[i] = new TupleCollection[this.dimensions[i]]; for (int j = 0; j < this.dimensions[i]; j++) { uncoveredTuples[i][j] = new TupleCollection(); } } currentTupleLength = new int[this.dimensions.Length][]; for (int i = 0; i < this.dimensions.Length; i++) { currentTupleLength[i] = new int[this.dimensions[i]]; } }
protected override void OnLoad(EventArgs e) { base.OnLoad(e); using (AdomdConnection conn = new AdomdConnection(connStr)) { const string mdx = "select " + "[Measures].[Votes Count] on columns, " + "[Items].[Item Category].Members on rows " + "from [Sample] " + "where [Time].[Month].[January 2009]"; using (AdomdCommand cmd = new AdomdCommand(mdx, conn)) { conn.Open(); CellSet cs = cmd.ExecuteCellSet(); DataTable dt = new DataTable(); dt.Columns.Add(" "); Axis columns = cs.Axes[0]; TupleCollection columnTuples = columns.Set.Tuples; for (int i = 0; i < columnTuples.Count; i++) { dt.Columns.Add(columnTuples[i].Members[0].Caption); } Axis rows = cs.Axes[1]; TupleCollection rowTuples = rows.Set.Tuples; int rowNum = 0; foreach (Position rowPos in rows.Positions) { DataRow dtRow = dt.NewRow(); int colNum = 0; dtRow[colNum++] = rowTuples[rowNum].Members[0].Caption; foreach (Position colPos in columns.Positions) { dtRow[colNum++] = cs.Cells[colPos.Ordinal, rowPos.Ordinal].FormattedValue; } dt.Rows.Add(dtRow); rowNum++; } this.MdxGrid.DataSource = dt; this.MdxGrid.DataBind(); } } }
private void getFilterData() { AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @" select {[Люди].[Имя сотрудника].Members} on rows, {[Проекты].[Название проекта]} on columns from [Часы работы];"; CellSet cs = cmd.ExecuteCellSet(); listEmployees = new List <string>(); TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; foreach (Microsoft.AnalysisServices.AdomdClient.Tuple row in tuplesOnRows) { listEmployees.Add(row.Members[0].Caption); } }
private Dictionary <string, object> CodigoDeAdomd1Dimension(string query) { Dictionary <string, object> resultado = new Dictionary <string, object>(); AdomdConnection con = new AdomdConnection("Data Source=DESKTOP-MF82JHU;catalog=FullOlapCube"); con.Open(); AdomdCommand cmd = new AdomdCommand(query, con); CellSet cs = cmd.ExecuteCellSet(); TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; Microsoft.AnalysisServices.AdomdClient.Tuple tp = cs.Axes[1].Set.Tuples[1]; string tituloX = tp.Members[0].ParentLevel.Caption; string tituloY = ""; foreach (Microsoft.AnalysisServices.AdomdClient.Tuple column in tuplesOnColumns) { tituloY = column.Members[0].Caption; } TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; List <string> listaNombres = new List <string>(); List <double> listaValores = new List <double>(); for (int row = 0; row < tuplesOnRows.Count; row++) { for (int members = 0; members < tuplesOnRows[row].Members.Count; members++) { listaNombres.Add(tuplesOnRows[row].Members[members].Caption); listaValores.Add(Convert.ToDouble(cs.Cells[0, row].FormattedValue)); } } con.Close(); resultado.Add("listaNombres", listaNombres); resultado.Add("listaValores", listaValores); resultado.Add("tituloX", tituloX); resultado.Add("tituloY", tituloY); return(resultado); }
public static JObject getMontlyTotalsPerYear(String partitionId, int year) { dynamic retVal = new JObject(); retVal.partitionId = partitionId; retVal.year = year; // todo: external config String serverUrl = "http://cube.office.zen.corp"; String catalogName = "ZenPlanner-CubeOne"; String userName = "******"; String password = "******"; // Michael ... nice dude! Took me a sec... StringBuilder connStr = new StringBuilder(); connStr.Append("Data Source=").Append(serverUrl).Append("/olap/msmdpump.dll;Catalog="); connStr.Append(catalogName).Append("; UID=").Append(userName).Append("; PWD=").Append(password); //Connect to the local server //String connStr = "jdbc:xmla:Server=http://cube.office.zen.corp/olap/msmdpump.dll;Catalog=ZenPlanner-CubeOne;USER=XXXX;PASSWORD=XXXXXX"; //"Data Source=http://cube.office.zen.corp/olap/msmdpump.dll;Catalog=ZenPlanner-CubeOne; UID=ZENOFFICE\\DAVID; PWD=XXXXX" using (AdomdConnection conn = new AdomdConnection(connStr.ToString())) { conn.Open(); //Create a command, using this connection AdomdCommand cmd = conn.CreateCommand(); StringBuilder s = new StringBuilder(); s.Append("SELECT {[Measures].[Fact Attendance Count],[Measures].[Fact Reservation Count] } "); s.Append(" ON COLUMNS ,"); s.Append(" NON EMPTY Hierarchize({DrilldownLevel({[Dim Date].[Month Of Year Name].[All]})})"); s.Append(" ON ROWS"); s.Append(" FROM [ZenPlanner-DataWarehouse]"); s.Append(" WHERE ([Dim Business].[Business Native ID].&[{").Append(partitionId).Append("}],[Dim Date].[Year Name].&["); s.Append(year.ToString()).Append("])"); //cmd.CommandText = @" // SELECT // {[Measures].[Fact Attendance Count],[Measures].[Fact Reservation Count]} // ON COLUMNS , // NON EMPTY Hierarchize({DrilldownLevel({[Dim Date].[Month Of Year Name].[All]})}) // ON ROWS // FROM [ZenPlanner-DataWarehouse] // WHERE ([Dim Business].[Business Native ID].&[{EE0DB82E-F1C3-4FC6-9976-8852F3F52D33}],[Dim Date].[Year Name].&[2016])"; cmd.CommandText = s.ToString(); //Execute the query, returning a cellset CellSet cs = cmd.ExecuteCellSet(); List <string> colNames = new List <string>(); //Output the column captions from the first axis //Note that this procedure assumes a single member exists per column. TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; foreach (Microsoft.AnalysisServices.AdomdClient.Tuple column in tuplesOnColumns) { string col = column.Members[0].Caption.Replace(" ", "_"); //result.Append(column.Members[0].Caption + "\t"); colNames.Add(col); } //Output the row captions from the second axis and cell data //Note that this procedure assumes a two-dimensional cellset TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; for (int row = 0; row < tuplesOnRows.Count; row++) { string rowName = tuplesOnRows[row].Members[0].Caption.Replace(" ", "_"); dynamic valueSet = new JObject(); for (int col = 0; col < tuplesOnColumns.Count; col++) { // .FormattedValue is either "", or "int" // .Value will be int or null int val = cs.Cells[col, row].FormattedValue != "" ? int.Parse(cs.Cells[col, row].FormattedValue) : 0; valueSet[colNames[col]] = val; } retVal[rowName] = valueSet; } conn.Close(); //return result.ToString(); } // using connection //dynamic tot = new JObject(); //tot.attendanceCount = 1; //tot.reservationCount = 10; //retVal.total = tot; //dynamic jan = new JObject(); //jan.attendanceCount = 1; //jan.reservationCount = 10; //retVal.jan = jan; return(retVal); } // getMontlyTotalsPerYear
private Dictionary <string, object> CodigoDeAdomd2DimensionSA(string query) { Dictionary <string, object> resultado = new Dictionary <string, object>(); AdomdConnection con = new AdomdConnection("Data Source=DESKTOP-MF82JHU;catalog=FullOlapCube"); con.Open(); AdomdCommand cmd = new AdomdCommand(query, con); CellSet cs = cmd.ExecuteCellSet(); TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; Microsoft.AnalysisServices.AdomdClient.Tuple tp = cs.Axes[1].Set.Tuples[1]; string tituloX = tp.Members[0].ParentLevel.Caption; string tituloY = ""; foreach (Microsoft.AnalysisServices.AdomdClient.Tuple column in tuplesOnColumns) { tituloY = column.Members[0].Caption; } TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; //List<string> listaNombres = new List<string>(); //List<double> listaValores = new List<double>(); int row = 0; int rowAux = 0; List <LineSeries> seriesList = new List <LineSeries>(); List <string> seriesLabel = new List <string>(); //Convert cvv = new Convert(); while (row < tuplesOnRows.Count) { string firstCategory = tuplesOnRows[rowAux].Members[0].Caption; StackedAreaSeries ls = new StackedAreaSeries(); ls.Title = firstCategory; ChartValues <DateTimePoint> cv = new ChartValues <DateTimePoint>(); while (tuplesOnRows[row].Members[0].Caption == firstCategory) { //for (int members = 0; members < tuplesOnRows[row].Members.Count; members++) //{ DateTimePoint dtp = new DateTimePoint(); dtp.DateTime = new DateTime(Convert.ToInt32(tuplesOnRows[row].Members[1].Caption), 1, 1); seriesLabel.Add(tuplesOnRows[row].Members[1].Caption); //} for (int col = 0; col < tuplesOnColumns.Count; col++) { dtp.Value = Convert.ToDouble(cs.Cells[col, row].FormattedValue); cv.Add(dtp); //listaValores.Add(Convert.ToDouble(cs.Cells[col, row].FormattedValue)); } row++; if (row == tuplesOnRows.Count) { break; } } ls.Values = cv; rowAux = row; seriesList.Add(ls); } con.Close(); resultado.Add("listaNombres", seriesLabel); resultado.Add("listaValores", seriesList); resultado.Add("tituloX", tituloX); resultado.Add("tituloY", tituloY); return(resultado); }
public IActionResult GetJsonData([FromQuery] int id) { AdomdConnection conn = new AdomdConnection(ConnString); conn.Open(); string commandText = ""; if (id == 1) { commandText = DefaultQuery; } else if (id == 2) { commandText = QuerySecond; } else if (id == 3) { commandText = QueryThird; } else if (id == 4) { commandText = QueryFourth; } else if (id == 5) { commandText = QueryFifth; } AdomdCommand adomdCommand = new AdomdCommand(commandText, conn); CellSet cs = adomdCommand.ExecuteCellSet(); //width of the cells TupleCollection tupleColumns = cs.Axes[0].Set.Tuples; //height of the cells TupleCollection tupleRows = cs.Axes[1].Set.Tuples; //Getting the main values from column tuples and the cellsets int columnIterator = 0; List <DataObj> dataObjs = new List <DataObj>(); foreach (Microsoft.AnalysisServices.AdomdClient.Tuple coltuple in tupleColumns) { DataObj obj = new DataObj(); obj.Name = coltuple.Members[0].Caption.ToString(); List <float> integerValues = new List <float>(); for (int i = 0; i < tupleRows.Count; i++) { if (!cs.Cells[columnIterator, i].FormattedValue.Equals("") && !cs.Cells[columnIterator, i].FormattedValue.Equals(null)) { integerValues.Add(float.Parse(cs.Cells[columnIterator, i].FormattedValue.ToString())); } else { integerValues.Add((float)0.00); } } obj.Data = integerValues; obj.Parameters = new List <string>(); dataObjs.Add(obj); columnIterator++; } //Getting the row tuples int TupleRowCount = tupleRows[1].Members.Count; List <DataObj> dataObjs2 = new List <DataObj>(); for (int columnNumber = 0; columnNumber < tupleRows[0].Members.Count; columnNumber++) { DataObj obj2 = new DataObj(); obj2.Name = "Parameter " + columnNumber; List <String> stringValues = new List <string>(); for (int rowNumber = 0; rowNumber < tupleRows.Count; rowNumber++) { stringValues.Add(tupleRows[rowNumber].Members[columnNumber].Caption.ToString()); } obj2.Parameters = stringValues; obj2.Data = new List <float>(); dataObjs2.Add(obj2); } conn.Close(); return(new JsonResult(new { y_values = dataObjs, x_values = dataObjs2 })); }
//</snippetGetActions> //<snippetDemonstrateDisconnectedCellset> string DemonstrateDisconnectedCellset() { //Create a new string builder to store the results System.Text.StringBuilder result = new System.Text.StringBuilder(); //Connect to the local server using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;")) { conn.Open(); //Create a command, using this connection AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @" WITH MEMBER [Measures].[FreightCostPerOrder] AS [Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity], FORMAT_STRING = 'Currency' SELECT [Geography].[Geography].[Country].&[United States].Children ON ROWS, [Date].[Calendar].[Calendar Year] ON COLUMNS FROM [Adventure Works] WHERE [Measures].[FreightCostPerOrder]"; //Execute the query, returning an XmlReader System.Xml.XmlReader x = cmd.ExecuteXmlReader(); //At this point, the XmlReader could be stored on disk, //transmitted, modified, cached, or otherwise manipulated //Load the CellSet with the specified XML CellSet cs = CellSet.LoadXml(x); //Now that the XmlReader has finished being read //we can close it and the connection, while the //CellSet can continue being used. x.Close(); conn.Close(); //Output the column captions from the first axis //Note that this procedure assumes a single member exists per column. result.Append("\t"); TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples; foreach (Tuple column in tuplesOnColumns) { result.Append(column.Members[0].Caption + "\t"); } result.AppendLine(); //Output the row captions from the second axis and cell data //Note that this procedure assumes a two-dimensional cellset TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; for (int row = 0; row < tuplesOnRows.Count; row++) { result.Append(tuplesOnRows[row].Members[0].Caption + "\t"); for (int col = 0; col < tuplesOnColumns.Count; col++) { result.Append(cs.Cells[col, row].FormattedValue + "\t"); } result.AppendLine(); } return(result.ToString()); } // using connection }
private void Page_Load(object sender, System.EventArgs e) { AdomdConnection conn = new AdomdConnection(ConfigurationSettings.AppSettings["adomdConn"]); conn.Open(); AdomdCommand comm = new AdomdCommand(@"SELECT {{[Age Range_Member].[Age Range].Members}*{[Gender_Employee].[Gender_Employee].Members}} on rows, {{[Measures].[Member Months],[Measures].[Allowed]} * {[Benefit Plan].[Benefit Plan Type].Members}} on columns from [RCBigMainCube]", conn); CellSet cst = comm.ExecuteCellSet(); CubeDef def = conn.Cubes["RCBigMainCube"]; conn.Close(); Dimension dim = def.Dimensions["Age Range_Member"]; StringBuilder str = new StringBuilder(), strTmp = new StringBuilder(); int axisRow = 1, axisCol = 0; int i, j, k; // indexers string tmpPrev, tmpCur; //temp string to hjold previous and current value HierarchyCollection rowHierarchy = cst.Axes[axisRow].Set.Hierarchies, colHierarchy = cst.Axes[axisCol].Set.Hierarchies; TupleCollection rowTuples = cst.Axes[axisRow].Set.Tuples, colTuples = cst.Axes[axisCol].Set.Tuples; int rowHierCnt = rowHierarchy.Count, colHierCnt = colHierarchy.Count, rowTuplCnt = colTuples.Count, colTuplCnt = colTuples.Count; str.Append("<table class=\"tableStyle\" cellspacing=\"0\">"); /********************************************Write the column header*************************************************/ /***************Write col dimensions*****************/ str.Append("<tr nowrap class=\"trStyle\">"); for (j = 0; j < rowHierCnt; j++) { str.Append("<td nowrap class=\"tdStyle\"> </td>"); } for (j = 0; j < colHierCnt; j++) { string dimName = colHierarchy[j].UniqueName; str.Append("<td nowrap class=\"thStyle\"><b>"); if ("Measures".Equals(dimName)) { str.Append(dimName); } else { str.Append(Regex.Match(colTuples[0].Members[j].LevelName, @"(?<=\]\.\[)[^\]]+(?=\]$)").Value); } str.Append("</b></td>"); } str.Append("</tr>"); /***************Write col dimensions*****************/ for (i = 0; i < colHierCnt; i++) { str.Append("<tr nowrap class=\"trStyle\">"); for (j = 0; j < rowHierCnt; j++) { str.Append("<td nowrap class=\"tdStyle\"> </td>"); } tmpPrev = string.Empty; for (k = 0; k < colTuplCnt; k++) { tmpCur = colTuples[k].Members[i].Caption; if (tmpPrev.Equals(tmpCur)) { tmpCur = " "; } else { tmpPrev = tmpCur; } strTmp.Append("<td nowrap class=\"thStyle\"><b>"); strTmp.Append(tmpCur); strTmp.Append("</b></td>"); } str.Append("</tr>"); } str.Append(strTmp.ToString()); /********************************************End of write the column header*************************************************/ for (i = 0; i < rowTuplCnt; i++) { str.Append("<tr nowrap class=\"trStyle\">"); tmpPrev = string.Empty; for (j = 0; j < rowHierCnt; j++) { tmpCur = rowTuples[i].Members[j].Caption; if (tmpPrev.Equals(tmpCur)) { tmpCur = " "; } else { tmpPrev = tmpCur; } str.Append("<td nowrap class=\"thStyle\"><b>"); str.Append(tmpCur); str.Append("</b></td>"); } for (k = 0; k < colTuplCnt; k++) { tmpCur = cst.Cells[k, i].FormattedValue; str.Append("<td nowrap class=\"tdStyle\">"); str.Append((tmpCur.Length == 0)?" " : tmpCur); str.Append("</td>"); } str.Append("</tr>"); } str.Append("<table/>"); Response.Write(str.ToString()); // conn.Close(); }