Crystal Reports and the Class Not Registered – DTSConnection error

Recently I started migrating our reporting solution from Crystal Reports 10 to Crystal Reports XI Release 2.  We had switched from .NET 1.1 to .NET 2.0, and had been running CR 10 fairly well.  Unfortunately, one of our other vendors doesn’t support using anything except CR XI.  To install CR XI with Visual Studio 2005, Crystal Reports recommends using Release 2.  For the most part, converting from CR 10 to CR XI Release 2 has gone smoothly.  Except for one small, insignificant little problem…  when running on the production web server, I would get a Class Not Registered – DTSConnection error.

Here’s the setup.  Windows 2003 Server, IIS 6.0, ASP.NET 2.0, Crystal 10 Server components install and Crystal XI R2 server install.  I have the web applications setup under the main site.  Some of the apps use the CR 10 components and the new apps use the CR XI R2 components.

Fortunately, after talking with Crystal’s Tech Support, we figured out the answer.  I was using the same Application Pool in IIS for both Crystal 10 web sites and Crystal XI R2 web sites.  Once I created a second application pool for the applications that used Crystal XI R2, and assigned that pool to those applications, everything worked perfectly.

Crystal Reports Tip #2: Formulas in Group Headers…

This is a continuation of dumb things that happen in Crystal Reports… I would like to blame Crystal for this one, but this came from my ‘not paying strict attention’ to Crystal’s logic.

 

Back to the running totals from my previous blog entry.  Once I figured out the problem with the running totals and nulls, my report seemed to be working fine.  I was going along, making changes, cleaning up the report, and making sure the headers printed on every page.  All of a sudden, my totals were back to zero again!  ARRRGGGGHHHHHHHH!!!!!!  (One side note of advice… Never, ever, ever, ever try to debug Crystal Reports when you are not feeling well, tired, irritated, sick, etc…  Just trust me on this!)  After spending over 2 hours agonizing over *why* the numbers were resetting, I happened to notice something…  I had my formula that initialized the variables in my grouping report band, which is where they are SUPPOSED to be.  Wait a second, this prints at the top of every page…  Yep, you guessed it, the formula field was being calculated for EVERY page!  Doh!

 

Lesson learned:  When initializing variables in a group header, make sure the ‘Print Group Header at the top of every page’ option is turned off.  Then create a second group of the exact same function to print that nice header out…

Crystal Reports Tip #1: Running totals in formulas…

Crystal Reports.  Love it or hate it, at some point, as a developer, you *will* use it.  And it’s got more gotchas than a George W. Bush proposal.  I’m going to add all the one’s that *I* run across in my blog, just so that I don’t forget what they are!

 

Here’s the first one that bit me *REALLY* hard this week.

 

I was working on a report that has subreports with columns that were totaled and the total sent back to the main report.  Easy, right?  Well, for some reason, I was getting a zero (0) back when I was expecting a number.  Examining the running total in the subreport indicated that the number was being calculated, it just was not being sent to the main report.  Grrrr….  After a LOT of digging, I discovered something… I was using one formula to send four subreport running totals to the main report.  Some of the subreport variables WERE making it to the main report… WTF!?!  It turns out that one of the subreport running totals had a null value in it for certain subreports.  When doing an assign of the variable to be passed up the the main report, the code would assign the running total to the variable.  Unfortunately, if the running total had a NULL, the assignment line AND THE REST OF THE FORMULA WOULD FAIL WITH NO FIGGING ERROR MESSAGE THROWN!  (Note:  There were a lot of swear words said when this was discovered…)

 

Which leads us to Crystal Tip #1:

If a running total value is used in a formula, ALWAYS, ALWAYS, ALWAYS wrap it up in a null check.  Here is the code:

 

if not IsNull({#SomeRunningTotalValue} then

   DoWorkWithTheRunningTotal

else

  DoSomeWorkWithoutTheRunningTotal

 

Maybe this will save someone else from losing a day or two…