Last week I wrote about using Notepad++ for working with SQL Commands and formulas. I loved the numbered lines and the way it finds matching parens. But I missed the green comments and blue functions. Notepad++ has about 80 languages that it can format correctly but Crystal formula syntax wasn’t one of them. So I decided to see if I could create a custom language for Notepad++ that would format Crystal formulas the way they appear in the formula editor.

After several hours of experiments I have something that I like. It will format a Crystal formula with functions in blue and comments in green. I even added the literals in red like you see in SQL. And you still get numbered lines and the ability to find matching parens and brackets. I even added the ability to fold (temporarily hide) sections between a pair of parens.

So, if you are using Notepad++ and would like to try my Crystal language you can right-click on the link below and save the xml file onto your hard drive. If you just click the link normally your browser will likely open the XML in a new window.

CrystalReports Language UDL for Notepadd++

Then to deploy the file in Notepad++:
1) From the Notepad++ menu go to [Language > Define your language ]
2) At the top of the window click “Import” and open the CrystalReports.xml file you downloaded.
3) Close and Reopen NotePad++

Then when you click “Language” from the Notepad++ menu you should see Crystal Reports at the bottom of the list.  When you select Crystal Reports, the current window should display Crystal formulas in the correct format.  If you get any strange behavior, please let me know.

If you have to write SQL commands or complicated Crystal formulas, it helps to have a good text editor. My favorite is NotePad++. Not only is it free, but it can do many helpful things beyond what Notepad can do:

• Number each line.
• Highlight the corresponding paren/bracket whenever you select a paren/bracket
• Highlight all the instances of any word that you select
• Record keystroke macros to automatically repeat a series of commands
• Select a vertical strip of characters from within of a larger block of text (called “column mode”). (e.g. highlight the 5th and 6th characters in all rows at once)
• Find all of the differences between two text blocks or two lists, using the “compare” plugin.
• “Fold” (collapse and expand) sections between parentheses or keywords

It can even format the code for 80 different programming languages, including SQL, showing reserved words and comments in different colors. Within a week I will have a UDL (User Defined Language) for Crystal Reports formula syntax. That will allow NotePadd++ to format a Crystal formula to look the same as it would in Crystal, with the comments in green and the functions in blue.

If you want to try out NotePad++ the best place to get it is through Ninite.com.  I have written about Ninite before. The advantage of Ninite is that it lets you select multiple items from a list of 80+ freeware apps. Then it gives you a tiny exe that installs all the applications you chose (and nothing else) with one click. No questions, no toolbars no ‘bonus’ apps.  It is one of the few places left where you can get freeware without junk.

I have written before about database connections that require the table alias to be different than the table name. Often this is accomplished by adding a 1 to the original table name. This came up again recently with a customer using a Progress database.

We were doing a “Set Location” for a connection that covered a handful of tables. Most of the tables were moved to the new connection but one table didn’t move. We got the warning “some tables could not be found” in the new connection. When I have seen this before it usually means setting the location of that one table directly to the corresponding table in the new connection. But for some reason, even though we could see the table in the new connection the update would fail as if the table did not exist. We could even add the table to the report from the new connection, but not set the location to it.

However, I noticed that when I added that table to the report the alias had a 1 added on the end. So as a long shot I changed the alias of the problem table, adding the 1, and then tried the update. This time the update went through. I couldn’t see any reason why this one table would behave differently but apparently this table needed the 1 added before the set location would work. One of the quirks of Progress.

If you refresh certain reports and then try to save the report with data you may get the following error:

“Additional parameter values are needed before this report can be saved or viewed with data….”

The error seems to occur only in certain versions of Crystal.  If you see this message and want to save the report with data Crystal will make you choose parameter values again, which means running the report an additional time. With some reports this is a significant waste of time.

I saw this recently with a customer who said it was worth a bit of research if we could prevent the error. The answer I found did solve the problem for that specific report so I thought it was worth sharing. It was caused by a date parameter set to “allow range values”. When we replaced the single “range” parameter with two single-value date parameters (“Begin Date” and “End Date”) the problem went a way. To confirm this I changed the parameter back to a range and the problem reappeared.

This might not work for every situation where this error appears, but it is certainly worth a try if you see this message.