CODEWITHSUNDEEP
ssasmdx
Paiman
Paiman

Reputation: 1

Building Analysis Service Queries(MDX) With Filters In Reporting Service

I'm having problem building MDX Queries in reporting service. I've searched through internet a lot about it and despite getting clear examples of MDX query and how to build it, I'm getting syntax error all the time. so here is the thing:

What I want to do is quite simple. I want to filter the data and only include rows that have equal values in corresponding columns and parameters. The original query generated by query builder is:

SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS,
 NON EMPTY { 
([Net Object Hierarchy].[Central Telecom Center].[Central Telecom Center].ALLMEMBERS * 
[Net Object Hierarchy].[Province].[Province].ALLMEMBERS * 
[Net Object Hierarchy].[County].[County].ALLMEMBERS * 
[Net Object Hierarchy].[District].[District].ALLMEMBERS * 
[Net Object Hierarchy].[Rural].[Rural].ALLMEMBERS * 
[Net Object Hierarchy].[Village].[Village].ALLMEMBERS * 
[Net Object Hierarchy].[Telecom Center].[Telecom Center].ALLMEMBERS * 
[Net Object Hierarchy].[Switch].[Switch].ALLMEMBERS * 
[Net Object Hierarchy].[Prefix].[Prefix].ALLMEMBERS * 
[Measure].[Measure ID].[Measure ID].ALLMEMBERS * 
DESCENDANTS([Date To].[Parent Date Key].[Level 02].ALLMEMBERS) ) } ON ROWS 
FROM [Irtel Planning Statistics]

The parameters to filter the data include: "paramCentralTelecomCenter", "paramProvince", "paramCounty", "paramDistrict", ..., "paramPrefix".

For instance, if the value of the parameter paramProvince is set to "CA", I just need to retrieve those records with the value of [Net Object Hierarchy].[Province].[Province]="CA". This should be very simple but I'm completely lost.

Can anyone help me with that please? Thanks in advance.

Upvotes: 0

Views: 384

Answers (1)

Sam Holloway
Sam Holloway

Reputation: 2009

You need to use a WHERE clause in your SELECT. This goes at the end, after the FROM.

For example, the following will restrict your query to those elements where Province is set to 'CA':

WHERE [Net Object Hierarchy].[Province].[Province].[CA]

Upvotes: 0

Related Questions