We help IT Professionals succeed at work.

dynamic reporting tools

Experts,

I've been tasked in developing a report or a utility that gives the ability for users to query our CMS database for any filters they need. we use SSRS as our reporting tool right now, but I don't think this would be an adequate solution for this type of report. I've looked into power pivot but also don't think that would work considering the amount of data we currently have.

Can anyone help with any suggestions of the best way to approach this project?

Thanks in advance,
Comment
Watch Question

SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>we use SSRS as our reporting tool right now
>developing a report or a utility that gives the ability for users to query our CMS database for any filters they need
Correct, SSRS is the 'give users exactly what a developer specifies' end of the spectrum, and doesn't serve the tinkerers of the world that want to do their own adhoc querying.

>I've looked into power pivot but also don't think that would work considering the amount of data we currently have.
Correct, but afaik there's no real 'lowest common demoninator level' query tool where users can do adhoc querying, unless you work out a deal with your DBA's to give them read-only access, and either let them have at it or create a datamart for their use.

Have you considered creating views that summarize the data in the SSRS reports, then connect Power Query / Power Pivot / Power Whatever to those views?  OR connect the Stored Procs that serve as the SSRS reports' data sources to Power Query.   If it helps I have an article called Microsoft Excel & SQL Server:  Self service BI to give users the data they want on how to pull that off.

Author

Commented:
excellent article Jim Horn. this is just the sort of guidance I was looking for.

Thanks for the quick response.