Reporting Software for SQL server

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
James ElliottManaging DirectorCommented:
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'  ;)
Put Your Flow Data to Work

SolarWinds® Flow Tool Bundle combines three easy-to-download, easy-to-use flow analysis tools that can help you quickly distribute, test, and configure your flow traffic.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
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.

sunny-jAuthor Commented:
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.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Sounds like creating a model in PowerPivot is your answer then.
sunny-jAuthor Commented:
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.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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 -
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.