John Rowan (8/11/2015)
I would suggest that you consider using SSAS. Developing a cube for your data gives you the control over how the data is put together and how the metrics are calculated. Your users can connect to the cube via an Excel Pivot and have the same user experience that they are used to and prefer. You can also use this as a data source for your SSRS reports so that your reports give the same answers as your pivots assuming you apply the same filters.Another alternative would be using a PowerPivot gallery on SharePoint so that you can develop the PowerPivot docs and have a centralized place to store them on the network (in Sharpoint Gallery) so that the users have one place to go to get them.
Thanks,
not to familiar with SSAS and it maybe over-complicated for the whats needed just now, but ill do a bit of reading on it. PowerPivot sound much more appealing if it can control users to acceing only what they should be.