Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Reporting Software for SQL server

Posted on 2014-11-13
Medium Priority
Last Modified: 2014-11-19
Greetings Experts,
I want to enable one of our staff members to use a reporting software to view information within our sql server. What is the best software out there to do this to enable a user to design reports and download data in a excel format for reporting purposes.

Kind regards. SJ
Question by:sunny-j
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 24

Accepted Solution

Phillip Burton earned 800 total points
ID: 40439477
The default reporting programme is SQL Server Reporting Services, either using Visual Studio 2010 (also known as BIDS) or, if you have SharePoint, you can use Report Builder if you have integrated it with SQL Server.

As for what is the "best" way, that is a subjective question. Crystal Reports is quite popular.
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 800 total points
ID: 40439483
This all depends on your requirements, your staff member's skills, and your budget. There are dozens of reporting tools out there, with varying degrees of complexity, functionality and costs.
The least technical and cost effective way is probably to run a stored procedure or database query directly from MS Excel using an ODBC connection. This just reads the data from the database and populates the Excel sheet. It's cheap (you already have all the tools, SQL server and Excel), but your formatting options are limited to what your SQL query can spit out and possibly Excel pivots. Still it's remarkably effective and we use it a lot in our organization.
Alternatively you can opt for a proper reporting tool, and for that there are many options. A few open source solutions which are potentially free are Jaspersoft and Pentaho. If you have the licenses already included in your MSSQL license you could also use SQL Reporting services. There is Crystal Reports (currently owned by SAP I think), Qlikview, Tableau, and a bunch more. All of these tools require you learn how to work with their designers (Reporting services uses Visual Studio, the others have their own design tools), and you possibly need further software and hardware to store report definitions, distribute the reports through your organization, etc.
If you're a small organization, and your data access needs are limited to a single individual, I'd recommend to first give the option of Excel a go. This assumes you don't want any slick looking reports but just want to have the data in a format in which you can play and manipulate it for analysis purposes (Pivot tables get the job done). Your staff member would have to know how to write SQL queries and possibly stored procedures but other than that there are little requirements.
If this is not enough, you'll have to look at alternative tools. Qlikview might be a good place to start for analysis. It doesn't require a data warehouse necessarily and also doesn't use much coding (mainly drag and drop). It's one of the cheaper solutions and by most considered user friendly. Tableau falls in the same category.
For more static reports (fixed layouts) Reporting services or Crystal reports might be an option, but they require considerably more infrastructure to make the reports available to others. I'm not very familiar with Jaspersoft or Pentaho but they might be able to do the same for less.
All in all this is a complex topic (Business intelligence) and requires some planning and thought. Our organization never thought this through properly and we're now stuck with 100's of unmanaged Excel reports as well as a reporting server setup, with a ton of duplication. It pays to look at this carefully even if your requirements now are still small. It can grow out of control if you don't implement the right processes and tools from the start.
Just my 2 cents worth.
LVL 12

Expert Comment

by:James Elliott
ID: 40439583
The ODBC => Excel option combined with Power Pivot is my favourite for quick deployment. I would also contest the view that you cannot make these look & feel 'slick'  ;)
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40439651
Hi James,

I agree, forgot about power pivot actually. And using excel 2010 and above with some slicers and pivot charts you can make some pretty things.
LVL 101

Assisted Solution

mlmcc earned 400 total points
ID: 40439910
You have been given a good list of options.

There are really 2 questions to answer
1.  What is the user doing with the data?
   If it is strictly viewing to answer questions then perhaps a SQL tool would suffice with a little training on how to build a query and filter.  
   If they really need reports for display on the screen or use as handouts in a meeting then a reporting tool may be the answer

2.  What skills does the user have and what skills are available in the organization?
   Most reporting tools are not overly end-user friendly.  For example, to use Crystal effectively requires a fair amount of training, a fair understanding of the database structure, and a good knowledge of SQL and query building mainly to understand how table joins work.

I agree with Koen, you need to think this through with the whole company in mind.  Once you give this capability to one user, others will want a similar capability.  Some of the tools are inexpensive while others require licenses for each user and require additional hardware or even their own server.  They may also require you to at least consider adding a report developer and may add additional workload to the system admin team.


Author Comment

ID: 40440208
Thank you all for your valuable comments. Yes, the user is very good with spreadsheets but unlikely to know much about SQL syntax so the suggestion that we could link the spreadsheet direct to the server may be very useful. The objective is to extract data and show certain findings so once we can get the raw data into csv format, we can pretty much work out what we need.
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440220
Sounds like creating a model in PowerPivot is your answer then.

Author Comment

ID: 40440414
Thanks Phillip, just discovered that our office/business versions of excel 2013 do not appear to support PowerPivot. Is that right? or is it possible to download the add-in. Kind regards.
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440436
Sadly, it is not just possible to download the add-in. It's very annoying.

I've come across this before - I've got a list of the various Excel flavours in my article "Mapping GPS photographs by time", which uses a similar tool (Power Map). The monthly subscription is about £10/month (about US$15/month) to Office365 ProPlus.

This Microsoft website confirms the versions shown in my article -
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40441751
Hmmm, that's very sneaky as it's not clearly mentioned anywhere that not all Excel 2013 versions support the Add-in. In fact, this link seems to suggest all versions do:

You don't mention which version of Office you guys are using but I'm guessing it's the Home & Business 2013 edition. A little more digging finds the following:

Power View and Power Pivot are available in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013.

As Phillip already mentioned, the Office 365 versions come with a monthly subscription so you can try it for a few months and if you don't like it, discontinue. But even buying a single Pro Plus license isn't going to break the bank, so it's still an interesting option to explore. I've only glanced at Power Pivot myself and never actually built any reports with it, but it's seriously powerful and can do a lot of things in the right hands. If you combine it with PowerQuery it gets even more interesting. Just make sure you allow your user enough time to learn how everything works. It's not overly complex but not as easy as a simple spreadsheet either, and depending on previous experience with data manipulation it can be a bit of a learning curve.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question