Hi,

I need to create a pivot table with chart for a summary report , I don't have  an issue with that; my problem is I don't want show the connection string which  include the database password.

I tried to create a ODBC on the server machine to be shared for all users but when a user download the excel from the shared location on server and refreshes the pivot it gives an error because the DSN is not exists on the local machine. so I have to export and import the connection for all the users which I think is not the best way .

so what is the best practice to share an excel with pivot table ??

Thanks

As far as I know, we cant  hide the connection string. As a work around, you can create a user in SQL Server and give limited permission to that user, then use that user to set the pivot table. Also when creating excel pivot table from SQL Server ,you can use Windows Authentication to connect SQL server:

http://msdn.microsoft.com/en-us/library/dd787978.aspx