Hi,


Need to make a decision about which of these to go for.


I'm looking to develop a web-based reporting solution which replaces a networked


 Access Database which displays stacked bar charts and line graphs and tabulated 


data.


All the data will be coming from SQL Server 2000 databases on our intranet.


I've got 2 yrs asp.net and sql server but no experience in either CR or SQL RS.


Has anyone got experience in both of these technologies to help me out with the 


best way forward.


All responses appreciated.


Cheers.

Both will do pretty much the same thing--get data and display it in a report.  There are a few snazzy features in one that aren't in the other, and vice versa.


A lot of the decision has to do with installation, licensing and designing the reports.


CR .NET can be deployed on an IIS server that is separate from the SQL Server.  Reporting Services cannot.  There are a lot of DBAs and network admins out there that will put IIS on a SQL box "over my dead body".  If yours is one of those, best of luck.  In truth, you can install Reporting Services on a separate box than the SQL Server, but you will need to purchase an additional SQL Server license.  For some places, no big deal.


In both cases, the standard report designer is integrated with the Visual Studio IDE.  If someone from the accounting department want to design reports, they'll either need VS, or they'll need to purchase a designer version of the software.  Business Objects offers scaled-down versions called Crystal Reports Designer, and there are one or two third party options available for Reporting Services.


Reporting Services has built in delivery mechanisms, similar to Crystal Enterprise.  If you want people to automatically receive their crystal reports via e-mail, you'll need to write a special application to do this, or upgrade to Crystal Enterprise.  RS comes with this component already.


As far as the designer goes, RS has some nice features, but I think Crystal's is a little more flexible (but also a little more complicated).


If you have VS 2002 or VS 2003, try messing around with Crystal a little.  There's a good book at www.crystalreportsbook.com, and the first chapter or so is available as a free e-book.  We also have some good resources at http://aspalliance.com/crystal.


SSRS can also be downloaded, but you'll need to check with yoru admin/DBA to see if you can install it.  As MSDN subscribers, I have a copy running locally with SQL Server Developer Edition that I play with, so you don't need to install it on a production machine just to mess around.


I don't think this is a simple choice, since every organization is different.  Different people, different capabilities, and different approaches.
If my post is your answer, please mark it as the answer. It will bring good karma.

Crystal Alliance
My Blog
Florist Blogs

Tough question to answer and you're going to have to experiment to see what works best for you. rjdudley had a good answer (and thanks for mentioning the book!) but I would like to add to it.


There are two critical parts of your question that need to be addressed. First is that you said you are using an Access database. In my experience, apps that use an Access database do so b/c they don't have many clients hitting the server and/or because they can't afford SQL Server. Reporting Services requires SQL Server. You don't have to USE Sql Server (you can still use Access as the database), but you do have to purchase a SQL Server license. That may answer your question there.


Secondly, you mention that you want to do charts. No one is impressed with the charting capabilities of CR.NET. Sure, it has plenty of chart types that will make most people happy. But the customization of charts sucks. You can't change the colors, most fonts, or much of anything else. Charts in CR.NET are like a black box. You tell it the type of chart you want and set the data points and it produces a great chart. But you have very little control over the details. If you are happy with the default settings, then you will be satisified. Re Reporting Services, I still haven't used it so I can't say anyting about its charting capabilities. So you are on you own to decide whether this is better or worse than the CR.NET charts. Considering that charts are so visual and everyone has their own preferences, you should do tests with both tools to see which suits your fancy.


HTH,




Brian Bischof


www.CrystalReportsBook.com

I had to make a decision about 6 months ago to go with Crystal 10 or RS and as what was said above both have their ups and downs but the one that sold it for me was security. In RS it is not possible to use ANY authentication other than windows authentication unless you have the Enterprise licence for SQL in which you have access to the security API and you have to write your own security component. There is a way around this by using web services but this looks unprofessional, you lose the ability to use the reports toolbar and drill down functionality. So if you are looking to provide reporting on an intranet (or a strongly controlled extranet) then RS could work. 


I ended up forking out the money to upgrade to Crystal 10 Adv Dev Edition which is far less buggy then 9) which gets aroud chart customisation problems and I just use MSDE and save money by not having to buy a SQL licence. One complaint I do have about Crstal is it's inability to work natively with datasets when it comes to using Crystal Enterprise Embedded, which hinders my reports from being scalable. I can also customise the majority of elements on any of my reports at runtime.


Ryan

Thanks for the replies... I still need to determine all client requirements


 this week but my leaning is towards SQL RS. I viewed this demo and was


 impressed with the tight integration and customising of data presentation - not much was 


mentioned about security though. By the way, is Microsoft veering away from CR.NET in 


favour of SQL RS? This app may be the first of many other reporting tools and in-house skills


 are MS-based for the medium term.


rjdudley: I've heard confilicting reports that SQL RS can/cannot be locally installed 


with the provided media (eval version says it does and so does this article on code magazine). 


I would prefer to do this than install on the only SQL "Server" which is running an 


important system 24-7. Re: MSDN subscriptions... if I needed to what would be the most 


cost-effective subscription to get the "local" SQL RS media? I had a look on MSDN subscriptions 


but there's not a great deal of focus on SQL.


bischofb: In the current system I'm replacing there are some stacked bar charts 


and line graphs which are coloured / patterned specifically to make photocopied versions as 


readable as colour versions - I currently work in a manufacturing facility where paper still 


has its place (our in-house web apps are in their 1st year). Your feedback leads me to 


believe CR might not be up to this. 


RyanSmith: Windows authentication might be ok but I'm surprised by this limitation.


Looking at  a summary of the features it says you can still put people into roles before you 


have to start programming effort. The other thing about RS is that you have to write 


extensions for anything other than reporting data direct from pre-defined queries. 


I'm thinking in terms of object modelling data specifically.


Cheers.

Remember that MSDN subscriptions are for development purposes only.  You do not have a license to run any of that software in a production or fail-over capacity.


My dev machine has SQL Developer Edition and SSRS installed locally, so it is very possible.  Since this is a dev machine, this is OK under our MSDN subscription.  A production environment is different.


You can install SSRS on a separate machine from your SQL server.  The most cost effective license for a production server would probably be SQL Server Standard, but your client should check their licensing agreement if they have one.  Your other option is SQL Enterprise, which is usually more expensive.  SSRS is considered as part of SQL Server, so there is no separate license needed, other than the additional SQL Server license for the separate machine.


It's been a long time since I installed the SSRS, but there should be an option to install the Visual Studio IDE plug-ins so the developers can design reports.  If I remember correctly, there is no limit to the number of times you install the design tools, but everyone needs Visual Studio who will be using it.  Make sure you double-check that.  The design tools do not need a local install of SQL Server--they'll communicate with the reporting server.  These design tools are only project templates and designer plug-in to the VS IDE.


Brian raised an excellent point about charting.  SSRS uses Dundas Chart as its charting engine, while CR .NET is very limited.
If my post is your answer, please mark it as the answer. It will bring good karma.

Crystal Alliance
My Blog
Florist Blogs

There is a major architecture difference between the two technologies. CR can be embedded directly into your application giving you complete access to who views each individual report. RS runs on it's own and cannot be embedded into your application (unless you use web services which has many downfalls) it runs as it's own seperate site and the ownly way to see the reports on the net is to put the entire service on the web and you need to pass the location of the report as a URL. Pasing parameters is also unprofessional at the moment as you need to pass them via the URL so anyone can see the parameters you used to create that report, to me this was not a risk I was not willing to take. As RS runs as its own app you need the Security API (which ownly comes in Enterprise) to be able to custom authenticate the user and then pass that information into RS.


What RS need is some kind of viewer server control (much like CR) that can manage the reports programatically and not have directly available on the web. The good news is that they are working on this functionality but have give no time lines. In my belief RS is currently a beta release and you won't see the full functionality until Yukon and VS 2005. Microsft is not veering awat from CR in fact from what I have read they are making some very good improvements and I am Brian can point you in the directions of the exact improvements being made.


You do need to write modules for just about everything in RS which in a very big pain. The good news is that there is alot of templates out there which make it easy to write your own modules.


Ryan

I'm at a loss about discussing RS b/c I'm so buried in CR. I'm sooo tempted to do a head-to-head comparison for each feature and try to provide a solid understanding of which tool excels in which area. There are so many discussions about this and no one really has a quality whitepaper available that we can reference. I did see one really good article but if focuses on Enterprise issues. Of course, this is very important, but my target audience is the developer in the small to medium size organization who doesn't need CE. This article doesn't help as much there. Here it is:


http://www.baadd.org/uploads/SQL%20Reporting%20Services%20vs%20Crystal%20Reports%20comparative%20analysis.pdf


I think what we need is a head-to-head comparison of the report designer features as well as a user friendliness comparison. I keep hearing about how easy SSRS is to use, but then I find out that it is a pain to connect to ADO.NET. This is the primary means of connecting to data and people are having trouble doing it? How is that user friendly? And what's up with having to build all these modules that Ryan mentioned? Why is everyone praising SSRS as being so user friendly when you have to jump through all these hoops to do basic reporting? I'm at a loss here. 


There's lots of confusion out there and no straight answers. We need better information than leaving it to the marketing departments to "help" us decide what tool is best. Very frustrating.


</rant>


Back to the question at hand. Re your charting needs, I think you'll be fine with CR b/c it does have a  black/white option and a multitude of other charts with lots of formatting options. I was a little too harsh earlier b/c the runtime customization of charts is lacking and could use a lot more work. As Ryan metioned earlier, getting the developer edition smoothes out a lot of bumps.


I still think that you shouldn't make a decision based upon what you are reading here. Both tools are free to use and easy to get started with. Create charting reports with both tools and see which one you like. I bet that both CR.NET and SSRS are going to meet your needs. So its really a matter of preference in terms of which one you feel more comfortable with. 


Choosing a reporting tool is a major decision and both choices have their share of headaches. Take into consideration what everyone here has to say, but don't make a major decision like this based on a few comments posted on the web. Work with each tool this week and see which one best fits your needs.

Brian Bischof


www.CrystalReportsBook.com

 calcium wrote:
Hi, Need to make a decision about which of these to go for. I'm looking to develop a web-based reporting solution which replaces a networked Access Database which displays stacked bar charts and line graphs and tabulated data. All the data will be coming from SQL Server 2000 databases on our intranet. I've got 2 yrs asp.net and sql server but no experience in either CR or SQL RS. Has anyone got experience in both of these technologies to help me out with the best way forward. All responses appreciated. Cheers.

Out of curisoity, what decision did you finally make and why?
matchbx

We have a web app that is using  crystal reports for reporting and forms that users download and fill out and we've had a great deal of problems with these.  It's not bad for basic reports and forms. Our bigest problem is getting the reports to present them selves consistently in different environemts - they look good in development when run from Crystal IDE,then little things like lines may not line up properly when moved to the QA server.  You fix that, then your paging gets all screwed up in production, and you can't reproduce the issue in QA or Dev ??!??!  It's very frustrating.  CR does not have a expandable table with cells where you can drag-and-drop values,  so you have to build it with lines - these are a pain to create, and difficult to maintain. Some of the forms we tried to develop  so we could export them in PDF and Word, however that has turned out to be an almost impossible task.  Exporting to Excell wasn't exactly straight forward either, and it turned out to be easer to pump the data into a datagrid and push this out to a pop-up window and specify excell as the application.

A few things to bear in mind here - 1) we were using the CR Professional version 9, not the developer version.  That may be part of our problems as we've been able to get alot of the code-behind to work, but not everything. 2) We are now using VS 2005 Beta!  VS2003/CR9 Pro gave us enough problems that VS2005 may have fixed, but there are some issues that may be beta related. 3) We have a large group of developers - some are more expreienced at CR than others.  Knowing some of the quirks makes things easier, having to learn the quirks can be time consuming.  Learning CR is not that difficult, and if you've used any other reporting tool, it should seem similar, but there is a learning curve, and the documentation for CR sucks.

We are planning on moving on to Reporting Services 2005 (beta!!!) this summer.  I've only seen demos so far, but from what I've seen so far, it seems it will solve some of the frustrating issues we've had.  The presenter actually re-created one of our reports in less than an hour that probably took 4 - 8 hours to do in CR. Keep in mind their experience and knowledge with RS and they used our existing stored procedure.  Also, we are just starting to understand the architectual changes we are going to have to make to our app.  Basic things like do we use the WebService or URL to fetch the report are still outstanding.

As everybody else said, this is a tough decision.  I've used CR elsewhere and did not have this many issues, but the architecture was different.  Basically, understand your needs and architecture 100%, then understand what each version of CR and RS provides for you. There are subtlties to each that you need to know up front.

Good luck

Eric