Export (0) Print
Expand All

Using Variables and Parameters (MDX)

In Microsoft SQL Server Analysis Services, you can parameterize a Multidimensional Expressions (MDX) statement. A parameterized statement lets you create generic statements that can be customized at runtime.

In creating a parameterized statement, you identify the parameter name by prefixing the name with the at sign (@). For example, @Year would be a valid parameter name

MDX supports only parameters for literal or scalar values. To create a parameter that references a member, set, or tuple, you would have to use a function such as StrToMember or StrToSet.

In the following XML for Analysis (XMLA) example, the @CountryName parameter will contain the country for which customer data is retrieved:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>
select [Measures].members on 0, 
       Filter(Customer.[Customer Geography].Country.members, 
              Customer.[Customer Geography].CurrentMember.Name =
              @CountryName) on 1
from [Adventure Works]
</Statement>
      </Command>
      <Properties />
      <Parameters>
        <Parameter>
          <Name>CountryName</Name>
          <Value>'United Kingdom'</Value>
        </Parameter>
      </Parameters>
    </Execute>
  </Body>
</Envelope>

To use this functionality with OLE DB, you would use the ICommandWithParameters interface. To use this functionality with ADOMD.Net, you would use the AdomdCommand.Parameters collection.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft