locked
Filter versus Slicer complexity RRS feed

  • Question

  • Hi folks,

    I want to know what is the correct syntax for using filter function? I am posting two queries below. One using Where clause and another using Filter function columns. I expect to see same result set being returned in both the cases but I am not seeing them the same. Is there anything I am missing here?

    Query with slicer - I get 55 rows back

    select
         [Measures].[Qty] on columns,
          {[Product].[Product Hier].[Item].members } on rows
    from [Cube]
    WHERE (
                {[Product].[Manufacturer].[Kinsley]}, 
               {

               [Product].[Category].&[01],[Product].[Category].&[02],[Product].[Category].&[03],[Product].[Category].&[04]

                }
             )

    Query with Filter function - I get 67 rows back

    select
    [Measures].[Qty] on  columns,
    (
          {     filter ({[Product].[Product Hier].[Item].members},
              ([Product].[Manufacturer] = [Product].[Manufacturer].[Kinsley]
              and (
                   [Product].[Category] = [Product].[Category].&[01]
                   or [Product].[Category] = [Product].[Category].&[02]
                   or [Product].[Category] = [Product].[Category].&[03]
                   or [Product].[Category] = [Product].[Category].&[04]
                  
                  )
              )
          )}
    ) on rows
    from [Cube]

    ** Problem is query with filter function doesnot limit the result set by Manufacturer 'Kinsley' only but it also returns Items for other manufacturers as well.

    Is there something I am missing in the second query? 

    Thanks for your reponse in advance.

     

     

     

    Friday, September 30, 2011 3:50 PM

Answers

  • When you use the clause in the slicer, you are restricting the cube space to look at that Manufacturer specifically, and the measure values returned are what you expect.

    In the other case, what it does is, give you the list of Products that meet the filter criteria, and that is it. The measure is still computed for the whole cube, and that will include the Manufacturer you thought you filtered out.

     

    HTH

     


    -Remember to mark as helpful/the answer if you agree with the post.
    • Marked as answer by zurich008 Friday, September 30, 2011 8:16 PM
    Friday, September 30, 2011 5:51 PM

All replies

  • The results are different because in the first query, you are using the conditions in the slicer axis, so the context is set to that space, and the measure is aggregated accordingly

    In the second case, you are filtering the ROWS axis, and it will give you the products that meet the filter criteria, but then the measure [Qty] will be computed on the whole cube, and not be restrcited to the manufacturer or category as you expect.

    HTH

     


    -Remember to mark as helpful/the answer if you agree with the post.
    Friday, September 30, 2011 4:34 PM
  • Thanks for ur response Naveen. Evenif I select null on columnns instead of Qty, issue exists.

    Also, in my second query if I include slicer axis, I get right numbers back.. Why is it so?

    select
    [Measures].[Qty] on  columns,
    (
          {     filter ({[Product].[Product Hier].[Item].members},
              ([Product].[Manufacturer] = [Product].[Manufacturer].[Kinsley]
              and (
                   [Product].[Category] = [Product].[Category].&[01]
                   or [Product].[Category] = [Product].[Category].&[02]
                   or [Product].[Category] = [Product].[Category].&[03]
                   or [Product].[Category] = [Product].[Category].&[04]
                  
                  )
              )
          )}
    ) on rows
    from [Cube]

    where [Product].[Manufacturer].[Kinsley]

    Friday, September 30, 2011 5:03 PM
  • When you use the clause in the slicer, you are restricting the cube space to look at that Manufacturer specifically, and the measure values returned are what you expect.

    In the other case, what it does is, give you the list of Products that meet the filter criteria, and that is it. The measure is still computed for the whole cube, and that will include the Manufacturer you thought you filtered out.

     

    HTH

     


    -Remember to mark as helpful/the answer if you agree with the post.
    • Marked as answer by zurich008 Friday, September 30, 2011 8:16 PM
    Friday, September 30, 2011 5:51 PM
  • Okay, got it. Is there a different way to write the same mdx by NOT using the where clause , yet still get the same results / 55 rows back.?
    Friday, September 30, 2011 8:01 PM
  • You can try either of the following:

    Use the expression in a subcube instead of the slicer

    SELECT [Measures].[Qty] on columns, 
    	[Product].[Product Hier].[Item].members on rows 
    from (
    SELECT {[Product].[Manufacturer].[Kinsley]}*{[Product].[Category].&[01],[Product].[Category].&[02],[Product].[Category].&[03],[Product].[Category].&[04]} ON 0
    FROM [Cube]) 
    
    


    Or, you can use a calculated member, as

    WITH MEMBER [Measures].[C Qty] AS
    Aggregate([Product].[Manufacturer].[Kinsley]
    * {[Product].[Category].&[01],[Product].[Category].&[02],[Product].[Category].&[03],[Product].[Category].&[04]}
    , [Measures].[Qty])
    SELECT [Measures].[C Qty] on columns, 
    	[Product].[Product Hier].[Item].members on rows 
    from [Cube] 
    
    


    HTH

     


    -Remember to mark as helpful/the answer if you agree with the post.
    Friday, September 30, 2011 8:26 PM