MDX for current user

Question

Just trying to work out the mdx for filtering by current logged on user.

SELECT
{ [Measures].[Contribution] }
ON COLUMNS,

{ [IFA].[Peak Tier].[Peak Tier].ALLMEMBERS }
ON ROWS

FROM [QAP Sales]

WHERE ( [Account Manager].[Account Manager].[All] )

Gives me a 'base' selection, now rather than all account managers, I just want the current logged in user. I think MID( Username, InStr (Username, "\") +1) gives my what I'm after, in terms of the user's name that will match with the acount manager's name in the relevant dimension, but I'm not sure how to apply it in the MDX.

Eventually I'm using this in PerformancePoint, but as a first step I guess I need to understand the actual query that will give me what I want.

Answer 1

If you have the user name in the dimension then you will be able to filter on it using the Username function in the MDX. If not then you will need a User dimension with the usner names tied to the dimension with a bridge table. Here is an articel on how to do that:

http://www.bidn.com/articles/ssas-administration/134/analysis-services-dynamic-security

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 2

Thanks Mike, yes, as I mentioned above I think I can use the Username function in MDX combined with the MID statement above to strip out what I need...I'm just not sure how to apply it in the MDX.

Answer 3

You can use the Filter() to create a calculated member of only those values retired by the UserName()

This should work.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 4

Hi DeanB61,

 You can use Filter() function in MDX to filter by current logged user, please take the following thread as a example,
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/cf06de2b-683b-4208-9682-a13f5e7ac2cd

For more information about Filter (MDX), please refer to:
http://msdn.microsoft.com/en-us/library/ms146037.aspx

Thanks,
Eileen