Using 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="">
    <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.

Community Additions