Best Practices for Excel Pivot Tables
The benefit of a direct-connected pivot table for the SQL Admin/Developer is: you no longer have to write and constantly tweak reports! Just give your users permission to run a stored procedure, and they can satisfy all their reporting needs through self-service.
Benefits for your business user: they can always drill down to the detail, be confident that it was not changed after retrieval from the database, and change parameters and refresh whenever they want, even after rearranging the report.
It requires no client software besides Excel, and has worked similarly since Excel 97. Nothing is required on the server except any version of SQL. (You can also connect to any data source with an ODBC driver!)
Although the concept is straightforward, there are some stumbling blocks to avoid. I will cover all the important points for setting up this solution, including:
1) Making the Connection
2) Configuring and Arranging the Table
3) Allowing Users to Change Parameters