private DataTable ParseMdDataSet(IEssMdDataSet ds) { DataTable dt = new DataTable(); IEssMdAxis[] axis = ds.getAllAxes(); IEssMdMember[] mbrs = null; rowHeaderCols = 0; colHeaderRows = 0; int rowNum = 0; int colNum = 0; // Initialize the table shape (number of row/col headers) for (int i = 0; i < axis[1].getTupleCount(); i++) { dt.Columns.Add(); // Add a column for each column in results } for (int x = 2; x < axis.GetLength(0); x++) { rowHeaderCols += axis[x].getAllTupleMembers(0).Count(); foreach (IEssMdMember tmp in axis[x].getAllTupleMembers(0)) { dt.Columns.Add(); // Add a column for each row header or tuple member in results } } for (int j = 0; j < axis[1].getDimensionCount(); j++) { colHeaderRows++; DataRow dr = dt.NewRow(); dt.Rows.Add(dr); // Add a row for each dimension in the header } // Populate column headers for (colNum = 0; colNum < axis[1].getTupleCount(); colNum++) { mbrs = axis[1].getAllTupleMembers(colNum); for (rowNum = 0; rowNum < mbrs.GetLength(0); rowNum++) { dt.Rows[rowNum][colNum + rowHeaderCols] = mbrs[rowNum].getName(); } } // Populate the rows int cellIndex = 0; int numDataCols = dt.Columns.Count - rowHeaderCols; DataRow newRow = null; // For each cell in the DataSet (left to right, top to bottom) while (cellIndex < ds.getCellCount()) { // If it's the first cell of the row, create a new row and build the row header cells if (cellIndex % numDataCols == 0) { newRow = dt.NewRow(); int rowHeaderCount = 0; for (int x = 2; x < axis.GetLength(0); x++) { foreach (IEssMdMember tmp in axis[x].getAllTupleMembers(cellIndex / numDataCols)) { newRow[rowHeaderCount] = tmp.getName(); rowHeaderCount++; } } } if (ds.isMissingCell(cellIndex)) { //TODO: Add a preference for #Missing/#NoAccess label newRow[cellIndex % numDataCols + rowHeaderCols] = "#Missing"; } else if (ds.isNoAccessCell(cellIndex)) { //TODO: Add a preference for #Missing/#NoAccess label newRow[cellIndex % numDataCols + rowHeaderCols] = "#NoAccess"; } else { //TODO: Add a preference for number format //TODO: Add a preference for number scale newRow[cellIndex % numDataCols + rowHeaderCols] = ds.getCellValue(cellIndex); } cellIndex++; if (cellIndex % numDataCols == 0) { dt.Rows.Add(newRow); } } return(dt); }
private void btnExecute_Click(object sender, EventArgs e) { try { //Update vars with input text m_qry = scintQry.Text; //Create the query object qry = cv.createIEssOpMdxQuery(); //TODO: Any other query properties that should be added here? if (Properties.Settings.Default.NameAlias == "NAME") { qry.setQuery(false, m_qry, false, IEssOpMdxQuery.EEssMemberIdentifierType.NAME); } else { qry.setQuery(false, m_qry, false, IEssOpMdxQuery.EEssMemberIdentifierType.ALIAS); } //Execute the query System.Diagnostics.Stopwatch qryTime = new System.Diagnostics.Stopwatch(); qryTime.Start(); cv.performOperation(qry); qryTime.Stop(); //Update the status bar with run stats String elapsed = null; TimeSpan ts = qryTime.Elapsed; if (ts.Minutes > 0) { elapsed = String.Format("Executed in: {0}:{1}.{2} minutes", ts.Minutes, ts.Seconds, ts.Milliseconds); } else { elapsed = String.Format("Executed in: {0}.{1} seconds", ts.Seconds, ts.Milliseconds); } tsslExecTime.Text = elapsed; this.execState = ExecutionState.QueryHasBeenExecuted; //Update the query history DataRow dr = this.dtQueryHistory.NewRow(); dr["QueryText"] = m_qry; dr["ElapsedTime"] = elapsed; dr["ExecutedBy"] = this.essUser; dr["Timestamp"] = DateTime.Now; this.dtQueryHistory.Rows.Add(dr); //Retrieve and parse the multi-dimensional data set IEssMdDataSet ds = cv.getMdDataSet(); DataTable dt = ParseMdDataSet(ds); //Bind the parsed data (DataTable obj) to the grid dgvResults.DataSource = dt; //Set the grid style DataGridViewCellStyle style = new DataGridViewCellStyle(); style.BackColor = Color.Black; style.ForeColor = Color.White; style.Font = new Font("Microsoft Sans Serif", 9, FontStyle.Bold); for (int i = 0; i < colHeaderRows; i++) { dgvResults.Rows[i].DefaultCellStyle = style; } for (int i = 0; i < rowHeaderCols; i++) { dgvResults.Columns[i].DefaultCellStyle = style; } dgvResults.AutoResizeColumns(); } catch (Exception ex) { MessageBox.Show(String.Format("Error executing query: {0}", ex.Message), "Query Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }