Share via

Using Variables and Parameters (MDX)

  • Article

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="https://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.

See Also

MDX Scripting Fundamentals (Analysis Services)