End User Ad-Hoc Reporting Tool: Microsoft SQL Server Management Studio or Microsoft Access?

Question

Our centralized IT department has suggested two primary ad hoc query tools for our general user base of approximately 200 staff members:

1. Microsoft SQL Server Management Studio 2008 (SSMS)

2. Microsoft Access 2003

Environment

• The backend database is a read-only Microsoft SQL Server 2005 database.

• The schema is 400+ tables; allowing access to the raw data for our general staff would be a disaster.

• We will be building an "abstraction layer" over the raw data for our general staff to run ad hoc queries against.

• The abstraction layer will most likely contain a number of views.

• A number of users have basic knowledge in Microsoft Access; none have used SSMS.

Which of the above tools (or alternative) would be best for a decidedly non-techie user base of approximately 200 people? What are the pros and cons of each?

Also, the IT department has suggested teaching people T-SQL so they may use SSMS. Is this reasonable?

Answer 1

How about this one? i-net Clear Reports (used to be called i-net Crystal-Clear) has a powerful ad-hoc reporting component that is made to be an easy-to-use thing for non-technical users. Your users won't have to know anything about reporting at all. They simply select the kind of report, the data et voila there is a report suiting the needs.

The data abstraction can be done easily by creating so called data-views which can be designed by e.g. your administration. There are various ways to access the ad hoc reporting GUI. We have a web GUI, a Java Applet or a standalone Java program.

• The end users will not need any training since the GUI is highly intuitive.
• The views can easily be build by drag and drop in addition to setting datatypes, formats and so on.
• All reports (depending on security settings) can be accessed via DAV our a report repository gui.
• The server supports different security settings on a per user or per group basis.
• The standalone report designer is free and fully functional.

Disclosure: Yep. I work for the company who built this.