Geek Answers Handbook

End-user custom reporting tool: Microsoft SQL Server Management Studio or Microsoft Access?

Our centralized IT department offered two main special query tools for our common user base of approximately 200 employees:

  • Microsoft SQL Server Management Studio 2008 (SSMS)

  • Microsoft Access 2003

Environment

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

  • Scheme - 400+ tables; providing access to source data for our general staff would be a disaster.

  • We will create an “abstraction layer” over the raw data for our general staff to launch special requests.

  • The level of abstraction is likely to contain several types.

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

Which of the above tools (or alternatives) is best suited for a specific non-technical user base of about 200 people? What are the pros and cons of each?

In addition, the IT department offered to educate T-SQL people so that they could use SSMS. It is reasonable?

+4
source share
9 answers

Your “abstraction layer” is the right approach for accessing Access. Create an MDB with basic views associated with it and distributed by users. Allow them to create new queries and reports in their own MDB as needed.

Now, how are you going to stop them from Cartesian joining on tables with millions of records or more, I'm not quite sure.

+5
source

How about this? i-net Clear Reports (commonly called i-net Crystal-Clear) has a powerful ad-hoc that is easy to use for non-technical users. Your users should not know anything about messages at all. They simply choose the type of report, data and voila - a report that meets the needs.

Data abstraction can be easily accomplished by creating so-called data representations that can be designed, for example. your administration. There are various ways to access the ad hoc GUI. We have a web GUI, a Java applet, or a stand-alone Java program.

  • End users will not need any training, as the GUI is very intuitive.
  • Views can be easily created by dragging and dropping in addition to customizing data types, formats, etc.
  • All reports (depending on security settings) can be accessed via DAV in our gui report repository.
  • The server supports different security settings for each user or group.
  • The standalone report designer is free and fully functional.

Disclosure: Yes. I work for the company that built it.

+6
source

Microsoft has a free tool for business and end users called Report Builder. It supports all the features of Reporting Services SQL Server. The good thing is the appearance of the Microsoft Office user interface.

You can download the latest version of “Report Builder 3.0” here http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=6116

And for more information about MS Report Builder check this link http://technet.microsoft.com/en-us/library/dd207008.aspx

+3
source

An attempt to teach non-technical T-SQL people to request a schema with 400 + tables will probably not succeed, unless they are limited to querying just for the views, and their views hide all the ugly complexities of various joins, groups, etc.

Our company was in a similar situation when Access was used at an early stage, and then we switched everyone to use T-SQL and SSMS. IMO, this is the approach you would like to take.

Again, the success of this will depend on the quality of your views or, even better, the reports that you provide to your end users.

Randy

+2
source

I would look at more than Stonefieldquery.com , which is intended for non-developers to create reports. Not that the report creator or query builder in Access is bad, but it can be too much. I think they also provide a way to centralize reports and queries where they can be shared. Many people will not be able to open one access file and create a report (I think that the construction of queries is in order.).

Most of them will use the drag and drop capabilities, but about 5-10% will bring the need for SQL, and then you can take advantage of the “learning moment” and get them some training.

+1
source

Deficiencies for access, of course, will cost; SSMS should be free if you are properly licensed for SQL Server.

Depending on real needs, some users may be better off with Crystal Reports (never thought I said so) or Reporting Services.

+1
source

you can create a series of sql server analytic cubes and configure users to use excel so that they can use excel summary tables.

+1
source

Being a newbie in special reporting and independently performing my work, I used Izenda.com special reporting. It was very straightforward, and I could do it myself compared to outsourcing.

+1
source

Check SQLS * Plus - http://www.sqlsplus.com

I found that SQLS * Plus is a very effective command-line tool for SQL Server - it is a free tool (for personal use) and allows you to create reports with headers, headers in HTML and CSV formats, format columns in custom masks, set the report length, size pages etc. As I understand it, it is very similar to the very famous Oracle SQL * Plus reporting tool

0
source

More articles:


All Articles