When developing pivot table reports, you are most likely working with the iqbs administrator account or equivalent. Whenever it comes to testing for a specific user, you would need that user to log in and test it for you.
There’s an easy way to impersonate for that user: Just add the following extension to the pivot tables’ connection string:
EffectiveUserName=DOMAIN\AccountName
A full connection string would look like:
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=iqbs Finance;Data Source=IQBS_SERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;LocaleIdentifier=1033;EffectiveUserName=DOMAIN\AccountName
The connection string can be found in Excel (2007 and up) under Data, Connections, choose the proper connection, click connection properties:
Note: To use this property, the caller (you) must have administrative permissions in Analysis Services.
You can find more on SSAS / cube connection strings in this post on Microsoft MSDN.
You can find the format of any connection string at www.connectionstrings.com.
