Parametrized MDX

When you are building SSRS reports from cube and in your reports you have certain number of parameters and you want to verify MDX before putting into the report. Like SQL you can create parameterized query in MDX using the XMLA .following is the syntax for parameterized query in SSMS.

Syntax:

</Execute

You need to put your MDX in the command tag as mentioned below and run in the XMLA editor of SSMS.

QUERY:

select [Measures].[Account Code Alternate Key] on 0,

FILTER ( [Dim Geography].[Country Region Code].members,

[Dim Geography].[Country Region Code].currentmember.name=@CountryName )on 1

from [Adventure Works DW]

CountryName

AU

Output

following is the output and you get your desired result in the CellData tag with appropriate data type.

[Dim Reseller – Geography – Sales Territory].[Sales Territory Country].[All]

All

[Dim Reseller – Geography – Sales Territory].[Sales Territory Country].[(All)]

0

8

[Dim Reseller – Geography – Sales Territory].[Sales Territory Group].[All]

All

[Dim Reseller – Geography – Sales Territory].[Sales Territory Group].[(All)]

0

5

364312

364312

Advertisements

About Zaim Raza

7 Years of professional experience in Business intelligence using SQL Server and Microsoft SharePoint/.net Technologies. Experience in Data warehousing, Data Modeling, Data profiling, Data Integration, Data cleansing, consultations and enterprise reporting. I want to work for organization where I am able to utilize my skills, experience and knowledge to transform the technology into business value.
This entry was posted in MDX. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s