Crystal Reports vs. SQL Server Reporting Service (SSRS)

Nicole Duffey

I'm a beginner level Crystal user and I know nothing about SSRS. I'm looking for insight!



Who is using these products, and are you ever using both- if so when?



Are you using a data warehouse or are you sourcing data directly from your live environment?



If you've used both in your career which one do you prefer and why?



What have you found to be the necessary skills needed to create reports in either?



Tell me about your lessons learned in implementing either.



Anything else you are willing to share!! Thanks!!

Joshua Bekerman bCLO bCRE

Hi Nicole, my answers below in purple:



Who is using these products, and are you ever using both- if so when?

I use Crystal Reports and have taken classes on SSRS but have not started using it yet.



Are you using a data warehouse or are you sourcing data directly from your live environment?

For Crystal Reports I only use it with the MDB Export file from Raiser's Edge.  Would prefer to use SSRS, eliminating the need to export from RE and hit the live data.



If you've used both in your career which one do you prefer and why?



What have you found to be the necessary skills needed to create reports in either?

• Not sure if these are absolutely necessary but they sure would help:
  • a good understanding of Query & Export, which Query and Export type to use and which fields to export
  • a general understanding of the structure of the MDB file RE creates and how the tables link together
  • a throrough understanding of the different sections in Crystal Reports (details, group header, report header, etc.)
  • great problem solving skills and the phone number for Blackbaud Crystal Reports support
  • a steady diet of chocolate and carbonated beverages

Tell me about your lessons learned in implementing either.

My lessons learned can be found at bekerman.com 



Anything else you are willing to share!! Thanks!!

I have way too many animals in my house.

James Andrews

I've used Crystal extensively, and have started experimenting with SSRS.  More recently because of financial and charting needs I've been reverting to Access and Excel, and I'm in the process of biting the bullet and learning VBA, and considering implementing powerpivot and VBA to eliminate the Access portion of my reporting.  But that's neither here nor there; it's just something fun I've taken up lately.



Anyway, to answer the questions I can answer:

Are you using a data warehouse or are you sourcing data directly from your live environment?

Sourcing from live.  For the majority of our Crystal reports, we use RE's export/custom reporting function.  There are a few that don't because of multiple exports or external data -- for those we export out of RE and open Crystal separately.  And I have a couple of Crystal reports that pull directly from the back end.

What have you found to be the necessary skills needed to create reports in either?

For creating the report itself, just some aptitude for thinking around a problem in different ways.  A very basic understanding of programming language structures so you can write formulas the way you want. Understanding of basic relational database principles.  Understanding grouping/sorting and how reports interact and process data.



For getting the data for your report, if you're going to be reporting off of live data, at a basic level you'll need decent SQL skills and at least a beginning understanding of how RE is set up.

Tell me about your lessons learned in implementing either.

RE's export limitations and the crippled implementation of Crystal are the things that frustrate me most in implementing custom reports. Here's an example. Say you want to export info about a constituent, including their primary business, and whether or not that business is a member of your corporate membership program.  It's not there, and to get it you have to do a second export and link things up in crystal.  



You'll find limitations like this start happening quickly once you move beyond basic custom reporting, and more and more frequently.  I'm not saying RE's custom reporting feature is useless, but the combination of export and custom reports can only take you so far.



I would clarify something Josh said -- you don't need to have SSRS to hit the live data. If you have access to your SQL server, you can write your SQL in Crystal and pull your report dataset directly from live.  I am not an expert, but from what I can tell, Crystal just basically passes your query to the server and takes the info it returns.  I've written a couple Crystal reports directly off of live data, and they work fine -- the only limitation is that if you're trying to write your code in Crystal, you can only see tables and stored procedures, not views or functions.  You can use them, you just can't see them. So it's best to write your queries outside of crystal (I use SSMS) and then just copy/paste into it.



Have fun!  I find that really digging into reporting and querying RE and whatnot is interesting and fun, and part of the fun is discovering the 'why the heck did they set that up like THAT?' moments and working through them.

Joshua Bekerman bCLO bCRE

I would clarify something James said about clarifying something I said about hitting the live data.    In my noggin my comment is more related to the user's ability to run the reports on their own through RE Custom Reports.  If you use SQL in Crystal Reports then you cannot run it through RE, is that correct?  (I hope so, otherwise I've been living a lie for the past ten years!) 



Anyway, instead of editing my original post and making James sound like a wackadoo, here's what I should have said:

For Crystal Reports I only use it with the MDB Export file from Raiser's Edge.  Would prefer to use SSRS so we could hit the live data and still allow users to run reports on their own through SSRS.  (From the little I've seen it looks like SSRS reports can be run by users, yes?  They can also run on a schedule and be sent via email, similar to RE: Queue... again, yes?)



Wheeeeeeeeeee!



 

Tom Wright

Hi Nicole,



We predominantly use SSRS and have been doing for well over 6 months now. We occasionally still use Crystal reports, but this tends to be when one of the canned Crystal reports in RE gets us "close enough". We use SSRS to pull data from both our live environment and our snapshotting warehouse.



I personally much prefer SSRS, but I'm from a technical background. Not sure I'd recommend it for everyone - you need to be sure your organisation won't be crippled in "hit-by-a-bus" situations.



In terms of necessary skills, you'll find a solid understanding of SQL a real help. Plus, to be honest, a decent grasp of how RE structures its data, though this does come with time and experimentation.



Hope this helps!



Tom

Barbara Van Handel

Thank you for starting my day with a
laugh.  Love your sense of humor. Matches mine.  I find
that humor is the best way to handle RE queries and reporting.
J

 

Barb Van
Handel

Administrative
Assistant

Development
Department

Rawhide Boys
Ranch

E7475 Rawhide
Rd.

New London,
WI  54961

(920)
531-2596

www.rawhide.org

 

 

James Andrews

jbekerman@collegefund.org:

I would clarify something James said about clarifying something I said about hitting the live data.    In my noggin my comment is more related to the user's ability to run the reports on their own through RE Custom Reports.  If you use SQL in Crystal Reports then you cannot run it through RE, is that correct?  (I hope so, otherwise I've been living a lie for the past ten years!) 

 

Ooooh I understand what you're saying now.  Yeah, if you use SQL in the report it won't run out of RE's custom reporting module. Also if you use two data sources it won't run (edit: which you totally have to do sometimes due to Export's limitations, as I mentioned before).  



I mean, on some larger level, Crystal can work in a similar manner to SSRS, with reports running on servers and being  web accessible and blah blah blah. Or at least so I hear. I mean, bigger companies than any of ours are running on it. But I wouldn't know how to do it, or how it works.  And I've read numerous times about crystal report viewers, etc. but haven't really experimented with them.



Also it is not very difficult to make me sound like a wackadoo so don't worry yourself about that. Give it enough time and I usually do it on my own.

Nicole Duffey

Wow, what a great response so far. Thank you everyone for your input and insight… I’m hopeful the discussions continue!

A few follow up questions:

1. Are you a full-time report writer and/or is report writing your main responsibility?
2. @ Josh Bekerman: What is keeping you from migrating to SSRS?
3. Some of you touched on or indicated just by your answers that you are “tech savvy”. I would consider myself “tech savvy”, but not in the programming sense. I can understand programmers when they talk, and I understand the basic principles of programming, but I’ve never had an interest in actually coding. I think this is why it takes me so long, and so much research to write formulas. I think if I was more gifted in this area or had staff members that were we could roll much more quickly through CR report development. Thoughts? What are each of your skill levels, or what would you say is the basic level of skill in this area to be good at CR.
4. @James Andrews: Your comments on the limitations of RE and the “why the heck did they set that up like that” is right on! Some days I wish I were on the BB design team, it just seems like they aren’t designing from a user perspective. We should start a support group J
5. @Tom Wright: Are you willing to share your SSRS transition/implementation story? How did you do it, did you have help, what kind of training did you have to get, are you finding that writing reports in SSRS is easier/faster?
6. Also @Tom Wright: you said you are still using CR when one of the canned reports gets you close. Are you referring to CR reports that you’ve already written in the past, or are you referring to canned RE reports that are written in CR? If the latter, how are you getting access to the source-code for those canned RE reports?

Joshua Bekerman bCLO bCRE

Nicole, lack of time and learning SSRS is keeping me from migrating to SSRS, along with the actual need for it.  CR meets most of our current needs so the ROI to implement SSRS wouldn't be worth it.  In a perfect world I would love to use SSRS but there are plenty of other priorities right now.  Also, even though I took training it seems like there is still a lot I need to figure out before going live with SSRS.



As for this question: "what would you say is the basic level of skill in this area to be good at CR?"  This all depends on the reports you need to create.  If it's a simple report (such as the one I'm going through on my website) then the level of skill would be minimal, with little to no programming or "tech savvy" needed.  The more complicated the report the more skills and experience needed.  Not really sure I would call it tech savvy though, for me it was more learning as I go.  And still is, there's plenty of things in CR that I don't know how to do, mostly because I haven't had the need to do it.

Tom Wright

Hi Nicole,



In answer to your questions:



1. We don't have a full-time report writer because we rarely build reports in isolation - they're more often a component of a wider project.



3. For SSRS, I would say you need a pretty good level of practical (i.e. doing, not just understanding) SQL. If you don't have this, I would start with some of the many online SQL tutorials and then quite quickly move to experimenting with SELECTs against your RE database.



5. Lot's of questions all in one...



a) When we decided to switch, we did so slowly - one business function at a time. We came from a position where Crystal reports were already being neglected (in the sense that people weren't using them), so there wasn't all that much risk. One thing I would say is that you'll have to choose between "Business Intelligence Development Studio" (BIDS) and "Report Builder" for authoring your reports. We went with BIDS, but I think Report Builder is supposed to be easier.



b) The help / training came from the web - there are plenty of people working with SSRS and many (many) more people working with SQL. I would particularly recommend the community at StackOverflow.



c) Not sure whether writing reports is now any easier or faster. This wasn't a motivator when we switched though. The primary goal was increased flexibility in terms of data objects, manipulation and presentation. By this metric, SSRS leaves (the RE implementation of) Crystal in the dust.



6. I meant canned reports in RE. We're not getting the source code. We're using these reports when they are close enough to what we need that we don't have to meddle in their source code. If we were going to invest time in understanding and modifying their source code, we'd just reimplement them properly in SSRS.



Hope this is useful,

Tom

James Andrews

Nicole Duffey:

Wow, what a great response so far. Thank you everyone for your input and insight… I’m hopeful the discussions continue!

A few follow up questions:

1. Are you a full-time report writer and/or is report writing your main responsibility?

I'm not a full time report writer, although there are times when the full time of my job is spent writing reports. 

1. Some of you touched on or indicated just by your answers that you are “tech savvy”. I would consider myself “tech savvy”, but not in the programming sense. I can understand programmers when they talk, and I understand the basic principles of programming, but I’ve never had an interest in actually coding. I think this is why it takes me so long, and so much research to write formulas. I think if I was more gifted in this area or had staff members that were we could roll much more quickly through CR report development. Thoughts? What are each of your skill levels, or what would you say is the basic level of skill in this area to be good at CR.

I don't know what my skill level is.  Like, I can write reports that do a lot of things, but there are vast areas of CR and the back end of RE that I know nothing about. And part of our hesitation with reporting off the back end is the fact that I know if I use RE's export and get a gift with its campaign, fund, appeal, amount, etc etc, it's all right there.  But if I write it off the back end there's always a nagging doubt in my mind that I'm doing something wrong and the data will be wrong.  I wish blackbaud would offer training in RE's structure and querying off the database. I think a 3-4 day session going through the more frequently used areas of the schema, some of the commonly used functions and views, etc, would be immensely valuable to those of us who want to take reporting to the next level.



All that said, what I do know is that there is no other recourse for getting better at writing code than practice. And it's hard to practice when you don't have specific things that you're working on. And there's always time pressure so you can never work on things for as long as you want. So it's a frustrating cycle. And I think this is something we all can commiserate with each other about!  



GROUP HUG

Marie Stark

Report writing is my main responsbilty, although I do a few other things. We use Crystal reports, and sometimes canned RE reports. I have some programming experience. Although it is helpful, I don't think it is essential.

As others have mentioned, it takes a lot of practice. I have spent hours  in my career trying out things such as running totals, subreports, and shared variable, until I finally got an understanding of how they work.

 

Michael Logan

Hi Tom, 

I know that Crystal Report can pull live data from Blackbaud server, how do you pull data with SSRS when your data is hosted with them. 
Our data is hosted with Blackbaud and I want to use SSRS. Do I have to have a back up and store the data in SQL server to run report in SSRS?


Thanks,



Tom Wright:

Hi Nicole,


In answer to your questions:


1. We don't have a full-time report writer because we rarely build reports in isolation - they're more often a component of a wider project.


3. For SSRS, I would say you need a pretty good level of practical (i.e. doing, not just understanding) SQL. If you don't have this, I would start with some of the many online SQL tutorials and then quite quickly move to experimenting with SELECTs against your RE database.


5. Lot's of questions all in one...


a) When we decided to switch, we did so slowly - one business function at a time. We came from a position where Crystal reports were already being neglected (in the sense that people weren't using them), so there wasn't all that much risk. One thing I would say is that you'll have to choose between "Business Intelligence Development Studio" (BIDS) and "Report Builder" for authoring your reports. We went with BIDS, but I think Report Builder is supposed to be easier.


b) The help / training came from the web - there are plenty of people working with SSRS and many (many) more people working with SQL. I would particularly recommend the community at StackOverflow.


c) Not sure whether writing reports is now any easier or faster. This wasn't a motivator when we switched though. The primary goal was increased flexibility in terms of data objects, manipulation and presentation. By this metric, SSRS leaves (the RE implementation of) Crystal in the dust.


6. I meant canned reports in RE. We're not getting the source code. We're using these reports when they are close enough to what we need that we don't have to meddle in their source code. If we were going to invest time in understanding and modifying their source code, we'd just reimplement them properly in SSRS.


Hope this is useful,

Tom