| title | description | ms.date | ms.service | ms.custom | ms.topic | ms.author | ms.reviewer | author |
|---|---|---|---|---|---|---|---|---|
Using Variables and Parameters (MDX) | Microsoft Docs |
Learn how you can you can parameterize a Multidimensional Expressions (MDX) statement in Microsoft SQL Server Analysis Services. |
05/02/2018 |
analysis-services |
mdx |
conceptual |
kfollis |
kfollis |
kfollis |
[!INCLUDEappliesto-sqlas] In [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssASnoversion], 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/region 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.