Creating Total to-date query...

Creating Total to-date query...

• Wil Burton

  Something like [Time].[Date].FirstChild:[Time].[Date].Lastchild might help. Be aware of performance issues with a large dimension.

  Regards

  Thomas Ivarsson

  
  

• AlexBB

  Thanks guys.

  Both answers work.
  Is there any performance difference between
  using NULL or [Time].[Date].FirstChild as the
  beginning of the range

  
  
  

• SiTec

  I am not familiar enough with adventureworks
  to give a proper example.

  However I will attempt to explain my scenario.
  We have a timesheet system where time spent
  on various projects/tasks is collected and then
  billed to a client.
  
  A report needs to be created that shows the
  previous weeks time (a particualr 1 week period with
  a start and end date) spent on a particular project/task
  by employee X and the total time spent so far on that
  project/task by emplyee X up until the end of that period.
  This means that year-to-date (or period-to-date) scenario
  does not work because we want ALL time up until
  the end of the selected period and there is no guarantee
  that a project will last a week, month, year, etc...

  Hope that somewhat clarifies the usage scenario.

  
  
  

• Juco

  Okay this is not working the way I anticipated.
  I am clearly not understanding MDX and the book
  I am refering to:
  'SQL Server 2005 Analysis Services 2005 with MDX'
  is of no help.
  
  Here is what I am trying to accomplish conceptually:
  

  WITH MEMBER [Measures].[TimeChargedToDate] AS
  'AGGREGATE ( {NULL:@ToTimesheetDate} )'

  SELECT NON EMPTY
  {
  [Measures].[Time Charged],
  [Measures].[TimeChargedToDate]
  } ON COLUMNS,
  NON EMPTY
  {
  ([Project].[Project Number].[Project Number].ALLMEMBERS
  * [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
  }
  ON ROWS
  FROM
  (
  SELECT ( STRTOSET(@ProjectProjectNumber, CONSTRAINED) ) ON COLUMNS
  FROM
  (
  SELECT ( STRTOMEMBER(@FromTimesheetDate, CONSTRAINED)
  : STRTOMEMBER(@ToTimesheetDate, CONSTRAINED) ) ON COLUMNS
  FROM [Timesheet_Cube]
  )
  )

  So the moral of the story here is that I want to get
  a list of all the projects, then all the users that have
  charged time to each project. Then I want the total
  hours for the time range specified AS WELL AS the
  total time charge up until the cutoff date (@ToTimesheetDate).

  Now as I have just learned you cannot have aggregates
  in your Measures, so can some kind soul please
  point me to a source where I can ejumicate myself on
  this MDX magic It seems that its much more difficult
  to operate with ranges of time in MDX than I though it should be.

  
  
  

  
  
  

• ahmedilyas

  If you are trying to do something like I
  described above, here is how to do it:
  
  

  WITH MEMBER [Measures].[TimeChargedToDate] AS
  'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

  SELECT NON EMPTY
  {
      [Measures].[Time Charged],
      [Measures].[TimeChargedToDate]
  } ON COLUMNS,
  NON EMPTY
  {
      (
          [Project].[Project Number].&[PA01203] *
          [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS
      )
  } ON ROWS
  FROM
  (
      SELECT
      {[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]}
      ON COLUMNS
      FROM
      (
          SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
          FROM [Timesheet_Cube]
      )
  )

  
  
  

• ratslav

  Well, I'm still unclear about the usage scenario - if the calculated member is defined on the [Time].[Date] hierarchy, then on which hierarchy will the user select a time period If you can translate this to the Adventure Works cube, that would help clarify.
  
  

• Autofreak

  If you're using AS 2005, then you can try the shorthand:

  { NULL : [Timesheet].[Date].[Year].&[2006].&[9].&[15] }

  
  
  

• mexy

  If you are trying to do something like I
  described above, here is how to do it:
  
  

  WITH MEMBER [Measures].[TimeChargedToDate] AS
  'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

  SELECT NON EMPTY
  {
  [Measures].[Time Charged],
  [Measures].[TimeChargedToDate]
  } ON COLUMNS,
  NON EMPTY
  {
  ([Project].[Project Number].&[PA01203] * [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
  } ON ROWS
  FROM
  (
  SELECT
  {[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]} ON COLUMNS
  FROM
  (
  SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
  FROM [Timesheet_Cube]
  )
  )

  
  
  
• Creating Total to-date query...