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.