Get SSAS cube data in Tabular form using MDX and Fill into Asp Dropdown

Question

I want to get the Operating Units in Asp Dropdown list from SSAS Cube.

The structure is:

I want all of the members in a result DataSet/DataTable then I'll be able to bind the DataTable to Asp DropDown.

My Desired Result set is like :

I've tried the following MDX:

select {} on 0,

[Product Hierarchy].[Operating Unit].members on 1
from
[Consolidated Sales];

The output I'm getting is:

I am unable to fill the result set in DataTable in asp.net, I've tried the following code:

//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);
                DataTable dt = new DataTable();
                AdomdCommand cmd = new AdomdCommand();
                cmd = conn.CreateCommand();
               //cmd.Parameters.Add("DimProductRegion", "Bike");
                cmd.CommandText = "select {   }                on columns,             [Product Hierarchy].[Operating Unit].[Operating Unit]        on rows from [Consolidated Sales]";

                AdomdDataAdapter da = new AdomdDataAdapter(cmd);
                da.Fill(dt);


                DDL_Product.DataSource = dt;
                DDL_Product.DataTextField = "ParameterCaption";
                DDL_Product.DataValueField = "ParameterValue";
                DDL_Product.DataBind();

Please Help me resolve this. I'm new to MDX

Please Share the output you are getting after running the above MDX. — Nishant Gupta

ryancopester ryancopester · Accepted Answer · 2018-09-11T12:57:54

Do you have access to the source table for the Dimensions? I'm not an expert on MDX by any means, but I've always used the source tables directly when populating lists in an app.

Another tip - open Visual Studio, select Business Intelligence, then create a new project using the "Report Server Project Wizard." Connect to your SSAS CUBE, select the dimensions (or measures) you desire, and it'll generate an MDX query for you.

Get SSAS cube data in Tabular form using MDX and Fill into Asp Dropdown

1 Answers