Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
what is Difference between filter() and where condition in MDX?

Answered what is Difference between filter() and where condition in MDX?

  • Friday, June 15, 2012 7:21 AM
     
     
    Sign In to Vote
    0

    Hi Frds,

    I am New in MDX level,

    I want difference between  filter() and where condition in MDX query level.

    I am try to MDX queries in the follwing way.

    1)select col_A on columns,

    filter(Date,Country) on rows

     from Cube

    // this query level showing some results.

    2)select col_A on columns

     from Cube

    where

    (Date,Country)

    //this query level showing some results

    Query1 results and Query2 results are not same.

    I am try to follwing another MDX query.

    3)select col_A on columns,

    filter(Date) on rows

     from Cube

    where (Country)

    this query level error message showing.

    Error:"Query (8, 11) Too few arguments were passed to the 'FILTER' function. The minimum argument count for the function is 2."

    Please send me Explanation with examples.

    Regards

    Rama

     

All Replies

  • Friday, June 15, 2012 8:16 AM
     
     Answered
    Sign In to Vote
    0

    Filter is typically used to limit down the set of items you display, and applies directly to the set you are looking at. It wont apply any filter to the rest of the data you are looking at, whilst the where clause will actually filter the data being returned to you down to that member.

    Filter requires two arguments - The set you want to filter, and the filter criteria. Whilst where can only contain valid members or sets.

    Typically you will use filter to get a list of items that have some form of condition, and you can do a lot more in terms of the filtering - ie: Only items that have sold more than 50 items - you wont get that with a where clause. The query in the where clause will then filter the returned data to the screen by that element.

    For example: I want the Amount per product in Jan 2012, where we sold more than 50 items.

    SELECT {[Measures].[Sales Amount]} ON Columns

              ,FILTER([Products].[Products].[Products], [measures].[Sales Items Sold] > 50) ON Rows

      FROM [Cube]

    WHERE [Date].[Month].[Jan 2012]