Skip to main content
Using Variables and Parameters (MDX)

Applies To: SQL Server 2016

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="">  
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">  
select [Measures].members on 0,   
       Filter(Customer.[Customer Geography].Country.members,   
              Customer.[Customer Geography].CurrentMember.Name =  
              @CountryName) on 1  
from [Adventure Works]  
      <Properties />  
          <Value>'United Kingdom'</Value>  

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.

MDX Scripting Fundamentals (Analysis Services)