In this post we will see how we can export mdx query result. Here we are going to use MVC with the help of jQuery Ajax. We will run the MDX query and get the cell set and then we will convert this cell set to html table so that we can render this html table easily on client side. Once we render the html table we will do some manipulations with the data, to increase the readability. We use one jQuery Ajax Call, One Controller, One function to execute the query and get cell set, and another function to convert the cell set to html table. And at last the controller will return the html table to the Ajax success. And then we will export the data on client side. Simple right? Then let us move on. I hope you will like this article.

You can always read about Microsoft ADOMD, MDX Queries and Cell Set here: Microsoft ADOMD, MDX, CellSet.

Background

Recently I got a requirement to export the MDX query result to Excel. When a user clicks on a button I needed to export the MDX query result of the cube given. Here in this post I am sharing you the process I have done.

Using the code

Firstly, you need to create a button in your page to fire the click event.

  1. <input type="submit" class="exprotbutton" value="Export to Excel" id='excelExport'/>
Now you need to load the jQuery reference to the page.
  1. <script src="Scripts/jquery-1.11.1.min.js"></script>
The next thing is to fire the click event.
  1. $('#excelExport').bind('click', function (e)
  2. {
  3. try
  4. {}
  5. catch (e)
  6. {
  7. console.log('Excel Export Grid: ' + e.message);
  8. }
  9. });
Now we will create an Ajax call to our controller.
  1. $.ajax(
  2. {
  3. url: '../Exporting/Export/',
  4. type: 'GET',
  5. dataType: 'json',
  6. contentType: 'application/json; charset=utf-8',
  7. success: function (data) {},
  8. error: function (xhrequest, ErrorText, thrownError)
  9. {
  10. console.log('Excel Export Grid: ' + thrownError);
  11. }
  12. });
Here Exporting is the controller name and Export is the action name.

Have you noticed that we have not created the action yet. No worries we will create it now.
  1. public JsonResult Export()
  2. {
  3. StringBuilder sbConnectionString = new StringBuilder();
  4. sbConnectionString.Append("Provider=MSOLAP;data source=");
  5. sbConnectionString.Append(serverName + ";initial catalog=" + databaseName + ";Integrated Security=SSPI;Persist Security Info=False;");
  6. string readString = myModel.ExCellSet(query, sbConnectionString.ToString());
  7. return Json(readString, JsonRequestBehavior.AllowGet);
  8. }
Here serverName, databaseName and query are the global variable I set. You can pass your own here.

Now we are passing the query and connection string to our model myModel and fire call the function ExCellSet. This ExCellSet function will execute the query and return the cell set. So shall we create that model function?
  1. public string ExCellSet(string query, string adoMDConnection)
  2. {
  3. string readerString = string.Empty;
  4. CellSet cst = null;
  5. AdomdConnection conn = new AdomdConnection(adoMDConnection);
  6. try
  7. {
  8. try
  9. {
  10. conn.Open();
  11. }
  12. catch (Exception)
  13. {}
  14. using(AdomdCommand cmd = new AdomdCommand(query, conn))
  15. {
  16. cmd.CommandTimeout = connectionTimeout;
  17. cst = cmd.ExecuteCellSet();
  18. }
  19. if (cst.Axes != null)
  20. {
  21. readerString = BuildHTML(cst);
  22. return readerString;
  23. }
  24. else return null;
  25. }
  26. catch (Exception)
  27. {
  28. cst = null;
  29. throw;
  30. }
  31. finally
  32. {
  33. conn.Close(false);
  34. cst = null;
  35. }
  36. }
So our cell set is ready now we need to convert this cell set to the HTML table, right? For that I strongly recommend you to read here: Convert CellSet to HTML Table.

Anyway I am pasting the code here for your reference.
  1. try
  2. {
  3. System.Text.StringBuilder result = new System.Text.StringBuilder();
  4. //check if any axes were returned else throw error.
  5. int axes_count = cst.Axes.Count;
  6. if (axes_count == 0) throw new Exception(“No data returned
  7. for the selection”);
  8. //if axes count is not 2
  9. if (axes_count != 2) throw new Exception(“The sample code support only queries with two axes”);
  10. //if no position on either row or column throw error
  11. if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0)) throw new Exception(“No data returned
  12. for the selection”);
  13. int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
  14. cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;
  15. //Number of dimensions on the column
  16. col_dim_count = cst.Axes[0].Positions[0].Members.Count;
  17. //Number of dimensions on the row
  18. if (cst.Axes[1].Positions[0].Members.Count > 0) row_dim_count = cst.Axes[1].Positions[0].Members.Count;
  19. //Total rows and columns
  20. row_count = cst.Axes[1].Positions.Count + col_dim_count; //number of rows + rows for column headers
  21. col_count = cst.Axes[0].Positions.Count + row_dim_count; //number of columns + columns for row headers
  22. //gridPanel.ClientIDMode = System.Web.UI.ClientIDMode.AutoID;
  23. //////lets clear any controls under the grid panel
  24. //gridPanel.Controls.Clear();
  25. ////Add new server side table control to gridPanel
  26. Table tblgrid = new Table();
  27. tblgrid.Attributes.Add(“Id”, “myhtmltab”);
  28. tblgrid.Attributes.Add(“class”, “display”);
  29. //We will use label control to add text to the table cell
  30. Label lbl;
  31. string previousText = “”;
  32. int colSpan = 1;
  33. for (cur_row = 0; cur_row < row_count; cur_row++)
  34. {
  35. //add new row to table
  36. TableRow tr = new TableRow();
  37. for (cur_col = 0; cur_col < col_count; cur_col++)
  38. {
  39. //create new cell and instance of label
  40. TableCell td = new TableCell();
  41. TableHeaderCell th = new TableHeaderCell();
  42. lbl = new Label();
  43. //check if we are writing to a ROW having column header
  44. if (cur_row < col_dim_count)
  45. {
  46. //check if we are writing to a cell having row header
  47. if (cur_col < row_dim_count)
  48. {
  49. //this should be empty cell — it’s on top left of the grid.
  50. //result.Append(” ,”);
  51. lbl.Text = ”“;
  52. td.CssClass = “titleAllLockedCell”; //this locks
  53. //the cell so it doesn’t scroll upwards nor leftwards
  54. }
  55. else
  56. {
  57. //this is a column header cell — use member caption for header
  58. //result.Append(cst.Axes[0].Positions[cur_col –
  59. // row_dim_count].Members[cur_row].Caption + “,”);
  60. //if (cur_row < 1)
  61. //{
  62. lbl.Text = cst.Axes[0].Positions[cur_col– row_dim_count].Members[cur_row].Caption;
  63. th.CssClass = “titleTopLockedCell”; // this lockeders
  64. //the cell so it doesn’t scroll upwards
  65. //}
  66. if (lbl.Text == previousText)
  67. {
  68. colSpan++;
  69. }
  70. else
  71. {
  72. colSpan = 1;
  73. }
  74. }
  75. }
  76. else
  77. {
  78. //We are here.. so we are writing a row having data (not column headers)
  79. //check if we are writing to a cell having row header
  80. if (cur_col < row_dim_count)
  81. {
  82. //this is a row header cell — use member caption for header
  83. lbl.Text = cst.Axes[1].Positions[cur_row– col_dim_count].Members[cur_col].Caption.Replace(“, ”, ”“);
  84. td.CssClass = “titleLeftLockedCell”; // this lockeders
  85. //the cell so it doesn’t scroll leftwards
  86. }
  87. else
  88. {
  89. //this is data cell.. so we write the Formatted value of the cell.
  90. lbl.Text = cst[cur_col– row_dim_count, cur_row– col_dim_count].FormattedValue;
  91. //td.InnerText = cst[cur_col – row_dim_count,
  92. //cur_row – col_dim_count].FormattedValue;
  93. td.CssClass = “valueCell”; //this right
  94. //aligns the values in the column
  95. }
  96. //turn the wrapping off for row header and data cells.
  97. td.Wrap = true;
  98. }
  99. if (((lbl.Text != previousText) || (lbl.Text == ”“)) && (cur_row < col_dim_count))
  100. {
  101. tr.TableSection = TableRowSection.TableHeader;
  102. th.Text = “HEADER”;
  103. th.Controls.Add(lbl);
  104. tr.Cells.Add(th);
  105. tblgrid.Rows.Add(tr);
  106. }
  107. else if ((lbl.Text != previousText) || (lbl.Text == ”“) || (lbl.Text == null) || (lbl.Text == “”))
  108. {
  109. td.Controls.Add(lbl);
  110. tr.Cells.Add(td);
  111. tblgrid.Rows.Add(tr);
  112. }
  113. else
  114. {
  115. try
  116. {
  117. tr.Cells[tr.Cells.Count– 1].ColumnSpan = colSpan;
  118. }
  119. catch
  120. {}
  121. }
  122. if (cur_row < col_dim_count) previousText = lbl.Text;
  123. }
  124. //result.AppendLine();
  125. }
  126. using(StringWriter writer = new StringWriter())
  127. {
  128. HtmlTextWriter htw = new HtmlTextWriter(writer);
  129. tblgrid.RenderControl(htw);
  130. return htw.InnerWriter.ToString();
  131. }
  132. }
  133. catch (Exception ex)
  134. {
  135. throw ex;
  136. }
So it seems everything is set, now we have our html table, the controller will return this string to json. Do you remember we have given the type as JSON in our Ajax call?
  1. return Json(readString, JsonRequestBehavior.AllowGet);
Since our data is ready, we will rewrite our Ajax function as follows.
  1. $('#excelExport').bind('click', function (e)
  2. {
  3. try
  4. {
  5. $.ajax(
  6. {
  7. url: '../Exporting/Export/',
  8. type: 'GET',
  9. dataType: 'json',
  10. contentType: 'application/json; charset=utf-8',
  11. success: function (data)
  12. {
  13. data = '<div>' + data + '</div>';
  14. var updateHtmlDom = $.parseHTML(data);
  15. var AppliedHtml = updateHtmlDom[0].innerHTML;
  16. AppliedHtml = tableToExcel(AppliedHtml, title + ".xls");
  17. saveText($('#SubmitForm'), title + ".xls", AppliedHtml, 'text/xls;charset=utf-8');
  18. },
  19. error: function (xhrequest, ErrorText, thrownError)
  20. {
  21. console.log('Excel Export Grid: ' + thrownError);
  22. }
  23. });
  24. }
  25. catch (e)
  26. {
  27. console.log('Excel Export Grid: ' + e.message);
  28. }
  29. });
The first step we are doing is parsing the html we got using $.parseHTML in jQuery. Next we are passing the parsed data to the function tableToExcel so that the table can be formatted in the format of excel data. Here is the code for the function tableToExcel.
  1. var tableToExcel = (function (table)
  2. {
  3. var uri = 'data:application/vnd.ms-excel;base64,',
  4. template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines /></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
  5. base64 = function (s)
  6. {
  7. return window.btoa(unescape(encodeURIComponent(s)))
  8. },
  9. format = function (s, c)
  10. {
  11. return s.replace(/{(\w+)}/g, function (m, p)
  12. {
  13. returnc[p];
  14. })
  15. }
  16. return function (table, name)
  17. {
  18. var length = name.replace('.xls', '').length;
  19. if (length > 30)
  20. {
  21. name = name.slice(0, 25) + '...';
  22. }
  23. else name.replace('.xls', '');
  24. var ctx = {
  25. worksheet: name || 'Worksheet',
  26. table: table
  27. }
  28. return format(template, ctx)
  29. }
  30. })()
Now we have formatted the html data to the one which excel supports. We are passing the data to the function saveText as follows.
  1. saveText($('#SubmitForm'), title + ".xls", AppliedHtml, 'text/xls;charset=utf-8');
Here title is the file name of the excel file to be generated. The following is the definition of saveText function.
  1. function saveText(ref, fname, text, mime) {
  2. var blob = new Blob(1, { type: mime });
  3. saveAs(blob, fname);
  4. return false;
  5. }
Next we are going to export the data using blob export technology in HTML5.

If you are new to blog, you can check here using blob to understand how to use it.

Everything is set now. Build your application and check the output now. Just click on the export button, I am sure an excel file will be downloaded.

The time taken for exporting completely depends on how many data your query returns. I suggest you to export maximum of 5000 for a query, so that it won’t affect any other process.

Excel Export In MDX

Conclusion

Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

Your turn. What do you think?

If you have any questions, then please mention in the comments section.

Please see this article in my blog here.