Getting Attribute and Allias value in a dimension
Posted: Sat Feb 26, 2011 1:05 am
Currently i am only able to get the store number which is the element that i have selected. But i would like to have more information about the store which is stored as the attribute within customer dimension.
For example I would like to have my table to look as such.
State StoreNo StoreName Sales Sales Unit
Vic 8088 xxx $55 10
Nsw 4512 yyy $34 3
the code provided is in c# and i am using the TM1 api to obtain the data. help will be appreciated. thanks.
For example I would like to have my table to look as such.
State StoreNo StoreName Sales Sales Unit
Vic 8088 xxx $55 10
Nsw 4512 yyy $34 3
the code provided is in c# and i am using the TM1 api to obtain the data. help will be appreciated. thanks.
Code: Select all
table = new DataTable();
TM1AdminServer admin = TM1ConnectionAgent.TM1ConnectionOpen();
TM1Server myServer = TM1ConnectionAgent.TM1ServerOpen(admin);
string mdxExpression = string.Empty;
DataColumn[] keys = new DataColumn[1];
DataColumn column = new DataColumn();
column.ColumnName = "State";
table.Columns.Add(column);
keys[0] = column;
mdxExpression = "SELECT{[Data Fields].[Sales], [Data Fields].[Sales Unit]} ON COLUMNS, {ORDER({[Customers].Children}, ([Data Fields].[Sales]) , DESC)} ON ROWS FROM [Sales Cube] WHERE ([Year].[" + Year + "],[Version].[Final],[Products].[All Products],[Period].[" + Period + "]) ";
TM1MdxView MyTm1DataView = new TM1MdxView(myServer, mdxExpression);
TM1MdxAxis MyMdxColAxis = MyTm1DataView.GetAxis(0);
int MyMdxColTupleCount = MyMdxColAxis.TupleCount;
for (int i = 0; i < MyMdxColTupleCount; i++)
{
string columname = MyMdxColAxis.GetAxisValue(i, 0, 1).ToString();
table.Columns.Add(columname, typeof(decimal));
}
TM1MdxAxis MyMdxRowAxis = MyTm1DataView.GetAxis(1);
int MyMdxRowTupleCount = MyMdxRowAxis.TupleCount;
for (int y = 0; y < MyMdxRowTupleCount; y++)
{
DataRow rowNew = table.NewRow();
rowNew[0] = MyMdxRowAxis.GetAxisValue(y, 0, 1);
object cellValue;
for (int i = 0; i < MyMdxColTupleCount; i++)
{
cellValue = MyTm1DataView.GetCellValue(y, i);
rowNew[i + 1] = cellValue;
}
table.Rows.Add(rowNew);
}
MyMdxColAxis.Dispose();
MyMdxRowAxis.Dispose();
admin.LogoutAll();
MyTm1DataView.Dispose();
admin.Dispose();