Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSAS - How to filter by date range

Unanswered SSAS - How to filter by date range

  • Wednesday, February 20, 2013 9:58 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have an excel based on SSAS cube with date dimentions.

    Users can filter by certain month/year etc but we can't figure how to filter by certain date range- ex from 15/1/13 to 20/03/13.

    How / Is this easily possible for the end user?

    Namnami

     

All Replies

  • Wednesday, February 20, 2013 10:21 AM
     
     
    Sign In to Vote
    0

    Hey Namnami,

    Best possible way to filter the date is by creating hierarchies in Date Dimension,  i.e., Year, Quarter,Month,Day and even week if you want.

    So users can filter the date based on the hierarchies.

    For ex: If users want to filter from 15/1/13 to 20/03/13, they will check only 1st quarter in 2013 year and filter it.

    Hope this helps

    Thanks

    Rajasekhar

     
  • Wednesday, February 20, 2013 10:29 AM
     
     
    Sign In to Vote
    0

    My date dimension already has hierarchies. I don't clearly understand. User filters to see only first quarter in 2013. But then in order to filter only for certain dates the user needs to filter by date and check every date they want filtered? (and it still gives the user the whole list of dates)

    Thanks

    Namnami

     
  • Wednesday, February 20, 2013 12:43 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi.

    Put your date dimension in the report filter area in Pivot Table design tool. When you open the filter in the Pivot Table you have the option to select multiple items in a box down to the left.

    BR

    Thomas Ivarsson

     
  • Thursday, February 21, 2013 11:53 AM
     
     
    Sign In to Vote
    0

    Thanks Thomas Ivarsson, 

    But then I need to check every single date in the range... If I need  15/1/13 to 20/03/13 that means selecting over 50 check boxes! Isn't there a better way to filter the data for range of dates?

    Namnami

     
  • Thursday, February 21, 2013 11:58 AM
     
     
    Sign In to Vote
    0

    RajaSekhar, 

    I don't understand how can I filter first by quarter and then have the date filter list display only the quarter for me to narrow the filtering more. Please explain if this is possible.

     

    Namnami

     
  • Thursday, February 21, 2013 5:15 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    This is solved in Excel 2013 with the TimeLine Control. In earlier versions I do not know about a good solution.  In SSRS you can have parameters like a start date and an end date.

    HTH

    Thomas Ivarsson

     
  • Tuesday, February 26, 2013 6:20 AM
     
     
    Sign In to Vote
    0

    Thanks Mr Ivarsson, 

    Is there any solution, perhaps even a simple MDX code or something, which I can give the end user using Microsoft Excel Ver 14 (office 2010) to use?

    Namnami