Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

The MDX slicer will not change anything on an axis or ?

Posted by thomasivarssonmalmo on October 21, 2007

I am playing around with MDX to make a presentation for some beginners tomorrow. For several years I have had the thought that the slicer or the where clause will never influence member of the each axis(columns, rows), only values in the cells.
 
But try this MDX select in Management Studio.
 
Select [Date].[Calendar].[Calendar Year].members  On Columns,
TopCount(CrossJoin({[Product].[Product Categories].[Category].members},
{[Customer].[Customer Geography].[Country]}),10,[Measures].[Internet Sales Amount])on Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2001]);
 
You will only see 2001 on the column. I think that this behaviour is good and clear and I will have to change my tutorial according to this. If recall this right this behaviour was not the same before SP1.
 
And if you run this statement, you will see all years on columns but the TopCount seems to evaluate year 2004. I have always thought that it would pick the all level of the time dimension. Perhaps it does.
Select [Date].[Calendar].[Calendar Year].members  On Columns,
TopCount(CrossJoin({[Product].[Product Categories].[Category].members},
{[Customer].[Customer Geography].[Country]}),10,[Measures].[Internet Sales Amount])on Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2001]:[Date].[Calendar Year].&[2004]);
 
I will check this more carefully later this week.
 
Advertisements

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

 
%d bloggers like this: