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,
telliot79Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
telliot79Author Commented:
excellent article Jim Horn. this is just the sort of guidance I was looking for.

Thanks for the quick response.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.