ParallelPeriod using dynamic string

• Paul Treston

  Mr or Mrs. 500

  Points: 590

  More actions

  December 19, 2013 at 4:58 am

  #148010

  Hello there,

  I'm trying to make the below ParallelPeriod named set dynamic.

  This works fine:

  WITH DYNAMIC SET [Test] AS

  StrToSet

  (

  "ParallelPeriod([Finance Date].[Year-Week].[Week],1,[Finance Date].[Year-Week].[2013 Week 45])"

  )

  SELECT {[Test]} ON COLUMNS

  FROM [Cube];

  This produce an error of:

  WITH DYNAMIC SET [Test] AS

  StrToSet

  (

  "ParallelPeriod([Finance Date].[Year-Week].[Week],1,[Finance Date].[Year-Week].&[" + Cstr(datepart("yyyy",now())) + " Week " + Cstr(datepart("ww",now())) + "])"

  )

  SELECT {[Test]} ON COLUMNS

  FROM [Cube];

  Executing the query ...

  The '2013 Week 51' string cannot be converted to the date type.

  Execution complete

  Obviously I need to somehow convert the string '2013 Week 51' in to a member value that SSAS will accept. Does anyone have any suggestions ?

• Paul Treston

  Mr or Mrs. 500

  Points: 590

  More actions

  December 19, 2013 at 8:45 am

  #1674863

  I've figured it out. A nested StrToMember (with the right syntax) worked:

  WITH DYNAMIC SET [Test] AS

  StrToSet

  (

  "

  ParallelPeriod

  (

  [Finance Date].[Year-Week].[Week],

  7,

  StrToMember('[Finance Date].[Year-Week].[" + Cstr(datepart("yyyy",now())) + " Week " + Cstr(datepart("ww",now())) + "]')

  )

  "

  )

  SELECT {[Test]} ON COLUMNS

  FROM [Cube];