Thomas.I Microsoft BI & Analytics

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

Filter in the slicer, is it possible?

Posted by thomasivarssonmalmo on November 18, 2007

In my previous blogpost I discussed the status of the slicer(where clause in MDX) with the combination of a filter on an axis. I have had som doubts due to the behaviour,in AS2000(imagined or real), that was not always clear to me. This far I have not been suprised.

This post will not have any real news but I was asked if it was possible to put a filter in the slicer. I said no but that was wrong. I can refer to the comparision with the behaviour of a filter in TSQL.

Lets start with the query from my previous post here and run this MDX Select in management studio.

Select Filter([Date].[Calendar].[Calendar Year].Members,[Measures].[Internet Sales Amount] > 9000000) On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Sales Amount]);

The result will only be the the calendar years 2003 and 2004 because they are the only years with total sales above 9 000 000 .

It is possible to confuse people by making a slicer with another measure than is used in the filter clause on an axis

Select Filter([Date].[Calendar].[Calendar Year].Members,[Measures].[Internet Sales Amount] > 9000000) On Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where ([Measures].[Internet Order Quantity]);

You will see the measure [Internet Order Quantity] in the cells but the columns are sliced by [Internet Sales Amount] . The business scenario for this combination is limited but technically it is possible to do. Most clients supports this scenario.

Can we put this filter i the slicer? Run this MDX Select.

Select [Date].[Calendar].[Calendar Year].Members on Columns,
[Customer].[Customer Geography].[Country].members On Rows
From [Adventure Works]
Where (Filter([Date].[Calendar Year].[Calendar Year].members,
[Measures].[Internet Sales Amount] > 9000000),[Measures].[Internet Sales Amount]);

You will see the same result as for the first query. I have not seen a client that implements this yet. ProClarity Professional (6.3) only have filter on an axis but since the result is the same this does not matter. AS2000 was more restricted about what you could put in the slicer.

Advertisements

2 Responses to “Filter in the slicer, is it possible?”

  1. Darren said

    For the type of filter you have in your sample query you could also use the HAVING keyword on the axis.
    eg.
     
    Select [Date].[Calendar].[Calendar Year].Members
    having [Measures].[Internet Sales Amount] > 9000000
    on Columns,[Customer].[Customer Geography].[Country].members On RowsFrom [Adventure Works]Where [Measures].[Internet Sales Amount];

  2. Thomas said

    Hello Darren! Thank’s for the comment. I have thought about HAVING and had an idea to mention it. In MDX Solutions they claim that HAVING is evaluated after the NON EMPTY clause, but it works fine without NON EMPTY. According to the same book the result of HAVING and FILTER is the same but the HAVING-syntax is a little bit easier. 
     
    You are right about that HAVING should be mentioned. 

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: