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

sql-server ms-access reporting adhoc
iokevins picture iokevins · Dec 4, 2009 · Viewed 16.2k times · Source

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

gamma picture gamma · Jun 12, 2010

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.

Related questions

MS Access call SQL Server stored procedure

I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code or query I work with those tables very simple, I access them via name, like [Customers]. Also I have a …

Read More
How do I create a table alias in MySQL

I am migrating an MS Access application (which has linked tables to a MSSQL Server) to MySQL. As a means to overcome some MSAccess table naming problems, I am seeking a solution to add a MySQL table alias that will …

Read More
Editing Record issues in Access / SQL (Write Conflict)

a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another …

Read More