I feel like a kid with a new toy.  Ido Millet of Millet Software shared with me a method for taking any address in a report and using it to generate a map on that same report. The technique relies on a combination of two different features:

1) Crystal’s ability to show a dynamic image based on a path to that image
2) Google’s ability to generate a map image based on a URL with parameters

All you have to do is to write a formula that uses the address fields in a report to generate a Google compatible URL. He then set that URL to be the dynamic path for the image. The Crystal image object goes to that URL to fetch the image, which causes Google to generate the map image and send it back to the report. Here is a simple example of the formula:

"http://maps.google.com/maps/api/staticmap" &
"?center=Leesburg, VA" &
"&zoom=11&size=512x512" &
"&maptype=roadmap" &
"&markers=color:0x99FFFF|label:X|525 East Market St, Leesburg, VA" &
"&sensor=false"

Notice that Both the “Center” value and the address value in “Markers” are both Continue Reading »
Adding Google Maps to reports

I recently had to convert a complex Crystal Report into a SQL Query, including all the calculations. I eventually found ways to convert everything that was needed and learned some valuable SQL skills. But I also learned to appreciate the things I take for granted in Crystal syntax, things that have no equivalent in SQL syntax. Here are some examples just in case someone else has to do a similar conversion:

1) One huge difference is how simple it is in Crystal to reference one expression in another. Crystal will automatically calculate a series of dependent formulas in the right order, based on which formulas refer to others. In a SQL query, if you write a calculation for column A and you want to use that as part of the calculation for the next column, you can’t simply refer to column A by name. Instead you have to repeat the entire calculation.

The exception is Continue Reading »
Converting Crystal formula logic into SQL queries

I have written before about Crystal windows that end up off screen.  This often happens when you open something like the Database Expert or the Formula Editor.   This happened to me the other day when I was using my laptop outside of my office and so my second monitor wasn’t available.  It can also happen if you switch from a high resolution mode (which shows a larger workspace) to a lower resolution mode.  When I wrote about this before my solution involved digging into the registry.  Since then I have found some keyboard shortcuts that allow you to move a hidden screen back into the viewable area.  One of these 3 things should work:

1) Hold down the Windows key and hit the up and left arrows until it appears in your viewable area
2) Hit ALT-Space, then hit the X.  This will maximize the window in your current screen.
3) Hit ALT-Space, then hit the M.  This will put you in ‘Move’ mode and you can use your up and left arrows as mentioned above.

I solved a mystery this morning that has bugged me for a long time. As you can imagine I use lots of reports to run my business. I just noticed that half of them give me the “invalid printer” message when they are opened. This message usually appears when you move a report from one PC to another that has different printers. Because I fix other peoples reports on my PC, I see this message all the time, so I am used to ignoring it. But I only have one printer so it doesn’t make sense that I should see it on my own reports. The normal fix (selecting an existing printer) doesn’t work here because these reports are already pointed to an existing printer.

The message is also harmless. It doesn’t prevent the report from running. It just adds an extra click every time I open a report. That is why I ignored it to the point where it became invisible. But after one of my customers asked about the message I decided to see what it took to get rid of it.

So the next time the message came up I selected a different printer on my PC and hit ‘apply’. I then re-selected my main printer and hit ‘apply’ again. This fixed the problem on almost all reports.

On the few occasions where it didn’t fix the problem I clicked the printer ‘preferences’ button. This doesn’t appear for all printers but my Brother brand printer has a button called preferences. In there I found a setting that sets the printer preferences to the default. This also fixed the problem so I used that method on reports where the first method didn’t work.