I was responding to a recent CodeProject forum question and my response got so long and detailed I just thought I’d blog it instead.
If you haven’t been by CodeProject check it out. It’s a great site with an incredible wealth of very useful information in the articles and forum communities.
Anyway, the topic in question was this:
http://www.codeproject.com/Lounge.aspx?msg=2828212#xx2828212xx
“I’ve been tasked with researching some alternatives to SQL Server Reporting Services and was wondering what experiences and recommendations anyone out there has?
I’ve also seen a couple of add-ons for reporting services, some of which look quite promising – has anyone got any experience of these?
PS Ideally not Crystal Reports – I had to use this in an old job and *hated* it!!”
I’ve used various reporting frameworks as a developer and end user in various projects for the past 7 years. While I am not up on the extremely current (such as developing in CR XI or SSRS 2008 yet) I hope this information will prove useful. If you have any corrections or suggestions you would like to make please chime in!
As a side note my organization will be installing SQL 2008 (waiting for a couple of months now for our purchasing process to get the license through) and I’ll have a chance to really play with SSRS 2008. I’ll post an update at that time.
Another side note; this is strictly a comparison of reporting, not business intelligence.
Another side note (how many of these can I have? :)). There are some very good open source frameworks (such as Jasper for Java) that I have not had the opportunity to use. Commercial products like those listed here are not the only alternative. Plus, as several users have stated in the forum, rolling your own may be well suited to your project if you have very customized requirements, have a small reporting needs or restrictions on funds or use of open source limit your options.
Now, on to the comparison!
In several projects over the last 7 years I’ve used Crystal Reports (v8.5 and some limited XI), Active Reports (v3) and SSRS (v2005). All have their pros and cons, however, they are in completely different areas. Note that there are now newer versions of each product so I can’t speak for new features. These also were the base products. No 3rd party controls or such were used.
Here’s my very brief summary:
CR: Best designer and overall most professional output of static reports.
AR: Best tool from a strictly development perspective and most friendly to integration.
SSRS: Free, fairly decent but still an “early” product.
All three had their definite "quirks" (i.e. bugs, hacks, required massaging, etc).
Here’s my take in different areas:
Report Design (manual):
CR had the best designer I have seen. Incredibly easy to use and I could create very rich and professional looking reports in a matter of minutes. CR actually replaced my quick and dirty T-SQL result printouts when a supervisor asked for some quick numbers. I could do a very professional layout in 2 minutes ready for use in a meeting rather than the dry text print of a result screen. However, extreme dilligence must be placed on good design. It is very easy to scatter formatting and calculation code all over the place. It can quickly become a mess of spaghetti and make debugging a report almost impossible. If you exercise good design such as using base functions that are called by other functions, basing formatting of functions rather than inline code, using well documented code or good function/variable names then this can be mitigated. XI comes with a new function and formatting explorer that really made hunting for customizations very easy. This was a real “pain” in 8.5.
Active reports had no real designer other than a VB form like designer built into .Net. I believe they have upgraded this in newer versions. With AR I found that it was often easier (and more accurate) to edit the design in code. Definitely not a rapid report development tool. However, I did find that of the three this tended to produce the most accurate results since I could almost hand code it myself. As an example images were exactly where I placed them and with the exact right dimensions the first time. That being said getting a very professional looking report really depended on your code/design skills there wasn’t really any help from AR in this department.
SSRS’s designer leaves a lot to be desired. It is very rigid, has no real-time editing, and overall lacks a lot of the usability features of CR. I could design decent reports much faster than AR, but it was difficult (if even possible) to get the same professional output of CR. I believe this has been somewhat upgraded in 2008, but I’m not sure to what degree.
Advanced Design (i.e. functions, custom code, etc):
CR lets you create functions for almost any UI area of the report and this could be coded in a VBScript like syntax or their own. I say UI area because there really was no control over the various events or rendering loops. You could really only alter cell values, do running functions or change formatting. That being said in the 8.5 version not all UI elements could be customized on a granular level. For instance, you may be able to bold or hide something with a function but then couldn’t change the font or size. (Don’t quote me verbatim on this. It’s just a example and my memory may not be accurate as to the actual specific limitations)
AR is pretty much a wide open playground for developers. I love the amount of control you have. It is pretty much entirely built with integration from the ground up. Almost anything you’d ever want to customize you have full ability (and the .Net language) at your disposal.
SSRS is a love/hate situation. You can use custom code in three different ways. You can enter “expressions” almost anywhere you would want to manipulate data or formatting. This is all in a VB like syntax so C# developers may not like that. Plus, expressions are written in a single line so if you want do do anything other than simple manipulations or conditionals expressions won’t work. Another con with expressions is much like CR pre vXI days, it’s very easy to “lose” a function because there is no one explorer tool like in CR XI that shows you all the different expressions that are embedded into the report.
A step above expressions is code embedded in the document. Here you can create full fledged functions that are callable by the expressions, which is a preferred method if you are doing the same thing more than once. However, in v2005 the embedded code has no designer. It literally is a textbox with all the code at the same time. There is no syntax highlighting, no IntelliSense and no debugging. You simply run the report and if it doesn’t successfully execute you try and decipher the exceptions thrown.
Beyond that you can write your own code in C# (or whatever .Net language you choose), compile these into an assembly and then reference them in the report. This is great because you get the full robust quality of the CLR and Visual Studio to create your functions. However, this does slow down the development process as you’re now working in several different places and (at least with VS2005) each time you made a change to the assembly you had to close the reporting solution and re-open it for VS to reference the updated assembly dll.
Report Design (dynamically generated (even partially) within code):
Sometimes people want to be able to generate reports from scratch or even partially depending on the use of the application. For instance, if you have a billing department, you may have several different reporting requirements based on the type of bill, who the bill-to party is, etc. In this case you may have a base report template with a header and footer and on the fly add different “parts’ to generate the completed report. This would be beyond static reports but not ad-hoc in the sense that the user is creating their own report. I don’t really have experience in this area but I’ll state what I believe to be true. Feel free to correct me.
I don’t believe that CR has any ability to do this what-so-ever. Correct me if they have a new feature or product out by my experience is you either use a static report or a completely user-driven ad-hoc report.
I really don’t know if AR has this ability. The reports are ultimately stored in XML (I believe) and it’s completely development driven so I would almost assume you could do this. However, because the reports are really .Net objects you get full capability such as inheritance. In v2 we created a template “base” report that had a header and footer. Then we simply created several different static reports that were derived from the base report. Almost like a master page scenario in ASP.Net. The designer, however, didn’t show the master page elements and didn’t give any indication you were using a derived report class so you just had to remember things like not to make use of page headers or footers. In v3 they broke this ability. I created a support issue on their bug tracker but it hadn’t been resolved when the project was finished a couple of years ago. I’m sure they’ve fixed this in the current version. It was handy though!
The above solution is not quite the same as using sub-reports. With sub-reports you create the base reoprts and include any sub-reports. A sub-report may be reused several times. In the above solution I created the base report and used that several times, which you can’t do in the sub-report scenario.
SSRS is based on XML, so theoretically you can design a complete report in code at run-time, however, I’ve yet to hear of anyone doing this in a practical sense.
Ad Hoc Report Design (for end users):
CR: There was no ad-hoc ability in CR 8.5 other than putting an actual designer control in your windows forms app. If you were selling the app I believe there were licensing restrictions around this and often software vendors had to sell a fully licensed copy of CR with their product, adding another $500 to the bill just for the ad-hoc ability. Business Objects may have relaxed this in the later versions.
I have used the Business Objects InfoView XI R2 web based ad-hoc system as an end user. It may be just the way the vendor set it up but in my opinion this is a terrible interface. It is very clunky and constantly returns useless errors if a field no longer exists on a database, if a function is syntactically incorrect, etc. A very un-user friendly product for non-technical users. In this day and age with web-based designers from almost anything such as designing a scrapbook like ShutterFly or Google Maps mashups, BO’s interface is a far cry from a well designed UI. As a web developer myself I would have to state that this mostly is a lack of a good web 2.0 or Java development staff. They use Java applets. While Java applets (and UI in general) have gotten a bad rap there are some very good designs that show it is possible. There is no real fundamental difference between what an applet vs a full blown app can do regarding UI. If you have ever used IntelliJ (from JetBrains, the makers of ReSharper) or Eclipse (yes they fudge on their UI rendering and it’s not really Java that finally draws the screen) then you’ve seen some outstanding interfaces. I’ve seen some incredible Java applet games (even in 3D 7 years ago) if you think Applets are different. There’s no reason why BO couldn’t do the same if they had developers with the proper skills.
Active Reports also gives ad-hoc ability in the sense of putting a designer control in the Windows Forms app. however, unlike Business Objects there is no licensing fee to to this. This is largely one of the biggest draws to AR. As a developer you can purchase it and use it in all your apps without having to worry about licensing issues with your customers.
AR has no web designer, at least not as of v3.
SSRS: Comes with Report Builder. It is a fairly easy to use system but may be confusing the way they "navigate" relationships. All in all it’s probably the easiest ad-hoc system I’ve seen, however, v2005 offers only the most basic functionality. For instance you cannot conditionally format text. A very promising first release and I’m excited to see where they take this.
As a side note, while SSRS 2005 is free in the SQL Server Standard edition, they do leave some cool features for the Enterprise edition. For instance, SSRS can dynamically generate views of a table showing field values, aggregates for foreign-key relationships and links to foreign key tables. As an example, if you have a database of customers, orders, products, etc, you could view an order, navigate to the items, navigate to the base item, navigate to inventory, navigate to the invoices, navigate to the vendors, and on and on, without any development. If you wanted a customized view of a particular table you simply create it and reference it. Then SSRS will dynamically generate the table views as normal but use your definition when viewing that particular table. This is a great way to “drill-down” into an app quickly when you need to check on the data rather than diving into the Query Analyzer. But, alas, this is only offered in SQL Enterprise, <sigh>.
UI Controls:
CR has fairly decent UI controls. Lines, boxes and other shapes worked fairly well if you navigate around the rendering quirks (like page breaks through shapes, etc). Their charts were fairly decent but did not have near the granularity of something like Excel.
I didn’t use the AR controls on the projects we used it on so I can’t really speak to this.
SSRS has very basic user controls such as shapes, images and charts. I haven’t really used these other than the image control. It worked as expected.
I will say that the chart control had some definite quirks in Report Builder. When demonstrating RB to a group of co-workers we built a chart, viewed it, changed some formatting, viewed the changes, etc. We eventually got to a point where I asked it to chart an invalid sum or result. RB complained as expected, but we couldn’t simply change the values back to their valid settings. It somehow remembered and said “Nope. I don’t care what changes you make I’m not showing this chart again!” The only way to get rid of the error was to drop the chart all together and re-create it.
All three packages have 3rd party vendors who sell very nice UI controls. If your final intention is a very flexible and professional looking report you would probably want to explore these options.
Developer Take:
This is just a small section on how it is to develop with these various reoprting tools.
CR (as of v8.5) has almost no real report manipulation beyond their built-in functions. As a developer you can change various run-time things such as page setup, the target database, etc but the actual report must be pre-built.
AR is a developer’s dream! It is entirely created for .Net. You have full access to the reporting life-cycle. Just as you have access to events for web forms or ASP.Net web pages like Init, Dispose, etc you have the same abilities with AR. You can run code during the report init phase, the before or after retrieving a record from the db, before or after rendering a page, etc. Anything you want to do you can do. It’s definitely a breath of fresh air for any developer.
SSRS really has no other abilities than what was talked about above in the Advanced section. Like CR you can change a couple of run-time settings but you don’t have near the control of AR. Again, I suppose you could intercept the xml and manipulated it on the fly before passing it to the rendering engine, but again, you have no control other than basic formatting/calculation functions while in the actual generation process.
Distribution:
I’ve used four basic ways to distribute my reports: give the report file to the user, embed it in my Windows Forms app, embed it into my web app or upload it to some Reporting Server.
- Give the report to the user(s). With the web this is becoming less and less of a desired option.
CR: The user must have either the full CR product or a report viewer.
AR: I don’t think this is an option at all.
SSRS: With Report Builder you can export the report as an xml file, which you can share with co-workers or give to a developer who can use it as a base in SSRS to expand on the functionality. Comes in handy when an end user designs a report the way they want in RB but then requires some more advanced functionality so they give it to the developer to make the changes and deploy on the Report Server. This gives some freedom to the end user while taking some of the labor off the developer. - Embed it into the Windows Forms app. All three products can do this and usually without any problem what-so-ever. Since most of the security restrictions have been removed by running it in an application and the referenced reports/dlls are always available this is relatively straight forward. In all three cases a “report viewer” control will be embedded in the app. There is no licensing restrictions with using the report viewer with any of the there companies.
- Embed it into the web app:
I haven’t done this with CR but I know they have web controls for this.
With AR you can render the app in a variety of different ways. I especially like that I can, in code, render the report in a variety of formats, such as pdf, and stream it to the user as a download.
SSRS has a web viewer control but its very problematic. For instance parameters for linked reports or sub-reports don’t seem to carry through well. Dynamically assigning parameters is a head ache all together. On one app where we use forms authentication we simply unlinked the reports and created separate urls. Really lost a lot of nice dynamic functionality when we did that but it was the only we could find to adequately give data to only the users who should be able to view it. - Reporting Server:
As of v8.5 BO had a wonderful Crystal Reports Server web based server. It was very easy to use and was free if you only needed 5 concurrent users. This worked great for small departments. We had a reporting server crash and rebuilding the settings from backup was a pain. It took about a day and could have been made much easier but all in all the server was a pleasure to use when it was up and running. It offered all the normal functions such as deploying reports, distributing them via on demand, sending to email, caching, scheduling, etc. It even served as more of a document server in that it would let you upload other documents like PDFs, Word docs, etc.
Read the quirk below regarding the ActiveX report viewer control. It was the only sore point of the system. Unfortunately BO changed the licensing and now you can only use it for free with 5 named users. Now its really only good as a test server or a very very small department. To buy it is several thousands of dollars which makes it prohibitive for some groups and not really worth the money. A good development team could recreate the web app if they really wanted to use CR reports.
AR has no server.
SSRS has their Reporting Server. This is also an excellent system. It mirrors much of the functionality of the CR server (except that it only serves SSRS documents). This also is a pleasure to use and is very easy to implement. In 2008 they have revamped the server and now it no longer depends on IIS.
Quirks:
This is just all around quirks. There are a multitude for each app and you’ll just have to learn how to work around each one. Here are just a few I can remember off the top of my head.
CR:
- As of version 8.5 the browser web control used by CR Server (the web server) was a real pain. It was an ActiveX object and often would become corrupt or not download properly. Also, BO would regularly release new versions of the control and only half the browsers would correctly download the new version. The only way to manually re-install the control was to log in as an administrator (we had IE locked down fairly tight at that company), go into IE, view the downloaded objects and manually remove the control. Then we had to see if IE would correctly download the control the next time, which was up to chance again.
- Using the API in v8.5 for web forms was somewhat problematic. Not all reporting capabilities that were available in the designer were available in the API. Also, sometimes the order or inclusion of specific calls determined the output. For instance, you could not change some of the page setup settings without first telling it what size of paper you wanted. If the report was built for 8.5×11 (Letter) and I didn’t specifically state that in code none of my settings would be applied. If I applied all my settings and then set it for Letter it would wipe out all my previous settings.
AR:
- Again, as of version 3 they broke report inheritance. I’m sure it’s fixed by now, but what troubled me is they broke it from v2 to v3 and never fixed it for several months. This force our project at the time to go back and use v2. this could be quite a problem if you had already developed several reports in v3 and now had to run v2 and v3 on the same computer (which was a headache in itself).
SSRS:
- The web report viewer control doesn’t use standard printing functionality. They rely on their own control. This is a large hassle because in many large organizations they don’t let their users download and install software on their machines. This causes a very obscure error to appear on the screen causing help desk calls (usually to me). The only thing we can do is train users to export the report as PDF and print it through Acrobat Reader.
Anyway, that’s my take. While some of these products are older the general sentiment is still the same. CR has the best designer, AR is the best for developer integration, and SSRS is free and coming along.
I’ll try and post an update once I’ve had a chance to use SSRS 2008.