Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version

Microsoft Analysis Services 2005: Displaying a grid using ADOMD.NET and MDX

, 19 Aug 2008 CPOL
The article explains how to fetch data from Microsoft Analysis Services 2005 (OLAP) Cube using ADOMD.NET and MDX, and display it in the form of a grid.
ADOMDMDXSampleByHK.zip
ADOMDMDXSampleByHK
App_Data
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//Following is included to use ADOMD library
using Microsoft.AnalysisServices.AdomdClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //setting default connection string value
            txtConnStr.Text = "Provider=MSOLAP.3; Data Source=localhost;Initial Catalog=Adventure Works DW Standard Edition;Integrated Security=SSPI;";
            txtMDX.Text = "	select \n [Customer].[Customer Geography].[State-Province].Members on columns,\n" +
                        "Descendants([Date].[Calendar].[Calendar Year].&[2003],[Date].[Calendar].[Month])*" +
                        "{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]} on rows\n"+
                        "from [adventure works]";
        }
        
        //clearing any error message
        lblErr.Text = "";

    }

    /// <summary>
    /// btnGo click event handler
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnGo_Click(object sender, EventArgs e)
    {
        //using try....catch block. If any error occurs we will display it using lblErr
        try
        {
            CellSet cst = GetCellset();
            BuildGrid(cst);
        }
        catch (System.Exception ex)
        {
            lblErr.Text = ex.Message;
        }

    }

    /// <summary>
    /// This method reads MDX query from the text box and returns Cellset.
    /// </summary>
    /// <returns></returns>
    private CellSet GetCellset()
    {
        //Lets store the connection string and MDX query to local variables
        string strConn = txtConnStr.Text;
        string strMDX = txtMDX.Text;

        //create and open adomd connection with connection string
        AdomdConnection conn = new AdomdConnection(strConn);
        conn.Open();

        //create adomd command using connection and MDX query
        AdomdCommand cmd = new AdomdCommand(strMDX, conn);

        //The ExecuteCellSet method of adomd command will 
        //execute the MDX query and return CellSet object
        CellSet cst = cmd.ExecuteCellSet();

        //close connection
        conn.Close();

        //return cellset
        return cst;
    }

    private void BuildGrid(CellSet cst)
    {
        //check if any axes were returned else throw error.
        int axes_count = cst.Axes.Count;
        if (axes_count == 0)
            throw new Exception("No data returned for the selection");

        //if axes count is not 2
        if (axes_count != 2)
            throw new Exception("The sample code support only queries with two axes");

        //if no position on either row or column throw error
        if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
            throw new Exception("No data returned for the selection");

        int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
        cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;


        //Number of dimensions on the column
        col_dim_count = cst.Axes[0].Positions[0].Members.Count;

        //Number of dimensions on the row
        if (cst.Axes[1].Positions[0].Members.Count > 0)
            row_dim_count = cst.Axes[1].Positions[0].Members.Count;

        //Total rows and columns
        row_count = cst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
        col_count = cst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

        //lets clear any controls under the grid panel
        gridPanel.Controls.Clear();

        //Add new server side table control to gridPanel
        Table tblGrid = new Table();
        tblGrid.CellSpacing = 0;
        tblGrid.Width = col_count * 100;
        gridPanel.Controls.Add(tblGrid);

        //We will use label control to add text to the table cell
        Label lbl;

        for (cur_row = 0; cur_row < row_count; cur_row++)
        {
            //add new row to table
            TableRow tr = new TableRow();
            tblGrid.Rows.Add(tr);

            for (cur_col = 0; cur_col < col_count; cur_col++)
            {
                //create new cell and instance of label
                TableCell td = new TableCell();
                lbl = new Label();

                //check if we are writing to a ROW having column header
                if (cur_row < col_dim_count)
                {
                    //check if we are writing to a cell having row header
                    if (cur_col < row_dim_count)
                    {
                        //this should be empty cell -- it's on top left of the grid.
                        lbl.Text = "&nbsp;";
                        td.CssClass = "titleAllLockedCell"; //this locks the cell so it doesn't scroll upwards nor leftwards
                    }
                    else
                    {
                        //this is a column header cell -- use member caption for header
                        lbl.Text = cst.Axes[0].Positions[cur_col - row_dim_count].Members[cur_row].Caption;
                        td.CssClass = "titleTopLockedCell"; // this lockeders the cell so it doesn't scroll upwards
                    }
                }
                else
                {
                //We are here.. so we are writing a row having data (not column headers)
                   
                    //check if we are writing to a cell having row header
                    if (cur_col < row_dim_count)
                    {
                        //this is a row header cell -- use member caption for header
                        lbl.Text = cst.Axes[1].Positions[cur_row - col_dim_count].Members[cur_col].Caption;
                        td.CssClass = "titleLeftLockedCell"; // this lockeders the cell so it doesn't scroll leftwards
                    }
                    else
                    {
                        //this is data cell.. so we write the Formatted value of the cell.
                        lbl.Text = cst[cur_col - row_dim_count, cur_row - col_dim_count].FormattedValue + "&nbsp;";
                        td.CssClass = "valueCell"; //this  right aligns the values in the column
                    }

                    //turn the wrapping off for row header and data cells.
                    td.Wrap = false;
                }

                //add cell to the row.
                td.Controls.Add(lbl);
                tr.Cells.Add(td);
            }
        }

    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

khatri.hitesh
Architect Nexvisionix Inc.
India India
Click here to email Hitesh
 
Hitesh lives in Mumbai, INDIA and works with Nexvisioinix, one of the leaders in providing solutions based on Microsoft BI technologies. (For more information on Nexvisionix visit www.nexvisionix.com)
 
Since year 2003, he has been working on Microsft .NET and BI technologies such as Microsoft Analysis Services 2005. He is also proficient in MDX.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 19 Aug 2008
Article Copyright 2008 by khatri.hitesh
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid