Thank you very much again.
You say: ¡°
v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.
v This would return a cellset and the stored proc would extract the value of the required cell and return that value
¡°
But I wonder if in this way the ¡°poor¡± user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to¡ In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way
However, I give you an example of what I need to do. Let¡¯s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).
I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].
Role1 can access only the ¡°[DIM Mesi].[DIM Mesi].&[01]¡±, ¡°[DIM Mesi].[DIM Mesi].&[02]¡± and the ¡±existing¡± with these ones (I mean the ancestors of these members, which in this case, is the AllMember).
There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).
The calculations are something similar to the ones below.
I¡¯d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.
Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can¡¯t see that value) so that the total is not twice, in case I don¡¯t use VisualTotals.
If possible, I¡¯d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong
THANK YOU VERY MUCH for your kind help!
CALCULATE;
CREATE SET CURRENTCUBE.[GoodMembers]
AS {
iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),
iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),
iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])
};
scope
(
{[Measures].[Costi1]}
,
{
[GoodMembers]
}
);
this= [DIM Mesi].[DIM Mesi].&[00]/3
;
freeze(this);
end scope;
scope
([Measures].[Costi1],
{
[DIM Mesi].[DIM Mesi].&[00]
}
);
this= 0;
end scope