SQL Server 2005 Reporting Services - Pros and Cons

Question

I am developing a web application using ASP .NET 2.0, VS 2008 and SQL Server 2005. I would like to Use SSRS 2005 for the various reports I need to build for this web application. I would like to convince the team that we should adopt SSRS as the main reporting platform for most internal and external web applications we have.

What are the pros and cons of Reporting Services? I can see many pros like tight integration with IIS, SQL Server and Visual Studio, rich presentation features and export functionality, subscription etc so I am mainly interested in negatives of SSRS.

EDIT: I understand that if I am not using VS 2005 for my application development, I will be using different Visual Studio versions for application and report development. However, I am more interested in negatives (or not so good aspects) of SSRS itself.

Answer 1

I know you said 2005, but I will put in notes around 2008 as well.

SRS Pros:
- It is free (provided you have the SQL server license)
- Tight data integration with SQL Server, but it handles anything .NET can (Oracle, ODBC etc...) just fine. (2008 has native support for Terradata too)
- Components for Visual Studio, SharePoint and PerformancePoint all exist to make it easy to leverage it. It is just a web app though so integration into any web page or app that can talk to a web server is easy too.
- Built in tools to do subscriptions (i.e. emails that get sent out on a regular basis to a list of people with the report on them). The list of recipients can be static people or a sharepoint site or a dynamic list of people (pulled from a DB) (08 adds support for dynamic to sharepoint too)
- 3rd party vendors exist to enhance the product
- Export to a variety of formats (XML, CSV, Excel, PDF etc...)
- Ability to design templates which power users can use to build reports without knowing SQL (since the SQL is contained in the template). Power users use a special report builder tool which is delivered via click once.
- Works differently to Crystal reports (I don't like Crystal thats why this is a pro for me)

SRS Cons:
- Charting controls look like Excel 2003 and are limited. (2008 has the Dundas controls in by default so they are much more powerful, more varied and better looking)
- Kerberos issues due to it being a web app can cause annoying problems (2008 removes that as it is no longer an IIS web app. It runs it's own web server based off the IIS core but is closer to a stand alone app - so the security issues aren't a problem)
- Designer support is a pain. 2000 Reports must be developed in VS 2003, 2005 reports must be developed in VS 2005, 2008 reports must be developed in VS 2008. By Visual Studio I mean the normal one or the thin downed version you get with the SQL Management tools.
- Compatibility. Each version of reporting services can run only the current version and one version back of the reports.
- Security is limited to Integrated Windows or Anonymous (2008 has added support for forms based security and for custom providers, like you get with ASP.NET)

Answer 2

One of the Cons I see with your setup, is you will have to use Visual Studio 2005 for your reporting project since you are using SSRS 2005.

Since it looks like you are using Visual Studio 2008 for your other development, this means having both versions installed and having to have both open and running most the time.

I'm in the same situation and it is a hassle, but one I've gotten used to.

EDIT:
Some of the other Cons I've run across are usually designer related. They may have been fixed in 2008 (don't know for sure), but I attribute them to the infancy of SSRS compared to other more mature reporting solutions.

• Datasets changing to Text even though you set them to Stored Procedure every time you enter the data tab

• Web Service datasets losing their parameters when changing the query

• The expression editor is very dumbed-down. Its slightly better than using notepad.

Answer 3

The CONS:

1. Rendering might be different in Firefox or other browsers. When using SSRS ReportViewer on an ASP.NET page, just make sure to verify the look/feel/layout of the report when rendered on browsers other than IE.

2. One con is that with SSRS, there are so many options available to the developer that it could be confusing at first. I am talking more about whether to use Local Reports or Server Reports, whether to put code inside code modules, in reusable assemblies, or use reusable Managed Code (C#) Stored Procedures.

3. The biggest con I could think of in SSRS is that the code module is very basic. No intellisense and no debugging features. Also it would be great if SSRS scripting supported C# instead of just VB.NET.

4. While expressions are great, the problem is there is no single container/place which allows you to see all the expressions defined in a report. This could present a maintenance nightmare down the road.

That said, SSRS is a powerful tool in the hands of a seasoned developer.

Answer 4

Pros:

• Free
• Probably better tied to MS SQL than most others
• Works well for most types of reporting

Cons:

• Free; Support access isn't like Crystal
• Doesn't have every feature other, older competitors have. Being a newer piece of software, it is still getting new functionality that other products like Crystal Reports has had for years.

I often end up running reports in whatever way they're easiest. Lately I built my own web based interface that drives any combination of PDF/HTML/Crystal/SRSS reports and delivers them. Often Crystal will do some in 2 steps, or SRSS does something else better. If I had to pick one right now as a go-to for all scenarios I'd probably pick Crystal. The more I use SRSS being a few years old and still in active feature development (catching up to products like Crystal), I can see it being used a lot more... not quite yet though.

Answer 5

I find SSRS is very Robust and provides a very large range of capabilities to suit your reporting needs. I have tried others (Crystal) and did not like it nearly as much. (This could just be personal taste though).

Even for a beginner, SSRS has many wizards which will get you the results you desire and for the experienced developer, you can fine tune your reports with drill-throughs, colors, coding, etc.

I honestly have nothing bad to say about SSRS. The one downside that Dustin pointed out is that your VS version and SQL version are different. I have an app that is in VS2005, but uses a SQL 2000 backend and I have to have my reports separate in a VS2003 project to get them to work properly.

Answer 6

In order to better provide an analysis, it would be easier to if we new what alternative you are considering. For example, if it's SSRS vs nothing then the argument is simple, likewise SSRS vs bespoke reports (eg. ASP.NET). However, if you have other reporting tools under consideration, e.g. Crystal then we can be a bit more critical. Also, what kind of reports are you planning, and what features do you ideally need.

Regardless, I'd largely agree with the other posters. SSRS is free & very capable. Other systems might have extra bells & whistles, but would these be issues that mattered, and are prepared for the costs?

I suppose, given that SSRS is free and easy enough to setup, my suggestion would be to run a trial; you'll probably be able to make your own mind up through a bit of experimentation - you've got nothing to lose!

Answer 7

SSRS is amazingly simple to use. We've had interns writing reports the first day on the job with SSRS, and doing a good job at in.

That simplicity goes a long way with developers who just want to get reports done quick so they can get back to actual coding.

Answer 8

Correct me if i'm wrong but SSRS does not use Forms Authentication. For viewing reports from a web app using the built-in components will use what ever authentication is used for the application. However for subscription and using the Report Builder application (click-once) which requires access to the Report Manager its windows authentication.

[Edit] From Robert MacLean - MSSQL 2008 does support Forms Authentication, which is a good thing.

Answer 9

The main problem I had was setting up Reportserver in a stage/production web-environment. I had to get the reports out of the Reportserver Webservice into the web application. The environment looks pretty simple now, but I remember at that time it was difficult to debug.

An other issue was multilanguage reports. Again: now its easy to use, but be prepared to do some googling.

The designer is crap. Who knows what =First(Fields!Sales.Value,"DataSet1") means? I also had issues with the size of reports (the report was designed to fit on a A4 page but at the end didnt).

I still like the product. After some time I got everything solved and our trainee can design and publish great reports.

Answer 10

SSRS 2005 Pro integrated in Sharepoint Cons integration in Sharepoint could be a mess to set up using Kerberos etc...

Answer 11

I will only put some cons here as most people have already mentioned the benefits.

SSRS 2008: Primitive user interface little flexibility to do fancy stuff i.e. flashy charts, geo maps (w/o lat long) user interaction is only limited to applying filters, rest is pretty static

MS seriously has to re-think what competitors are doing in the field of BI & Data visualisation.