Parameterized MDX.

When you are building SSRS reports from cube and in your reports you have certain number of parameters and you want to verify  MDX before putting into the report. Like SQL you can create parameterized query in MDX using the XMLA .following is the syntax for parameterized query in SSMS.

Syntax:

<Execute>

   <Command>…</Command>

   <Properties>…</Properties>

   <Parameters>…</Parameters>

 </Execute

 You need to put your MDX in the command tag as mentioned below and run in the XMLA editor of SSMS.

QUERY:

<Envelope xmlns=”http://schemas.xmlsoap.org/soap/envelope/”&gt;

  <Body>

    <Execute xmlns=”urn:schemas-microsoft-com:xml-analysis”>

      <Command>

        <Statement>

          select [Measures].[Account Code Alternate Key] on 0,

          FILTER ( [Dim Geography].[Country Region Code].members,

          [Dim Geography].[Country Region    Code].currentmember.name=@CountryName )on 1

          from [Adventure Works DW]

        </Statement>

      </Command>

      <Properties />

      <Parameters>

        <Parameter>

          <Name>CountryName</Name>

          <Value>AU</Value>

        </Parameter>

      </Parameters>

    </Execute>

  </Body>

</Envelope>

 

Output

following is the output and you get your desired result in the CellData tag with appropriate data type.

        <root xmlns=”urn:schemas-microsoft-com:xml-analysis:mddataset” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:msxmla=”http://schemas.microsoft.com/analysisservices/2003/xmla”&gt;

         …

         …

         …

         …

         …

         …

         …

                    <UName>[Dim Reseller – Geography – Sales Territory].[Sales Territory Country].[All]</UName>

                    <Caption>All</Caption>

                    <LName>[Dim Reseller – Geography – Sales Territory].[Sales Territory Country].[(All)]</LName>

                    <LNum>0</LNum>

                    <DisplayInfo>8</DisplayInfo>

                  </Member>

                  <Member Hierarchy=”[Dim Reseller – Geography – Sales Territory].[Sales Territory Group]”>

                    <UName>[Dim Reseller – Geography – Sales Territory].[Sales Territory Group].[All]</UName>

                    <Caption>All</Caption>

                    <LName>[Dim Reseller – Geography – Sales Territory].[Sales Territory Group].[(All)]</LName>

                    <LNum>0</LNum>

                    <DisplayInfo>5</DisplayInfo>

                  </Member>

                </Tuple>

              </Tuples>

            </Axis>

          </Axes>

          <CellData>

            <Cell CellOrdinal=”0″>

              <Value xsi:type=”xsd:int”>364312</Value>

              <FmtValue>364312</FmtValue>

            </Cell>

          </CellData>

        </root>

      </return>

    </ExecuteResponse>

  </soap:Body>

</soap:Envelope>

Advertisements

About Zaim Raza

7 Years of professional experience in Business intelligence using SQL Server and Microsoft SharePoint/.net Technologies. Experience in Data warehousing, Data Modeling, Data profiling, Data Integration, Data cleansing, consultations and enterprise reporting. I want to work for organization where I am able to utilize my skills, experience and knowledge to transform the technology into business value.
This entry was posted in SSAS. Bookmark the permalink.

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