Hi,
I have a small problem and before trying to work through it myself I thought
I'd ask if anyone else has ever solved a similar problem and has a bit of
code I can pinch.

I have a cube that provides reporting on top of our timesheet capture
system. I have a dimension called Consultant with 1 level that contains the
name of each consultant in the company in the form "surname, forename" (e.g.
"Thomson, Jamie").
I want to implement custom security inside my cube role that only allows
each consultant to see their own data. I know that I need the MDX function
Username to do this. The Username function returns "domain\forename.surname"
(e.g. "int\jamie.thomson").

So, you can probably guess my request. Has anyone got any sample code that
dynamically alters the value returned from Username function into the correct
format? I know I'm going to have to use use VBA string functions to do this.
[Bear in mind that the domain name may not be 3 letters wrong so I can't
assume the name begins at character 5.]

Thanks in advance for any help anyone could give.

cheers
Jamie Thomson
http://www.conchango.com


P.S. So far I've come up with this in MDX Sample app:
select
{strtotuple("Consultant.[" + RIGHT(RIGHT(Username, 13), 7) + ", " +
LEFT(RIGHT(Username, 13), 5) + "]")} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]

but it only works for people whose Username is the same format as mine (i.e.
"int\jamie.thomson"). Like I say, it needs to be a bit more dynamic than this

What you are describing is called dynamic security. Send me an email and
I'll forward you a zip file contains a presentation that I gave at SQL PASS
last year, some sample instructions for configuing data, etc.

It lays out 3 possible approaches:
1) using member properties
2) using a security virtual cube
3) using a user-defined function

Enjoy.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jamie Thomson" <jamie.thomson (AT) donotspam (DOT) conchango.com> wrote

Me too...!

michaelvardinghus (AT) hotmail (DOT) com


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

If it is posible then i would also have one.

muhu (AT) danskebank (DOT) dk



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If it is posible then i would also have one.

hussain_mubeen (AT) hotmail (DOT) com
muhu (AT) danskebank (DOT) dk

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry bout the indiscretion but would you tell me which front-end you're
using and what amount of data is
involved in your current solution ?

/Michael Vardinghus (former Erp-consultant now DW-consultant - was involved
in developing an financial statement accountning system for
Danske Bank Asset Management Department - henol or niech can vouch for
me...)

"Mubeen Hussain" <hussain_mubeen (AT) hotmail (DOT) com> wrote