public static CellsetClass GetCellSet(string ASConnStr,string MDX)
{
ConnectionClass conn=null;
try
{
conn = new ConnectionClass();
CellsetClass cst = new CellsetClass();
cst.ActiveConnection=conn;
conn.Open(ASConnStr,"","",(int)ConnectModeEnum.adModeUnknown);
cst.Open(MDX,conn);
return cst;
}
catch{return null;}
}
public static CellsetClass GetCellSet(string ASConnStr, string MDX)
{
ConnectionClass conn = null;
try
{
conn = new ConnectionClass();
CellsetClass cst = new CellsetClass();
cst.ActiveConnection = conn;
conn.Open(ASConnStr, "", "", (int)ConnectModeEnum.adModeUnknown);
cst.Open(MDX, conn);
return(cst);
}
catch { return(null); }
}
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string query = "SELECT {{[Store].[All Stores],[Store].[All Stores].[USA],[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR],[Store].[All Stores].[USA].[WA]}*{[Gender].[All Gender].[F],[Gender].[All Gender].[M]}*{[Marital Status].[All Marital Status].[M],[Marital Status].[All Marital Status].[S]}*{[Product].[All Products].[Drink],[Product].[All Products].[Food],[Product].[All Products].[Non-Consumable]}} ON ROWS, " +
"{{[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store Sales],[Measures].[Sales Count],[Measures].[Store Sales Net],[Measures].[Profit],[Measures].[Sales Average]}*{[Education Level].[All Education Level].[Bachelors Degree],[Education Level].[All Education Level].[Graduate Degree],[Education Level].[All Education Level].[High School Degree],[Education Level].[All Education Level].[Partial College],[Education Level].[All Education Level].[Partial High School]}*{[Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}} ON COLUMNS " +
"FROM [Sales]";
ConnectionClass conn = new ConnectionClass();
conn.Open("Provider=MSOLAP; Datasource=localhost; Initial Catalog=FoodMart 2000;","",string.Empty,(int)ADODB.ConnectOptionEnum.adConnectUnspecified);
CellsetClass cs = new CellsetClass();
cs.Open(query,conn);
Response.Write("<table border=1>");
for(int i=0;i<cs.Axes[0].DimensionCount;i++)
{
Response.Write("<tr>");
for(int j=0;j<cs.Axes[1].DimensionCount;j++) Response.Write("<th> </th>");
//string lastCaption= string.Empty;//cs.Axes[0].Positions[0].Members[i].Caption;
int colspan=1;
for(int j=0;j<cs.Axes[0].Positions.Count;j++ )
{
Position pos = cs.Axes[0].Positions[j];
// if(lastCaption.Equals(pos.Members[i].Caption))
// {
// Response.Write("<th> </th>");
// //colspan++;
// }
// else
// {
//Response.Write("<th colspan="+colspan+">" +cs.Axes[0].Positions[j-1].Members[i].Caption + "</th>");
//colspan=1;
Response.Write("<th colspan="+colspan+">" +cs.Axes[0].Positions[j].Members[i].Caption + "</th>");
// lastCaption=pos.Members[i].Caption;
// }
//if(j==cs.Axes[0].Positions.Count-1) Response.Write("<th colspan="+colspan+">" +cs.Axes[0].Positions[j].Members[i].Caption + "</th>");
}
Response.Write("</tr>");
}
// string[] lastCaptions = new string[cs.Axes[1].DimensionCount];
for(int j=0;j<cs.Axes[1].Positions.Count;j++)
{
Position pos = cs.Axes[1].Positions[j];
Response.Write("<tr>");
for(int i=0;i<cs.Axes[1].DimensionCount;i++)
{
// if(pos.Members[i].Caption.Equals(lastCaptions[i]))
// {
// Response.Write("<th> </th>");
// continue;
// }
Response.Write("<th>" +pos.Members[i].Caption + "</th>" );
// lastCaptions[i]=pos.Members[i].Caption ;
}
for(int k=0;k<cs.Axes[0].Positions.Count;k++)
{
object[] coords=new object[]{k,j};
Cell cell = cs.get_Item(ref coords);
Response.Write("<td>"+cell.FormattedValue + "</td>");
}
Response.Write("</tr>");
}
Response.Write("</table>");
}
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string query = "SELECT {{[Store].[All Stores],[Store].[All Stores].[USA],[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR],[Store].[All Stores].[USA].[WA]}*{[Gender].[All Gender].[F],[Gender].[All Gender].[M]}*{[Marital Status].[All Marital Status].[M],[Marital Status].[All Marital Status].[S]}*{[Product].[All Products].[Drink],[Product].[All Products].[Food],[Product].[All Products].[Non-Consumable]}} ON ROWS, " +
"{{[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store Sales],[Measures].[Sales Count],[Measures].[Store Sales Net],[Measures].[Profit],[Measures].[Sales Average]}*{[Education Level].[All Education Level].[Bachelors Degree],[Education Level].[All Education Level].[Graduate Degree],[Education Level].[All Education Level].[High School Degree],[Education Level].[All Education Level].[Partial College],[Education Level].[All Education Level].[Partial High School]}*{[Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}} ON COLUMNS " +
"FROM [Sales]";
ConnectionClass conn = new ConnectionClass();
conn.Open("Provider=MSOLAP; Datasource=localhost; Initial Catalog=FoodMart 2000;", "", string.Empty, (int)ADODB.ConnectOptionEnum.adConnectUnspecified);
CellsetClass cs = new CellsetClass();
cs.Open(query, conn);
Response.Write("<table border=1>");
for (int i = 0; i < cs.Axes[0].DimensionCount; i++)
{
Response.Write("<tr>");
for (int j = 0; j < cs.Axes[1].DimensionCount; j++)
{
Response.Write("<th> </th>");
}
//string lastCaption= string.Empty;//cs.Axes[0].Positions[0].Members[i].Caption;
int colspan = 1;
for (int j = 0; j < cs.Axes[0].Positions.Count; j++)
{
Position pos = cs.Axes[0].Positions[j];
// if(lastCaption.Equals(pos.Members[i].Caption))
// {
// Response.Write("<th> </th>");
// //colspan++;
// }
// else
// {
//Response.Write("<th colspan="+colspan+">" +cs.Axes[0].Positions[j-1].Members[i].Caption + "</th>");
//colspan=1;
Response.Write("<th colspan=" + colspan + ">" + cs.Axes[0].Positions[j].Members[i].Caption + "</th>");
// lastCaption=pos.Members[i].Caption;
// }
//if(j==cs.Axes[0].Positions.Count-1) Response.Write("<th colspan="+colspan+">" +cs.Axes[0].Positions[j].Members[i].Caption + "</th>");
}
Response.Write("</tr>");
}
// string[] lastCaptions = new string[cs.Axes[1].DimensionCount];
for (int j = 0; j < cs.Axes[1].Positions.Count; j++)
{
Position pos = cs.Axes[1].Positions[j];
Response.Write("<tr>");
for (int i = 0; i < cs.Axes[1].DimensionCount; i++)
{
// if(pos.Members[i].Caption.Equals(lastCaptions[i]))
// {
// Response.Write("<th> </th>");
// continue;
// }
Response.Write("<th>" + pos.Members[i].Caption + "</th>");
// lastCaptions[i]=pos.Members[i].Caption ;
}
for (int k = 0; k < cs.Axes[0].Positions.Count; k++)
{
object[] coords = new object[] { k, j };
Cell cell = cs.get_Item(ref coords);
Response.Write("<td>" + cell.FormattedValue + "</td>");
}
Response.Write("</tr>");
}
Response.Write("</table>");
}
private void Page_Load(object sender, System.EventArgs e)
{
string query = txtQuery.Text;
strView = new StringBuilder();
if (String.Empty.Equals(query))
{
// query=txtQuery.Text="SELECT {{[Store].[All Stores],[Store].[All Stores].[USA],[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR],[Store].[All Stores].[USA].[WA]}*{[Gender].[All Gender].[F],[Gender].[All Gender].[M]}*{[Marital Status].[All Marital Status].[M],[Marital Status].[All Marital Status].[S]}*{[Product].[All Products].[Drink],[Product].[All Products].[Food],[Product].[All Products].[Non-Consumable]}} ON ROWS, " +
// "{{[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store Sales],[Measures].[Sales Count],[Measures].[Store Sales Net],[Measures].[Profit],[Measures].[Sales Average]}*{[Education Level].[All Education Level].[Bachelors Degree],[Education Level].[All Education Level].[Graduate Degree],[Education Level].[All Education Level].[High School Degree],[Education Level].[All Education Level].[Partial College],[Education Level].[All Education Level].[Partial High School]}*{[Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}} ON COLUMNS " +
// "FROM [Sales]";
return;
}
ConnectionClass conn = new ConnectionClass();
conn.Open("Provider=MSOLAP; Datasource=" + txtServer.Text + "; Initial Catalog=" + txtCatalog.Text + ";", "", string.Empty, (int)ADODB.ConnectOptionEnum.adConnectUnspecified);
CellsetClass cs = new CellsetClass();
cs.Open(query, conn);
strView.Append("<table border=1>");
Axis axisX = cs.Axes[0];
int dimCountX = axisX.DimensionCount;
int posCountX = axisX.Positions.Count;
Positions posX = axisX.Positions;
Axis axisY = cs.Axes[1];
int dimCountY = axisY.DimensionCount;
int posCountY = axisY.Positions.Count;
Positions posY = axisY.Positions;
for (int i = 0; i < dimCountX; i++)
{
strView.Append("<tr>");
strView.Append("<th colspan=" + dimCountY + "> </th>");
string lastCaption = posX[0].Members[i].Caption;
int colspan = 1;
for (int j = 1; j < posCountX; j++)
{
Position pos = posX[j];
if (lastCaption.Equals(pos.Members[i].Caption))
{
colspan++;
}
else
{
strView.Append("<th colspan=" + colspan + ">" + posX[j - 1].Members[i].Caption + "</th>");
colspan = 1;
lastCaption = pos.Members[i].Caption;
}
if (j == posCountX - 1)
{
strView.Append("<th colspan=" + colspan + ">" + pos.Members[i].Caption + "</th>");
}
}
strView.Append("</tr>");
}
string[] lastCaptions = new string[dimCountY];
for (int j = 0; j < posCountY; j++)
{
Position pos = posY[j];
strView.Append("<tr>");
for (int i = 0; i < dimCountY; i++)
{
if (pos.Members[i].Caption.Equals(lastCaptions[i]))
{
strView.Append("<th> </th>");
continue;
}
strView.Append("<th >" + pos.Members[i].Caption + "</th>");
lastCaptions[i] = pos.Members[i].Caption;
}
for (int k = 0; k < posCountX; k++)
{
object[] coords = new object[] { k, j };
Cell cell = cs.get_Item(ref coords);
strView.Append("<td>" + cell.FormattedValue + "</td>");
}
strView.Append("</tr>");
}
strView.Append("</table>");
}