Working with a client with Excel Pivot tables with a live data connection back to Dynamics GP, and had a couple of questions:

1. Is it possible to tighten down security on a pivot table such that the connection refreshes with live data without allowing the user to modify anything other than the report filters?
2. Is there a way to apply formatting to the total lines?

Appreciate any feedback!

Hi
You need to remove the lock flag from the Pivot Table Header cells.
The select Tools -> Protection and make sure the flag for "Use Pivot Table Reports" is ticked. Then click OK.
This will lock all data cells and only allow the user to change the filters.
Good luck.
Tony(OTPM)

Thanks Tony!

I probably should have mentioned that we have the pivot table set to not store data with the sheet and to refresh the data when it is opened.

With the protection enabled as you suggested, we get messages when we open the sheet:

"That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data."

It then suggests we remove protection, and indicates that it failed to refresh (although data does display). We only have one sheet and one pivot table.