Scope using different data hierarchies

• Maxime.Gagne

  SSC Eights!

  Points: 886

  More actions

  September 22, 2014 at 9:58 am

  #150779

  Hi,

  I've bee trying to make a scope act upon which hierarchy is chosen, but the behavior is not as intended.

  I created a dimension to deal with the Date aggregation so I do not ahve to create manually each calculation for every measures.

  This works perfectly well when there is only one date Hierarchy. The cube I'm working with as both Fiscal and Calendar hierarchies. Using the following code, I get the values back for Calendar Hierarchy, but Financial hierarchy won't.

  -- Year To Date

  SCOPE([2 - Date Tool].[Date Tool].[YTD]);

  SCOPE([1 - Dates].[Financial Hierarchy].MEMBERS);

  THIS = IIF(ISEMPTY([Date Tool].DefaultMember)

  , NULL

  , Aggregate(

  { [Date Tool].DefaultMember } *

  PeriodsToDate(

  [1 - Dates].[Financial Hierarchy].[Financial Year]

  , [1 - Dates].[Financial Hierarchy].CurrentMember

  ) )

  );

  END SCOPE;

  SCOPE([1 - Dates].[Calendar Hierarchy].MEMBERS);

  THIS = IIF(ISEMPTY([Date Tool].DefaultMember)

  , NULL

  , Aggregate(

  { [Date Tool].DefaultMember } *

  PeriodsToDate(

  [1 - Dates].[Calendar Hierarchy].[Calendar Year]

  , [1 - Dates].[Calendar Hierarchy].CurrentMember

  ) )

  );

  END SCOPE;

  END SCOPE;

  If anyone has an idea regarding this, I've been scraching my head for two days now.

  Thanks