Pivot Tables v Reports

Question

Pivot Tables v Reports

ps_vbdev

SSCarpal Tunnel

Points: 4060
More actions
August 11, 2015 at 7:46 am

#301832

When i get requests in from departments regarding MI i always try to deliver using ssrs. There is an old school in the company that insist on using excel pivot tables. I try to avoid this but when im overruled i do nightly extracts to an archive database using ssis. Then i build the pivots on this archived data and let them manipulate them as the see fit. As expected, users are passing copies of the sheets around departments, so I have no control on who is using what.
I was just wondering if there is anything else out there that would give the flexabilty of pivot tables but the control of ssrs?
(I couldn't make up my mind if this post should be here or in the reports section)
thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

ps_vbdev (8/11/2015)
When i get requests in from departments regarding MI i always try to deliver using ssrs. There is an old school in the company that insist on using excel pivot tables. I try to avoid this but when im overruled i do nightly extracts to an archive database using ssis. Then i build the pivots on this archived data and let them manipulate them as the see fit. As expected, users are passing copies of the sheets around departments, so I have no control on who is using what.
I was just wondering if there is anything else out there that would give the flexabilty of pivot tables but the control of ssrs?
(I couldn't make up my mind if this post should be here or in the reports section)
thanks.

Here is fine.

You're not going to be able to change the culture of the company in this area. Your time might be better spent helping the users create a more central repository of such spreadsheets to ensure that they get backed up on a regular basis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Rowan SSC Guru Points: 56440 More actions · Answer 2

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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ps_vbdev SSCarpal Tunnel Points: 4060 More actions · Answer 3

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.