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