Hiding calculated measures

• Ian C0ckcroft

  Hall of Fame

  Points: 3373

  More actions

  November 2, 2016 at 10:55 pm

  #331505

  Hi guys,

  in order to get around the seurity issues with not being able to deselect certain calculated measures for certain roles, I have created a dummy measure in the view from the DW.

  I then use a SCOPE statement to populate this measure

  Dummy measure:

  SELECT DISTINCT

  CAST(0 AS REAL) AS [Vendor Contractor Cost by Month],

  CAST(0 AS REAL) AS [Payroll Contractor Cost by Month],

  CAST(0 AS REAL) AS [Full Time Employee Cost by Month], TimeByDay

  FROM Dim_Time

  the SCOPE statement

  SCOPE ([Measures].[Full Time Employee Cost By Month]);

  THIS = ([Measures].[Actual Cost By Month], [Resource].[Resource Employee Type].[Full Time Employee]);

  END SCOPE;

  SCOPE ([Measures].[Payroll Contractor Cost By Month]);

  THIS = ([Measures].[Actual Cost By Month], [Resource].[Resource Employee Type].&[Payroll Contractor]);

  END SCOPE;

  SCOPE ([Measures].[Vendor Contractor Cost By Month]);

  THIS = ([Measures].[Actual Cost By Month],[Resource].[Resource Employee Type].&[Vendor Contractor] );

  END SCOPE;

  this works fine for User not in the restricted group

  as soon as i test for a user in the restricted group, I get the error that the measure dont exist.

  I presume its looking for them, but they hidden.

  Any idea how to get around this

  thanks guys

  Ian

  Ian Cockcroft
  MCITP BI Specialist

• Ian C0ckcroft

  Hall of Fame

  Points: 3373

  More actions

  November 3, 2016 at 5:14 am

  #1910310

  I did find a work around. set ScriptErrorHandlingMode to IgnoreAll

  to understand what I am doing, check this ooout http://sqlblog.com/blogs/alberto_ferrari/archive/2009/02/05/measure-tool.aspx

  cheers

  Ian

  Ian Cockcroft
  MCITP BI Specialist