Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Filter members data from different hierarchies

Answered Filter members data from different hierarchies

  • Sunday, June 17, 2012 2:46 PM
     
     
    Sign In to Vote
    0

    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


     

All Replies

  • Sunday, June 17, 2012 6:08 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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