Solved

Reporting Software for SQL server

Posted on 2014-11-13
10
202 Views
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
0
Comment
Question by:sunny-j
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 200 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.
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 200 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.
0
 
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'  ;)
0
 
LVL 12

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.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 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.

mlmcc
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:sunny-j
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.
0
 
LVL 24

Expert Comment

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

Author Comment

by:sunny-j
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.
0
 
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 - https://support.office.com/client/Start-Power-Pivot-in-Microsoft-Excel-2013-addin-a891a66d-36e3-43fc-81e8-fc4798f39ea8
0
 
LVL 12

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:

http://social.technet.microsoft.com/wiki/contents/articles/14224.powerpivot-version-compatibility.aspx

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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now