none
Filter members data from different hierarchies

    Question

  • Hi All,

    I'm trying to write a query that will show members from one level in hierarchy X and will be filter by members from hierarchy Y.

    for example in Adventurworks DB query, I want to see the sales amount by fiscal month, filtered by dates from the Gregorian calendar.

    select [Measures].[Internet Sales Amount] on 0,
    [Date].[Fiscal].[Month] on 1
    from [Adventure Works]
    where ({[Date].[Calendar].[Month].&[2001]&[9].&[35]&[2001].&[20010901],[Date].[Calendar].[Month].&[2001]&[10].&[40]&[2001].&[20011001]})

    The problem is that when I put in the where clause more then one member i get the desired result. But when I put a set of dates, it shows me the sales amount in the entire fiscal month, like it ignoring the filter.

    any suggestion why does the filter is not being applied?

    Do I have any other simple way to get the desired result?

    Thanks,

    Liran


    Sunday, June 17, 2012 2:46 PM

Answers

  • The effect of the WHERE clause means that members of fiscal month (as pre calculated aggregations members)  will be sliced by the dates you put in the slicer.  Mdx will look at those fiscal months that exists with those dates in regard with the facts.

    The query the most equivalent to yours without a WHERE clause should be:

    SELECT 
      [Measures].[Internet Sales Amount] ON 0
     ,Exists
      (
        [Date].[Fiscal].[Month].MEMBERS
       ,{
          [Date].[Calendar].[Month].&[2001]&[9].&[35]&[2001].&[20010901]
         ,[Date].[Calendar].[Month].&[2001]&[10].&[40]&[2001].&[20011001]
        }
       ,"Internet Sales"
      ) ON 1
    FROM [Adventure Works];

    You filter would work fine if the filter has the same granularity as your set.

    If the desired result is to limited the showed months to those that can exist with a manageable calendar date filter and reduce the value of them to the aggregated value of the filter you can try a subselect:

    SELECT 
      [Measures].[Internet Sales Amount] ON 0
     ,[Date].[Fiscal].[Month] ON 1
    FROM 
    (
      SELECT 
        [Measures].[Internet Sales Amount] ON 0
       ,{
          [Date].[Calendar].[Month].&[2001]&[9].&[35]&[2001].&[20010901]
         ,[Date].[Calendar].[Month].&[2001]&[10].&[40]&[2001].&[20011001]
        } ON 1
      FROM [Adventure Works]
    );

    Philip


    • Edited by VHteghem_Ph Sunday, June 17, 2012 7:21 PM
    • Proposed as answer by Martin Mason Sunday, June 17, 2012 8:10 PM
    • Marked as answer by Eileen Zhao Monday, July 09, 2012 2:37 AM
    Sunday, June 17, 2012 6:08 PM