Enterprise Reporting Solutions

Question

Hello everyone,

What options are there in the industry for enterprise reporting? I'm currently using SSRS 2005, and know that there is another version coming out with the new release of MSSQL.

But, it seems like it might also be a good time to investigate the market to see what else is out there.

What have you encountered? Do you like it/dislike it? Why?

Thank you.

Answer 1

One of the most comprehensive solutions is Cognos.

Dislike: You wouldn't believe how many CDs it ships in... its huge.

Answer 2

Crystal Reports by Business Objects seems to be a popular choice.

I never wrote any reports in it myself, but others in my team who did sometimes struggled getting the more complex reports to work.

It also might be a bit pricey, depending on your budget.

Answer 3

Having experiences with both (CR and SSRS) here is the lowdown of what I think:

CR lets you develop a report very fast. As long as its simple. If it gets slightly complicated, it gets fishy trying to make it do what you want. Per example you are limited to a max hierarchy of 2 subreports. It gets weird when you have subreports that need parameters that must be altered in a main report, etc. Plenty of workarounds but sometime they simply suck. Also the report layout is basically fixed; you have to put your data and info into the specific sections (Page Header/Footer,Details/Report Footer/Header). This is rather helpful as it helps you correctly display data that spans on multiple pages. Also it has a fairly complete set of functions that can be used to manipulate financial data and etc.

SSRS is more flexible around the report editing. Its report wizard allows you to basically create a report in a WYSIWYG environnement, it allows you multiple subreports so you can easily display multiple datasets in one page. It allows you to connect .NET assemblies to do complicated data manipulation/calculation. However, it can get hard to properly display your reports in a fixed way, you often have to struggle to get everything displayed as you want it.

Crystal Reports is $$$. SSRS, if I remember correctly is now bundled "free" in the SQL Server Enterprise edition. Of course you probably pay for it in the price of the whole package, I guess it's MS way to try and push it in corporate land.

Answer 4

I've been using SSRS for a while now... and coworkers who look over my shoulder say it looks to be MUCH easier to do the SSRS thing than the Crystal. I've never used Crystal, so I can't tell you which is better, but I get the distinct impression that MS tried to rush SSRS out the door.

Largest weaknesses:

• Sharing Datasets. I work in a DoD environment. 90% of my reports use a Service parameter. I get sick of typing the same query over and over again.
• Skinning. If you do the report wizard you can skin your report, but not if you do it manually? huh? I can "skin"things by selecting all the affectedfields and then setting back colors,fore colors, etc. But nowhere (atleast no where I can find) can youskin something with 1 click.
• No custom skinning. Report wizard/ manual, there's no where I can find to implement a custom skin. Would be nice to just set up something (like CSS for HTML) and then just link to it. Tools should help you by reducing your effort rather than add to said effort.
• Matrixes need better documentation. I can do VERY simple things, but once I try to get into fun/difficult things, books/the internet seem to let me down. Tables don't have this issue.

Strengths:

• Very simple for an old SQL developer to get good reports that at least look better than the drek that dumping a restlt set to Excel provides.
• Custom sorting (use on most reports)
• Handles SP and Straight SQL. Love that I'm not locked into 1 path or the other (I've used both depending on circumstances).
• Price... once you've paid for Visual Studio/SQL Server... it's a freebie.

My 2 cents, hope this helps you.

Answer 5

There are a number of really great solutions out there for Enterprise Reporting. Within the big four (BO/Crystal, MS SRSS, Cognos, Oracle) the basic reporting functions are all covered. You really need to evaluate what core functionality is most important to you and what the pre-dominant architecture in your environment is.

The consolidation within the BI market has made the environment issue all the more relevant. If you have an Oracle enterprise, you may as well use Oracle BI. The same applies for SAP/BO, IBM/Cognos, and Microsoft. Particularly if you are making a new BI decision.

Finally, there are a number of Open Source solutions (BIRT, Jasper, Pentaho) that make sense if you are an OSS shop or if you are looking to avoid some of the licensing fees associated with the major BI players.

Answer 6

A "pure Java" solution is i-net Crystal-Clear.

• Supports Crystal Reports templates as well as any JDBC data source.
• Comes with a visual report designer.
• Good price for what it does, especially in comparison to some of the "pricier" alternatives.
• The latest version includes a web-based configuration tool as well as an ad-hoc report creation tool.

Answer 7

We are in the middle implementing Cognos right now, and I really think it's a fairly robust tool. The ETL tool seems pretty straightforward and easy to use and the front end is fairly easy to administer and set up. I don't have much experience in the framework models and the data modeling stuff, but our report designer guy really seems to like it.

Answer 8

You should try BIRT. BIRT is open source so you can start for free. It has a nice graphical designer. You can see some videos of how easy to design BIRT reports at http://www.birt-exchange.com. The BIRT project was sponsored by Actuate Corp who offers commercial servers for deploying BIRT to the Enterprise when you need scheduling, security integration, email notifications, etc. The commercial version also mixes AJAX with the BIRT viewer for more end-user interactivity and offers ad-hoc BIRT reporting through a browser.

Answer 9

I'm suprised no-one has mentioned Microstrategy. We do quite a bit of datawarehouse (11TB) work and microstrategy does a great job or generating SQL so the business users can get the data without bothering us. However it is a very expensive solutuion. if you don't need ad-hoc abilities and decide on crystal i recommend lookin into their VS2005 or Eclipse plugins which are "fre for production use".

Answer 10

I'd like to make two contributions. One is very negative (CR is rubbish) and the other is very positive (SSRS is backing store independent and available at no cost).

On a side note, if you mod an answer down then add a comment explaining why you think the answer is wrong or counterproductive, unless someone else already said the same thing. Even then, a simple "as above" would be helpful.

Crystal Reports is rubbish

Crystal Reports is an insult to the development community. Simple dialog resize bugs that would be the work of moments to fix have remained uncorrected over ten years and six major releases, so I really doubt that any attempt is ever made to address the tough stuff. Crystal Reports is profoundly untrustworthy, as this SQL demonstrates.

SELECT COUNT(*) FROM sometable WHERE 1=0

This statement produces a result of one when it should produce zero. This is a repeatable off-by-one error in the heart of the Crystal Reports SQL engine.

The support for CR is equally dismal, having been moved offshore many years ago. If you cough up $200 for a support call, an unintelligible foreigner will misunderstand your question and insult your intelligence until you give up, at which point he will - because you have chosen to give up - declare the call resolved.

If it's really this bad why is it so popular? Great marketing. Management types see glossy adverts promising much, and because CR has been around so long they assume it's all true. When managers lack the technical expertise to make a decision, rather than allow a technical person to make the decision they fall back on precedent and repeat the mistakes of their peers. They also fail to realise that if they want to actually use the web delivery stuff they are up for a server licence. Also, longevity means it's easy to find people with CR experience.

For the details and a good laugh I recommend these links.

• Clubbing the Crystal Dodo
• Crystal Reports "Sucks"
• Crystal Reports Sucks Donkey Dork

Or just type "crystal reports sucks" into Google. For a balanced perspective, also try "crystal reports rocks". Don't worry, this won't take much of your time. There are no positive reviews outside their own marketing hype.

Now for something more positive.

SQL Reports is effectively free

You can install it at no charge as part of SQL Express with Advanced Services. You can also install .NET 2.x which brings with it ADO.NET drivers for major database providers as well as generic OLEDB and ODBC support.

Since SSRS uses ADO.NET, this means you can connect SSRS to anything to which you can connect ADO.NET, ie just about anything.

The terms of the licence applying to SSRS as supplied with SQL Express require it to be deployed and installed as part of SQL Express. They don't have anything to say about where reports get their data.

SQL Express is limited, but the accompanying SSRS has no such limitations. If your data is provided by another database engine you can support as many users as that engine is licensed to support. Don't get me wrong, at work we have dozens of licensed copies of MS SQL Server. I'm just saying that you can use SSRS against the backing store of your choice, without having to find or justify budget for it. What you will be missing is scheduling and subscription support. I speak from experience when I say that it is not profoundly difficult to write a service that fills the gap.

SSRS fulfils every promise that CR makes. Easy to use, good support for user DIY, has a schema abstraction tool conceptually similar to CR BO but which works properly, high performance, schedulable, easy to use, stable, flexible, easy to extend, can be controlled interactively or programmatically. In the 2008 edition they even support rich-formatted flow-based templates (mail merge for form letters).

It is the best reporting solution I have ever seen in twenty years of software development on platforms ranging from mainframes through minis to micros. It ticks every box I can think of and has no profound weakness I can recall.

It does not address problems like heterogeneous data provision, but IMHO these can and should be addressed outside of the report proper. Plenty of data warehousing solutions (such as SSIS) provide tools for solving such problems, and it would be absurd to put a half-assed duplicate capability in the report engine.

Answer 11

I've used Cognos Series 7, Cognos Series 8, Crystal Reports, Business Objects XI R2 WebIntelligence, Reporting Services 2000, Reporting Services 2005, and Reporting Services 2008. Here's my feedback on what I've learned:

Reporting Services 2008/2005/2000

PROS

1. Cost: Cheapest enterprise business intelligence solution if you are using MS SQL Server as a back-end. You also have a best-in-class ETL solution at no additional cost if you throw in SSIS.

2. Most Flexible: Most flexible reporting solution I've ever used. It has always met all my business needs, particularly in its latest incarnation.

3. Easily Scalable: We initially used this as a departmental solution supporting about 20 users. We eventually expanded it to cover a few thousand users. Despite having a really bad quality virtual server located in a remote data center, we were able to scale to about 50-100 concurrent user requests. On good hardware at a consulting gig, I was able to scale it to a larger set of concurrent users without any issues. I've also seen implementations where multiple SSRS servers were deployed in different countries and SSIS was used to synch the data in the back-ends. This allowed for solid performance in a distributed manner at almost no additional cost.

4. Source Control Integration: This is CRITICAL to me when developing reports with my business intelligence teams. No other BI suite offers an out-of-box solution for this that I've ever used. Every other platform I used either required purchasing a 3rd party add-in or required you to promote reports between separate development, test, and production environments.

5. Analysis Services: I like the tight integration with Analysis Services between SSRS and SSIS. I've read about instances where Oracle and DB2 quotes include installing a SQL Server 2005 Analysis Services server for OLAP cubes.

6. Discoverability: No system has better discoverability than SSRS. There are more books, forums, articles, and code sites on SSRS than any other BI suite that I've ever used. If I needed to figuire out how to do something in SSRS, I could almost always find it with a few minutes or hours of work.

CONS

1. IIS Required for SSRS 2005/2000: Older versions of SSRS required installing IIS on the database server. This was not permissible from an internal controls perspective when I worked at a large bank. We eventually implemented SSRS without authorized approval from IT operations and basically asked for forgiveness later. This is not an issue in SSRS 2008 since IIS is no longer required.

2. Report Builder: The web-based report builder was non-existant in SSRS 2000. The web-based report builder in SSRS 2005 was difficult to use and did not have enough functionality. The web-based report builder in SSRS 2008 is definitely better, but it is still too difficult to use for most business users.

3. Database Bias: It works best with Microsoft SQL Server. It isn't great with Oracle, DB2, and other back-ends.

Business Objects XI WebIntelligence

PROS

1. Ease of Use: Easiest to use for your average non-BI end-user for developing ad hoc reports.

2. Database Agnostic: Definitely a good solution if you expect to use Oracle, DB2, or another database back-end.

3. Performant: Very fast performance since most of the page navigations are basically file-system operations instead of database-calls.

CONS

1. Cost: Number one problem. If I want to scale up my implementation of Business Objects from 30 users to 1000 users, then SAP will make certain to charge you a few hundred thousands of dollars. And that's just for the Business Objects licenses. Add in the fact that you will also need database server licenses, you are now talking about a very expensive system. Of course, that could be the personal justification for getting Business Objects: if you can convince management to purchase a very expensive BI system, then you can probably convince management to pay for a large BI department.

2. No Source Control: Lack of out-of-the-box source control integration leads to errors in accidentally modifying and deploying old report definitions by mistake. The "work-around" for this is promote reports between environments -- a process that I do NOT like to do since it slows down report development and introduces environmental differences variables.

3. No HTML Email Support: You cannot send an HTML email via a schedule. I regularly do this in SSRS. You can buy an expensive 3rd party add-in to do this, but you shouldn't have to spend more money for this functionality.

4. Model Bias: Report development requires universes -- basically a data model. That's fine for ad hoc report development, but I prefer to use stored procedures to have full control of performance. I also like to build flat tables that are then queried to avoid costly complex joins during report run-time. It is silly to have to build universes that just contain flat tables that are only used by one report. You shouldn't have to build a model just to query a table. Store procedure support is also not supported out of the box without hacking the SQL Overrides.

5. Poor Parameter Support: Parameter support is terrible in BOXI WebIntelligence reports. Although I like the meta-data refresh options for general business users, it just isn't robust enough when trying to setup schedules. I almost always have to clone reports and alter the filters slightly which leads to unnecessary report definition duplication. SSRS beats this hands down, particularly since you can make the value and the label have different values -- unlike BOXI.

6. Inadequate Report Linking Support: I wanted to store one report definition in a central folder and then create linked reports for other users. However, I quickly found out end-users needed to have full rights on the parent object to use the object in their own folder. This defeated the entire purpose of using a linked report object. Give me SSRS!

7. Separate CMC: Why do you have to launch another application just to manage your object security? Worse, why isn't the functionality identical between CMC and InfoSys? For example, if you want to setup a scheduled report to retry on failed attempts, then you can specify the number of retries and the retry interval in CMC. However, you can't do this in InfoSys and you can't see the information either. InfoSys allows you to setup event-driven schedules and CMC does not support this feature.

8. Java Version Dependency: BOXI works great on end-user machines so long as they are running the same version of java as the server. However, once a newer version of java is installed on your machine, things starts to break. We're running Java 1.5 on our BOXI R2 server (the default java client) and almost everyone in the company is on Java 1.6. If you use Java 1.6, then prompts can freeze your IE and FoxFire sessions or crash your report builder unexpectedly.

9. Weak Discoverability: Aside from BOB (Business Objects Board), there isn't much out there on the Internet regarding troubleshooting Business Objects problems.

Cognos Series 8

PROS

1. Ease of Use: Although BOXI is easier to use for writing simple reports for general business users, Cognos is a close 2nd in this area.

2. Database Agnostic: Like BOXI this is definitely a good solution if you expect to use Oracle, DB2, or another database back-end.

3. FrameWork Manager: This is definitely a best-in-class meta-data repository. BOXI's universe builder wishes it was half as good. This tool is well suited to promoting packages across Development, Test, and Production environments.

CONS

1. Cost: Same issue as Business Objects. Similar cost structure. Similar database licensing requirements as well.

2. No Source Control: Same issue as Business Objects. I'm not aware of any 3rd party tools that resolve this issue, but they might exist.

3. Model Bias: Same issue as Business Objects. Has better support for stored procedures in FrameWork Manager, though.

4. Poor Parameter Support: Same issue as Business Objects. Has better support for creating prompt-pages if you can code in Java. Buggy behavior, though, when users click the back-button to return to the prompt-page. SSRS beats this out hands-down.

5. Inadequate Error Handling: Error messages in Cognos are nearly impossible to decipher. They generally give you a long negative number and a stack dump as part of the error message. I don't know how many times we "resolved" these error messages by rebuilding reports from scratch. For some reason, it is pretty easy to corrupt a report definition.

6. No Discoverability: It is very hard to track down any answers on how to troubleshoot problems or to implement functionality in Cognos. There just isn't adequate community support in Internet facing websites for the products.

As you can guess from my answer, I believe Microsoft's BI suite is the best platform on the market. However, I must state that most articles I've read on comparisons of BI suites usually do not rate Microsoft's offering as well as SAP's Business Objects and Cognos's Series 8 products. Also, I've also seen Microsoft come out on the bottom in internal reviews of BI Suites in two separate companies after they were review by the reigning CIO's. In both instances, though, it seemed like it all boiled down to wanting to be perceived as a major department that justified a large operating budget.

Answer 12

In his blog at SAP Community Website, Henry Nordstrom, has given a very good evaluation of various reporting tools available. Though he has done the same from SAP usage point of view, the facts are applicable to anything else also.

Henry's Blog on SAP Developer Network

Answer 13

I'm surprised nobody mentioned OpenReports with Jasper report templates. I know it's not quite enterprise level, but it's quite powerful and I think on par with Crystal Reports. I use iReport to create CR-like reports. OpenReports also supports JXLS which is very easy to use to create Excel-based reports.

http://oreports.com/ http://jasperforge.org/projects/ireport

Answer 14

If you want an enterprise-class report server that works with ANY report designer you want to use, check out Universal Report Server from VersaReports.com. Out-of-the-box it supports Crystal, DevExpress, Telerik, and ActiveReports, and provides an API if you want to support another report designer.

Answer 15

I recommend you to try FastReport.Net. I like it, check my answer here: