How to display data in SSAS by start and end date

• StevenM

  SSC Enthusiast

  Points: 112

  More actions

  June 23, 2023 at 8:35 am

  #4217421

  I have a SQL Server 2016 OLAP Cube in the multidimensional mode and a corresponding database containing the tables Cases and Person. The Case table has the columns StartDate and EndDate and the Person table has the columns FirstName, LastName, and BirthDate. I'm using Visual Studio with the template Multidimensional Project to modify the cube.

  I want the cube to have multiple Time attributes: Year, Quarter, Month, Week, and Date. These attributes should be placed in the left row, and their corresponding count of Cases should be displayed, where, for example, the Year is between the StartDate and the EndDate. Specifically, when I select Year as the displaying value, it should show all cases that had some occurrence in that year. Valid examples listed for the Year 2011 would be cases that started before 2011 and ended in 2011, cases that started in 2011 and ended in 2011, cases that started in 2011 and ended in one of the following years, and cases that started before 2011 and ended after 2011.

  How it should display for Year:

  

  How it might display for Quarter:

  

  The display doesn't have to be exactly as described; it could also work as a column. I only want a statistical view.

  I have already spent one week trying to get it working but without success. The case count is always the same for each date.

  I already created the project in Visual Studio and defined the dimensions and created the cube, it only needs modification to work with my scenario.

  I have found a website that could have a possible solution, however, I need the script to work with my dimension:

  AGGREGATE( 
  {NULL:LINKMEMBER([Date].[Calendar].CURRENTMEMBER
  ,[Start Date].[Calendar])}
  * {LINKMEMBER([Date].[Calendar].CURRENTMEMBER
  , [End Date].[Calendar]):NULL}
  , [Measures].[Project Count])

  https://www.purplefrogsystems.com/2013/04/mdx-between-start-date-and-end-date/

  Thank you in advance.

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  June 24, 2023 at 9:10 am

  #4218215

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.