I'm doing a POC, generating graphs & pivot tables using PowerPivot 2010 for SharePoint.
My requirements are,
Getting user specific data when the user logs in to the SharePoint environment.
If the above option is not possible, I should be able to filter the data using a user specific flag - can be a role/username/bit value.
The SharePoint may be hosted using Claims authentication.
Services are basically REST services which are protected by a cookie based authentication.
I understand the following are the constraints in using PowerPivot for SharePoint.
PowerPivot can refresh the data and store the it in the Excel workbook using a credentials configured either in the ConnectionString, connection manager or SSS ID. Hence points 1 (above) is not feasible.
I'm unable get the user name of the current user in PowerPivot. This can be done using VBA or VSTO - but hosting VBA is not feasible in SharePoint.
SharePoint should use Windows Classic authentication.
Is it possible to write a wrapper around the PowerPivot / Excel services to bring the
data on the fly from a data source? This will allow me to retrieve to any sort of services/databases.
If the above are shortcomings of PowerPivot 2010, are they being addressed in PowerPivot 2010 (with SQL 2012)?
Appreciate any help/direction on the implementation.