Heres an example of one of my C# classes where I read the results into a DataTable and return the DataTable.

This is an example of getting Age Groups vs Gender (How many consumers Age 24-35 that are male?)

I have it returning a fake dataset for testing because my connection to sql server 2000 is very slow.

 

 

public class MdxToDTConsumer

{

//String db = "usush2kdm01";

String consumerConnStr;

AdomdConnection consumerConn;

 

public MdxToDTConsumer()

{

if (ConfigurationManager.AppSettings["fakedata"].Equals("1"))

return;

 

string consumer_cube = ConfigurationManager.AppSettings["consumer_cube"].ToString();

consumerConnStr = "provider=msolap.2; Data Source=" + db + "; Initial Catalog = \"" + consumer_cube + "\"";

////////////////

// Make Connection here

///////////////

 

consumerConn = new AdomdConnection(consumerConnStr);

consumerConn.Open();

}

public void CloseConn()

{

if (consumerConn != null && consumerConn.State == ConnectionState.Open)

consumerConn.Close();

 

 

}

 

 

public DataTable GetAgeGenderDT(string zone_id, string partner_id, string ct_id, string p1, string lang)

{

DataTable AgeGenderTable = new DataTable();

AgeGenderTable.Columns.Add("label", typeof(string));

AgeGenderTable.Columns.Add("label_key", typeof(int));

AgeGenderTable.Columns.Add("all_gender", typeof(int));

AgeGenderTable.Columns.Add("male", typeof(int));

AgeGenderTable.Columns.Add("male_pct_val", typeof(double));

AgeGenderTable.Columns.Add("male_pct", typeof(string));

AgeGenderTable.Columns.Add("female", typeof(int));

AgeGenderTable.Columns.Add("female_pct_val", typeof(double));

AgeGenderTable.Columns.Add("female_pct", typeof(string));

AgeGenderTable.Columns.Add("unknown", typeof(int));

AgeGenderTable.Columns.Add("unknown_pct_val", typeof(double));

AgeGenderTable.Columns.Add("unknown_pct", typeof(string));

 

 

 

if (ConfigurationManager.AppSettings["fakedata"].Equals("1"))

{

for (int i = 0; i < 20; i++)

{

DataRow dataRow = AgeGenderTable.NewRow();

dataRow["label"] = "Item num" + i.ToString();

dataRow["label_key"] = i;

dataRow["male"] = i * 100;

dataRow["male_pct_val"] = 0.30;

dataRow["male_pct"] = "30%";

AgeGenderTable.Rows.Add(dataRow);

}

 

 

return AgeGenderTable;

}

 

System.Globalization.CultureInfo myCI = new CultureInfo("en-US");

 

DateTime dt_p1 = DateTime.Parse(p1, myCI);

string mdxStr =

 

"WITH " +

"MEMBER [Measures].[SumOfAG] AS " +

" '( Sum({" + EcrmUtils.getCubeDate(dt_p1) + "}, [Measures].[RunningTotalSubs]))' " +

 

" MEMBER [Measures].[SuperSum] AS "+

" ' (Sum({[Age Group].[13 and 17],[Age Group].[18 and 24],[Age Group].[25 and 34],[Age Group].[35 and 44],[Age Group].[45 and 54],[Age Group].[over 55],[Age Group].[less then 13],[Age Group].[UNKNOWN] },[Measures].[SumOfAG] ))' " +

 

" MEMBER [Measures].[GMale] AS " +

" '( Sum({(" + EcrmUtils.getCubeDate(dt_p1) + ",[Gender].[Male])}, [Measures].[RunningTotalSubs]))' " +

 

" MEMBER [Measures].[GMalePer] AS " +

" ' ([Measures].[GMale] / [Measures].[SuperSum])' " +

 

" MEMBER [Measures].[GFemale] AS " +

" '( Sum({(" + EcrmUtils.getCubeDate(dt_p1) + ",[Gender].[Female])}, [Measures].[RunningTotalSubs]))' " +

 

" MEMBER [Measures].[GFemalePer] AS " +

" ' ([Measures].[GFemale] / [Measures].[SuperSum])' " +

 

" MEMBER [Measures].[GUnknown] AS " +

" '( Sum({(" + EcrmUtils.getCubeDate(dt_p1) + ",[Gender].[Unknown])}, [Measures].[RunningTotalSubs]))' " +

 

" MEMBER [Measures].[GUnknownPer] AS " +

" ' ([Measures].[GUnknown] / [Measures].[SuperSum])' " +

 

" select {[Measures].[SumOfAG], [Measures].[GMale], [Measures].[GMalePer], [Measures].[GFemale], [Measures].[GFemalePer], [Measures].[GUnknown], [Measures].[GUnknownPer] } " +

 

" on Columns, { ([Age Group].children) } on Rows from [consumer_analysis] where ([Zone Id]." + EcrmUtils.getZoneDimension(zone_id, partner_id, ct_id, "0") + ") ";

 

 

 

try

{

 

 

AdomdCommand myCommand = new AdomdCommand();

myCommand.Connection = consumerConn;

myCommand.CommandText = mdxStr;

 

AdomdDataReader reader = myCommand.ExecuteReader();

while (reader.Read())

{

DataRow dataRow = AgeGenderTable.NewRow();

// for each item in the resultset, print the value

for (int i = 0; i < reader.FieldCount; i++)

{

 

string rName = reader.GetName(i).ToString();

if (rName.Contains("[Age Group].[Age Group].[MEMBER_CAPTION]"))

{

string data_label = reader.GetValue(i).ToString();

string dl_lower = data_label.ToLower();

int i_label_key = 100;

 

 

if (dl_lower.Equals("less then 13"))

{

if (lang.Contains("fr"))

data_label = "moins de 13 ans";

else

data_label = "under 13";

 

i_label_key = 1;

}

else if (dl_lower.Equals("13 and 17"))

{

if (lang.Contains("fr"))

data_label = "de 13 17 ans";

else

data_label = "13 to 17";

 

i_label_key = 2;

 

}

else if (dl_lower.Equals("18 and 24"))

{

if (lang.Contains("fr"))

data_label = "de 18 24 ans";

else

data_label = "18 to 24";

 

i_label_key = 3;

 

}

else if (dl_lower.Equals("25 and 34"))

{

if (lang.Contains("fr"))

data_label = "de 25 34 ans";

else

data_label = "25 to 34";

 

i_label_key = 4;

 

}

else if (dl_lower.Equals("35 and 44"))

{

if (lang.Contains("fr"))

data_label = "de 35 44 ans";

else

data_label = "35 to 44";

 

i_label_key = 5;

 

}

else if (dl_lower.Equals("45 and 54"))

{

if (lang.Contains("fr"))

data_label = "de 45 54 ans";

else

data_label = "45 to 54";

 

i_label_key = 6;

 

}

else if (dl_lower.Equals("over 55"))

{

if (lang.Contains("fr"))

data_label = "plus de 55 ans";

 

i_label_key = 7;

 

}

else if (dl_lower.Contains("unknown"))

{

data_label = "Unknown";

i_label_key = 8;

}

 

 

dataRow["label"] = data_label.ToString();

dataRow["label_key"] = i_label_key;

 

 

}

else if (rName.Contains("[Measures].[SumOfAG]"))

{

 

// double d_all_subs = Convert.ToDouble(reader.GetValue(i));

dataRow["all_gender"] = Convert.ToInt32(reader.GetValue(i));

 

}

 

else if (rName.Contains("[Measures].[GFemale]"))

{

dataRow["female"] = Convert.ToInt32(reader.GetValue(i));

}

 

else if (rName.Contains("[Measures].[GFemalePer]"))

{

double d_pct = Convert.ToDouble(reader.GetValue(i));

dataRow["female_pct"] = String.Format("{0:0.00%}", d_pct).Replace("NaN", "-").Replace("Infinity", "-");

dataRow["female_pct_val"] = d_pct;

}

 

else if (rName.Contains("[Measures].[GMale]"))

{

dataRow["male"] = Convert.ToInt32(reader.GetValue(i));

}

 

else if (rName.Contains("[Measures].[GMalePer]"))

{

double d_pct = Convert.ToDouble(reader.GetValue(i));

dataRow["male_pct"] = String.Format("{0:0.00%}", d_pct).Replace("NaN", "-").Replace("Infinity", "-");

dataRow["male_pct_val"] = d_pct;

}

 

else if (rName.Contains("[Measures].[GUnknown]"))

{

dataRow["unknown"] = Convert.ToInt32(reader.GetValue(i));

}

 

else if (rName.Contains("[Measures].[GUnknownPer]"))

{

double d_pct = Convert.ToDouble(reader.GetValue(i));

dataRow["unknown_pct"] = String.Format("{0:0.00%}", d_pct).Replace("NaN", "-").Replace("Infinity", "-");

dataRow["unknown_pct_val"] = d_pct;

}

}

AgeGenderTable.Rows.Add(dataRow);

 

}

reader.Close();

return (AgeGenderTable);

}

catch (Exception e1)

{

EcrmUtils.WriteErrorMsg(e1.ToString() + mdxStr);

return null;

 

}

}

}