Adhoc reporting using PowerPivot for Sharepoint 2010

Question

i want to use Power Pivot/Excel for adhoc reporting on the web for both internal (Domain) as well as external (Non Domain) users.

So i have created a excel file in power pivot gallery using connection (SSAS connection) from ODC file.

Now, as per my r & d we can't fetch permission based data in the web browser in excel using Sql server analysis services as source. Also, SSAS works with windows authentication only but my requirement is to fetch permission based data for both Windows and non-windows users.

So, i am using unattended service account to fetch the data from SSAS. And to fetch permission based data i want to pass current login id to customdata() a attribute in connection string.

So, Anyone please tell me how can i pass the value of sharepoint current logged in user( windows or FBA) to customdata() in connection string ?

Or if you have some other approach for my requirement please suggest me ?

Great Thanks,

---

Neelam Gupta

Answer 1

i want to use Power Pivot/Excel for adhoc reporting on the web for both internal (Domain) as well as external (Non Domain) users.

So i have created a excel file in power pivot gallery using connection (SSAS connection) from ODC file.

Now, as per my r & d we can't fetch permission based data in the web browser in excel using Sql server analysis services as source. Also, SSAS works with windows authentication only but my requirement is to fetch permission based data for both Windows and non-windows users.

So, i am using unattended service account to fetch the data from SSAS. And to fetch permission based data i want to pass current login id to customdata() a attribute in connection string.

So, Anyone please tell me how can i pass the value of sharepoint current logged in user( windows or FBA)  to customdata() in connection string ?

Or if you have some other approach for my requirement please suggest me ?

Great Thanks, 

---

Neelam Gupta

Answer 2

any chance you have worked this out yet? i have the same / similar challenge...

Answer 3

No, Actually powerpivot is not for adhoc reporting because when we creates a model then its showing all the Dimensions and facts as a query and not showing any relationship b/w the facts and dimensions.

---

Neelam Gupta