SSRS Ad Hoc Reports

Question

I want an ad hoc solution for creating simple reports, most of which will be exported and subsequently munged around with in Excel. I must say I was slightly underwhelmed having played with the SSRS ad hoc report builder, but I'm open-minded. Did anyone use this as a production solution to allow manager-types to create some of their own reports? Would you say it was:

•     Better Than Sliced Bread?
•     A Good experience?
•     A Bad experience?
•     Evil incarnate?

Enquiring minds want to know.
 

Answer 1

MSRS is not an ad-hoc tool, so it shouldn't be approached as such. MSRS is generally used for creating quality, production-ready reports. We can take advantage of it's more advanced features and capabilities, you should have an IS background (hence why you develop reports in VS), so keep VS away from manager types. We could give reason why manager types are doing ad-hoc reports, but that's another forum.

You can achieve "some" of what you're looking for using MSRS, due to the flexible nature of its query engine. Addition details were not posted by you, such as what data source you're using.

I've found an approach that delivers limited ad-hoc:

• You'll have to map out a number of ad-hoc query scenarios, dependant on the data source. These scenarios will for the basis fo the "template reports". Let's discuss an example: If you are using SQL Server, you'll need to consider the number of join scenarios. If you are using MSAS, you may have a number of "filter" scenarios.
• Define some "Meta information" and store it in a table to be used for parameter selections. Let's discuss an example: Meta-information has been defined by me about MSAS cube that controlled selections for rows, columns, and filters and stored it in a table in the ReportServer database. Seemed like a logical place.
•  Different display formats like tables, matrix, etc. need to map out that you need to use/support. In my case, I have standardized on the matrix layout with some rigid rules on what would be supported.

It would not be an elegant solution, and certainly not recommended as a reporting strategy. But it would allow someone to get the number they were looking for.

It would be much better to focus on ad-hoc tools for ad-hoc and reporting tools for production. Different requirements and expectations.

You can enroll for SSRS Certification Training from Intellipaat to master the concepts of SQL Server Reporting Services