Hi Ole
Role --> Dimension Data Tab --> select your Measures Cube Dimension
I did just what is stated above and this does limit what measure a user can see, but I run into a different problem when I do this.
I'm using Time Intelligence to get YTD, MTD, etc.... and when I limit what measures a roll can see any reports they look at that referance Time Intelligence don't work. Something about the Scope Statement being messed up.
Can anyone help
".. when I limit what measures a role can see any reports they look at that referance Time Intelligence don't work .." - could you provide more details of the Time Intelligence calculations involved, and what happens Are you using time calculated measures
Below is my calc script. The problem is that when I use dimension security to hide any of the measures referanced in the scope statement, the user can no longer use the time calcs, even for the measures they have rights to.
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If deleted or modified, the data within the cube will be affected.
This command should only be edited if you intend on manually specifying how the cube will be aggregated.
*/
CALCULATE
;/*
Begin Time Intelligence script for the [Period].[Period] hierarchy.
*/
CREATE
MEMBER CURRENTCUBE.[Period].[Period Calculations].[Year to Date] AS "NA",VISIBLE
= 1;CREATE
MEMBER CURRENTCUBE.[Period].[Period Calculations].[Quarter to Date] AS "NA",VISIBLE
= 1;CREATE
MEMBER CURRENTCUBE.[Period].[Period Calculations].[Month to Date] AS "NA",VISIBLE
= 1;CREATE
MEMBER CURRENTCUBE.[Period].[Period Calculations].[Year Over Year Growth %] AS "NA",VISIBLE
= 1;Scope
({
[Measures].[Booked Tons],
[Measures].[Sold Tons],
[Measures].[Invoice Amt],
[Measures].[Open Ord Tons],
[Measures].[Open Ack Rev Amt],
[Measures].[Plan Tons],
[Measures].[Plan Invoice]
}
);
// Year to Date
(
[Period].[Period Calculations].[Year to Date],
[Period].[Acct Year].[Acct Year].
Members,[Period].[PERIOD_KEY].
Members) =
Aggregate({ [Period].[Period Calculations].
DefaultMember } * PeriodsToDate([Period].[Period].[Acct Year],
[Period].[Period].
CurrentMember)
);
// Quarter to Date
(
[Period].[Period Calculations].[Quarter to Date],
[Period].[Acct Qtr].[Acct Qtr].
Members,[Period].[PERIOD_KEY].
Members) =
Aggregate({ [Period].[Period Calculations].
DefaultMember } * PeriodsToDate([Period].[Period].[Acct Qtr],
[Period].[Period].
CurrentMember)
);
// Month to Date
(
[Period].[Period Calculations].[Month to Date],
[Period].[Acct Month].[Acct Month].
Members,[Period].[PERIOD_KEY].
Members) =
Aggregate({ [Period].[Period Calculations].
DefaultMember } * PeriodsToDate([Period].[Period].[Acct Month],
[Period].[Period].
CurrentMember)
);
// Year Over Year Growth %
(
[Period].[Period Calculations].[Year Over Year Growth %],
[Period].[Acct Year].[Acct Year].
Members ( 1 ) : Null,[Period].[PERIOD_KEY].
Members) =
(
( [Period].[Period Calculations].
DefaultMember ) -( [Period].[Period Calculations].
DefaultMember, ParallelPeriod([Period].[Period].[Acct Year],
1,
[Period].[Period].
CurrentMember)
)
)
/
( [Period].[Period Calculations].
DefaultMember, ParallelPeriod([Period].[Period].[Acct Year],
1,
[Period].[Period].
CurrentMember)
);
(
[Period].[Period Calculations].[Year Over Year Growth %],
[Period].[Acct Year].[Acct Year].
Members ( 0 ),[Period].[PERIOD_KEY].
Members) =
Null; Format_String((
[Period].[Period Calculations].[Year Over Year Growth %],
[Period].[PERIOD_KEY].
Members)
) =
"Percent";End
Scope;CREATE
MEMBER CURRENTCUBE.[MEASURES].[Sls Tons % Of Plan] AS [Measures].[Sold Tons]/[Measures].[Plan Tons],FORMAT_STRING
= "Percent",VISIBLE
= 1 ;