Salesforce and Crystal Reports

And specifically how they don't play nice together. I've used Crystal Reports for many years now, and as far as reporting tools for relational databases go, I've always been happy with it. The problem being, Salesforce isn't truly a relational database and while it's great for data input, has never been well optimized for data output. Crystal has attempted to create a data connector for Salesforce, and I've been able to do a lot with it, but it's still fraught with problems. I've also come to have issues with the company itself, which is now SAP, but I'll get to that.

The problems. First off, while they give the impression that you can build a report on Salesforce data the same as you would on any other database, that's not entirely true. Like I said, Salesforce isn't really relational (it's relational-ish, perhaps?) so the standard methods of relating tables and defining filters in the Select Expert doesn't always produce what you're expecting. I haven't fully put my finger on what works and what doesn't, and I'm disinclined to do much in the way of QA for Crystal, but I often find that filters I've defined in the Select Expert have to essentially be replicated by suppressing groups or detail sections of records that shouldn't have been returned in the first place based on my criteria. Pay very close attention to results returned where your Select Expert criteria contains a lot of "and"s and "or"s, that's where I hit the most problems.

Then there are the issues with efficiency. Anyone who has developed reports on a relational database knows that complex reports can take a long time to run, hence the rise of OLAP (which I really need to learn more about). Well, if Salesforce is your data source, then expect the time to run a report to increase exponentially. When you include multiple Salesforce objects and define filters on what data to return in the Select Expert, you often end up pulling in a lot more data than you really need because of the limitations of Salesforce's SoQL language and Crystal has to then process what should be displayed. The workaround to this is to write Command statements in SoQL to pull the data from Salesforce wherever possible instead of pointing CR to multiple Salesforce objects. But part of the advantage of a tool like CR is supposed to be the GUI interface, so it's frustrating always having to write long, complex SoQL statements for reports that shouldn't really be that complicated.

Then there's the limitations of Salesforce itself that impact Crystal. Any other database, you'd have the ability to write SQL statements to do aggregate functions on the data (max, min, count, etc) and then let that processing happen on the database server itself and just return the results. But SoQL isn't that robust. A common request I get is to see the last time a participant (Contact) met with a JD, or the first time they worked transitional work after graduating from Life Skills class. I have 2 options to accomplish this. I can create a roll-up summary field in Salesforce and then use that field to pull in details about the record associated with that roll-up summary field. So in my first example, I create a roll-up summary field in the Contact record for the maximum Event date where the Event Record Type = "JD Meeting". Then if I want more information than just that date, like who the meeting was with, I have to relate the Contact table with the Event table on both the Contact Id and that roll-up summary date field. Kind of a long way to get not very far.

But even that roundabout solution for aggregates is not always an option, because the roll-up summary fields don't allow you to filter data based on another field. So in my second example, it's not enough to get the first time someone attended transitional work ever, I need to see the first time they attended transitional work after they've graduated from class, and our participants sometimes attend class multiple times over many years. I can't create a roll-up summary field for that, so my only option is to pull every single transitional work attendance record into the report, group it by participant, filter it for dates that are greater than their last class graduation (which has to happen in Crystal, and not in Salesforce, for the same reason as the roll-up summary limitation) and then only display the first record, hiding all the rest. That's a lot of data being pulled into Crystal, when I ultimately only need about 1% of it.

So I realize all of that was a bit wonky, but anyone who's done reporting should get the gist of it.

Now I have a number of reports designed in Crystal that can't run on crystalreports.com because they take so long to run that they time out. So despite this great on-demand system we have, I still have users calling me to run reports periodically, just what we were trying to get away from. Then further complicate this by crystalreports.com having unexpected outages 3 times during business hours in the last 6 months, and users running operational reports (like payroll) who can't wait for that crap, call me then. And ever since SAP took over Crystal, there's no more phone or email support, even something as catastrophic as their entire system being down has to be posted on a forum and then you just sit back and hope one of their techies is paying attention. (They claim they have email support, but don't believe it, the support people just email you and direct you to that forum). Very annoying, very unreliable, very unprofessional.

So what now? I'm still trying to figure that out. At the recent Dreamforce conference, I explored many reporting solutions. Everyone's answer is similar though- yank all of the data out of Salesforce into a data warehouse, either on your servers or theirs, and then do the reports on that. So I'm looking into the solutions that include data warehousing as part of an on-demand service so we can keep moving in the direction of cloud computing. They're all a bit young though, and I've now been burned by 2 supposed Salesforce reporting solutions (Crystal and Jasper Reports) so I'm wary about moving too quickly. I'm hoping to just hold things together long enough to let some of the bigger players test out available solutions and get them past the 1.0 stage. More to come on this I'm sure.