In the previous article, I provided the simple example to start working on ADOMD libraries. The small application was implemented to execute the MDX file. The MDX query returns data from Jan to September in 2014 and the same period in 2013 (Year-To-Date report)

Now, we need a flexible method that allows us to choose which month we would like to return data to CYTD report.

Implementation

I continue using the example provided in previous article and adding a new combo box that contains a list of months. When we change any month in the combo box, we pass the value to our MDX query.

Combo Box

Name: cbbYearMonth

Modify Form_Load event

When the main form loads, it need to bind data into cbbYearMonth

private void fCYTDProductSubCategory_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable(“YearMonth”);
dt.Columns.Add(“YearMonthID”);
dt.Columns.Add(“YearMonthName”);
dt.Rows.Add(“December 2014”, “December 2014”);
dt.Rows.Add(“November 2014”, “November 2014”);
dt.Rows.Add(“October 2014”, “October 2014”);
dt.Rows.Add(“September 2014”, “September 2014”);
dt.Rows.Add(“August 2014”, “August 2014”);
dt.Rows.Add(“July 2014”, “July 2014”);
dt.Rows.Add(“June 2014”, “June 2014”);
dt.Rows.Add(“May 2014”, “May 2014”);
dt.Rows.Add(“Apirl 2014”, “Apirl 2014”);
dt.Rows.Add(“March 2014”, “March 2014”);
dt.Rows.Add(“Febuary 2014”, “Febuary 2014”);
dt.Rows.Add(“January 2014”, “January 2014”);
dt.Rows.Add(“December 2013”, “December 2013”);
dt.Rows.Add(“November 2013”, “November 2013”);
dt.Rows.Add(“October 2013”, “October 2013”);
dt.Rows.Add(“September 2013”, “September 2013”);

cbbYearMonth.DisplayMember = “YearMonthName”;
cbbYearMonth.ValueMember = “YearMonthID”;
cbbYearMonth.DataSource = dt;

}

Modify SelectedIndexChanged event on combo box

This step is to get value of Year Month which we will use to pass to MDX query.

private void cbbYearMonth_SelectedIndexChanged(object sender, EventArgs e)
{

sYearMonth = cbbYearMonth.SelectedValue.ToString();
}

Modify Click event on Load Data button

This step is to pass YearMonth parameter to MDX query which we read from CYTDTotalAmountProductSubCategory.mdx file

private DataTable GetCYTDTotalAmountProductSubCategory()
{
string pYearMonth = “[Date].[Calendar].[Month].[” + sYearMonth + “]”;
string ConnectionOLAPCube = “Provider=MSOLAP;Data Source=DUNGDT\\SQLSERVER2014; Initial Catalog=AdventureWorksDW2014Multidimensional;”;
ConnectionOLAPCube.Replace(“\\\\”, “\\”);
var dt = new DataTable();
string query = System.IO.File.ReadAllText(“D:\\Projects\\Analytics\\Analytics.DAL\\MDX\\CYTDTotalAmountProductSubCategory.mdx”);
AdomdConnection conn = new AdomdConnection(ConnectionOLAPCube);
conn.Open();
var cmd = new AdomdCommand(query, conn);

cmd.Parameters.Add(“YearMonth”, pYearMonth);

var objDataAdapter = new AdomdDataAdapter(cmd);
objDataAdapter.Fill(dt);
conn.Close();
return dt;

}

We declare a new variable pYearMonth, we need to format as MDX but value of member depends on which we select on the combo box.

Modify MDX script

This step is to modify MDX script which receive the parameter from our application

WITH MEMBER Measures.[CYTD Current]ASSUM(YTD([Date].[Calendar].CURRENTMEMBER)   ,[Measures].[Internet Sales Amount]),FORMAT_STRING =”Currency”MEMBER Measures.[CYTD Last Year]ASSUM(   YTD(      ParallelPeriod([Date].[Calendar].[Calendar Year]                 ,1 ,[Date].[Calendar].CURRENTMEMBER )   )   ,[Measures].[Internet Sales Amount]),FORMAT_STRING =”Currency”SELECT NON EMPTY({                  Measures.[CYTD Current]  ,Measures.[CYTD Last Year]  }                  ) ON COLUMNS,NON EMPTY([Product].[SubCategory].CHILDREN) ON ROWSFROM [Adventure Works]WHERE StrToSet(@YearMonth)

Testing

Case 1: Year Month is September 2014

Case 2: Year Month is September 2013

Conclusion

ADOMD libraries provide a set of methods that we can work on Microsoft OLAP cube (multidimensional database). Hopefully, you can take first basic steps and use them successfully and appropriately.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s