Crystal Reports 2008 SP3 upgrade hangs

I know, it's been a LONG time since my last post. During this time, Crystal FINALLY rolled out a SP3 build, thus replacing the need to go SP0 (who does an SP0 anyway?), SP1, SP2, and then SP3. It also should fix the issue I detailed here.

But, in rolling to the new build on a fresh install, my installer froze on the "Determining disk space requirements" screen. For hours.

I tried everything. Cleaning my registry with CCleaner. Rebooting. Etc and etc. Nothing worked.

Until I tried to most unlikely thing of all:
1. Check the French language pack during install.
2. Instead of selecting "Typical", choose "Custom" and don't click anything, just click "Next"

Voila! It's installing.

What the? Why in the world would that fix it? Checked some SAP forums, and it looks like I'm not the first to figure that out, so hopefully they'll get that fixed too....

Until next time.

Get RGB values from Excel cells

I do custom Crystal reports for customers as a side-job and was presented with an interesting challenge this last week.

The issue was that the customer wanted to color-code fields in their Crystal report based on pre-defined fields. They provided a spreadsheet with the colors, and it was my job to get them somewhere that the report could use.

In the end, I had to use some VBA and some new SQL tables, but it works, and works well.

The first challenge was this - in Crystal reports you really only have a couple of conditional formatting options for color, either their color constants or an RGB scheme:
// This conditional formatting formula must return one of the following Color Constants:
//
// Color (red, green, blue)
// crBlack
// crMaroon
// crGreen
// crOlive
// crNavy
// crPurple
// crTeal
// crSilver
// crRed
// crLime
// crYellow
// crBlue
// crFuchsia
// crAqua
// crWhite
// crNoColor

Since the customer was providing the colors, I knew I had to go with RBG. But how to pull those values out of the provided spreadsheet? Enter VBA.

Thanks to the power of the Google, I took and modified some VBA code to pull the unique R code, G code, and B code (since Crystal expects 3 distinct strings, not a single string in their RBG formula) for each cell:

Function showR(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showR = Application.Evaluate("=Hex2dec(""" & Right(myStr, 2) & """)")

End Function

Function showG(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showG = Application.Evaluate("=Hex2dec(""" & Mid(myStr, 3, 2) & """)")

End Function

Function showB(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showB = Application.Evaluate("=Hex2dec(""" & Left(myStr, 2) & """)")

End Function

That worked like a charm. I then dumped those values into the database, where I was able to use conditional formatting to push those values out of the database into the field.

After a large amount of work, it works like a charm.

Crystal Reports 2008 Upgrade to SP1 error - Fixed!

For work I use Crystal Reports, a lot. Recently, I was able to get a new laptop, and of course, reformatted and put Windows 7 on it (which I LOVE, but that's for another blog entry). In doing so, I also upgraded from Crystal Reports XI to Crystal Reports 2008. Of course, by this time, SAP has released a number of service packs, and I had to get SP0, SP1, and SP2. So I'm humming along, installing everything, when I see the following message: "The feature you are trying to use is on a network resource that is unavailable. Click OK to try again, or enter an alternate path to a folder containing the installation package 'CrystalReports.msi' in the box below."

Wait. What? You gave me a pre-packaged install file that is supposed to unpack everything I need. Only it didn't. And I don't have the CrystalReports.msi installed anywhere. Ugg. Now what?

I couldn't find it to download, I couldn't find it on my computer, I couldn't find it in the install package.

What I ended up having to do is reinstall Crystal. Completely. But, when I got to the screen asking if I wanted the typical or custom install, I didn't click next. Instead, I went to the temp directory it unpacked to (as you can see above - it's C:\Users\UserName\AppData\Local\Temp\) and sure enough, there's a RarSFX1 folder there, which gets deleted upon install. I copied that folder to my desktop and proceeded through the install.

Then, when I got back to that error message, I simply pointed it to my desktop, which had the RarSFX1 folder (with the CrystalReports.msi inside) and it took it and let me install the rest of the way.

What a mess. SAP really needs to fix this issue, but even though I'm not the only one with the issue, they've not posted any fixes or instructions. Needless to say, this RarSFX1 folder is getting zipped and saved on my hard drive (as well as backed up offsite) in case I need it in the future.