Show child levels when the top level is forced to be null - Avoid aggregations on the top level

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 28, 2015 at 6:21 am

  #150906

  Hi everybody,

  it was difficult to select a title for my question.

  Let´s say I have a geographical hierarchy with Region --> Country --> District --> Store levels

  I want to avoid the aggregations in local currency at the Region Level because that makes no sense.

  I scoped the Net Sales measure like this:

  SCOPE ([Measures].[Net Sales], [Fx Rate].[Fx Rate].[Local Currency], [Stores].[Store].Members);

  this = SUM([Fx Rate].[Fx Rate].&[1], [Measures].[Net Sales LC]);

  END SCOPE;

  SCOPE ([Measures].[Net Sales], [Fx Rate].[Fx Rate].[Local Currency], [Stores].[Region].Members);

  this = null;

  END SCOPE;

  The scopes are working but I have a visualization problem. When I drag and drop the geo hierarchy in the pivot table nothing is shown because the upper level (Region) has only empty cells (this=null;). If I change the Fx Rate type to a reference currency, then the regions are shown and I can expand the lower levels, change the filter again to local currency and the values are back, but this is not the best approach.

  Any ideas about how to tackle that? Any comment would be appreciated

  Kind Regards

  Paul Hernández

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 29, 2015 at 2:35 am

  #1794219

  Hi everybody,

  if someone is interested I got the answer in the MSDN forum. The solution is explained is this post:

  http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=24

  Paul Hernández