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