I have written several articles about the usefulness of Notepad++. I often use it to write or test complex formulas and SQL commands. I have also used it to see the affect of a logic change, by comparing the output (text) of the report before and after a change. I have even created a custom language interpreter so that Crystal formulas written in Notepad++ look better than they do in Crystal’s formula editor.

This week I found a new use that I should have seen before. A customer sent me two different versions of a complex report and we weren’t sure which one to use. We needed to see the differences between the formulas in the two reports. So I exported both reports to the format “Report Definition”.  This export creates a text file that lists all of the major settings of the report, lists the objects in each section, and includes the text of all the formula fields that are actively being used by the report.

Once I had the two Report Definitions, I opened them up in Notepad++ and used the “Compare” add-in. This took me straight to the handful of formula differences. It was easy to show these differences to the customer to see which version he wanted to use.

If you want to try out the Notepad++ with the Compare plug-in you will need to start by downloading Notepad++ version 7.4.2.  This is the latest version that includes the plug-in manager.  Once you have Notepadd++ installed you can use the plug-in manager to install the Compare plug-in.  After that you can update to the latest version of Notepad++.

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 several articles about using Notepadd++ for writing long formulas or SQL statements. I just found out that one of the features I use in Notepad++ has been (partially) available in Crystal Reports forever.  I never noticed. The feature is called “column mode” and allows you to select text in a column without selecting the entire row.

Lets say you start a formula with DateTime variables like this:

WhilePrintingRecords;
DateTimeVar DateA;
DateTimeVar DateB;
DateTimeVar DateC;
DateTimeVar DateD;
DateTimeVar DateE;

Then you realize that you want them to be Date instead of DateTime. You can highlight a “column” made up of the word “Time” on all 5 rows at once, and then hit delete. To select a column you hold down the ALT key and then click the mouse in the upper left corner of rectangle and drag to the lower right corner. In this case you would start just before the “T” in Time in the first row, and drag down and across until your cursor was just after the “e” in Time in the last row. By using the ALT key you will highlight a rectangle of 4 characters across and 5 rows down. Then you hit delete and just those 20 characters are deleted.

You can also copy and paste a rectangle. Say you have to write a formula that is something like this:

If month({Trans.Date}) = 01 then {Balance.Pd01} else
If month({Trans.Date}) = 02 then {Balance.Pd02} else
If month({Trans.Date}) = 03 then {Balance.Pd03} else
If month({Trans.Date}) = 04 then {Balance.Pd04} else
If month({Trans.Date}) = 05 then {Balance.Pd05} else
If month({Trans.Date}) = 06 then {Balance.Pd06} else
If month({Trans.Date}) = 07 then {Balance.Pd07} else
If month({Trans.Date}) = 08 then {Balance.Pd08} else
If month({Trans.Date}) = 09 then {Balance.Pd09} else
If month({Trans.Date}) = 10 then {Balance.Pd10} else
If month({Trans.Date}) = 11 then {Balance.Pd11} else
If month({Trans.Date}) = 12 then {Balance.Pd12}

Normally I would start by typing the first row and then copy it 11 more times. Then I would change each row to use a different number from 1 to 12.  Once I have changed the column of values after the ‘=’ sign I could copy that 2-digit column and past it over the values in the other 2-digit column in one step.

To do this you select the 2 digit column as a rectangle using the ALT key. You can right clock in the column to copy (or use Ctrl-C). Then you select the other 2-digit column and right-click to paste (or use Ctrl-V).

Note, in Crystal you should select these rectangles starting in the upper left corner, especially if you plan to copy and paste.  You can get inconsistent results if you start in one of the other corners.

NotePad++ has a much more sophisticated column mode, allowing you to:

1. Paste a single word or character value into multiple rows at once.
2. Select a column and start typing. The new text is added to ALL the rows at the same time.
3. Use Shift-Alt to mark the column using arrow keys , instead of the mouse.
4. Select the column of text starting in any corner.

Unfortunately, these don’t work in Crystal.

I often work with large and complex reports. Sometimes making a minor change can have unexpected consequences. I like to be able to see that the only things that changed are the things I intended to change. If the report is long or dense it can be a challenge to identify changes. But I recently worked out a relatively simple way to identify all of the values on a report that have changed as the result of my formula changes. I use the “compare” add-on in NotePadd ++.

So the first thing I do is refresh the ‘before’ report so I know that I have up to the minute data. Then I export the entire report into TXT format, creating the file before.txt. Then I make my change(s) and export the entire report a second time into TXT format, creating the file after.txt. Now I open these two files in open NotePad++ and run a compare.  All of the differences will be highlighted and it is easy to see all the changes.

For example, one of my upcoming assignments is to simplify the formulas in a complex report without changing the output. So my plan is to use this method after each round of changes. Since nothing should be different, any changes I find in the compare process will be a sign of a mistake.

If you haven’t tried NP++ (which is free) you can read more about it here, including the best place to download it (see update below).

Update – I just tried to install NP++ for a customer and I couldn’t add the Compare plugin because the plugin manager was completely missing. After some research I found that the developer of the plugin manager module added a sponsorship image (an ad) and so the plugin manager is no longer included in new downloads. If you already have the plugin manager upgrades will not affect you, since this only affects new installs.  The developer of NP++  is working on a replacement plugin manager.

Since Ninite always installs the latest version I recommend that, for now, you download NP++ version 7.4.2 directly from the NP++ site.  This was the last version that came with the plugin manager. It should automatically update itself to the latest version of NP++.

I do lots of work using GoToMeeting and often the screen I see is reduced in size, making things hard to read. It can be frustrating when trying to highlight a word without including characters on either side. So I have developed the habit of using double-clicks to select words. I find that not everyone knows this trick.

When selecting normal text, like in MS Word or in a browser, you can double click on a word to select the entire word. In many applications the word selected also includes an extra space on the end. This allows you to move the word to another part of the sentence without having to add and delete spaces.  (In a few applications you can even use a triple-click, which selects an entire paragraph. This works in most browsers and MS Word and Wordpad. )

I also use double-clicks when working in the Crystal Reports formula editor. If you double-click on literal word (within quotes), Crystal will select that word (but no trailing spaces). If you double click inside a field name Crystal will select either the table name (before the period) or the field name (after the period).  If you double-click on the name of a variable or function, Crystal will select that name with no spaces.
I find this very helpful when copying and pasting values.

I also use Notepad++ to write both Crystal formulas and SQL commands. When you double click a word in Notepad++ it not only selects that word and highlights it; it also highlights every other instance of that word in the document. This makes it easy to see where a specific table, field or value is being used.

I wrote two posts last month about finding where a field is used. I got to test these methods recently while helping a customer clean up a report. There were a handful of formulas shown as “in use” that we needed to delete. So I thought this would make a good demo of the new features.

First, we checked all of the options in the fly-out menus and found most of the formulas so we could replace them with other fields and delete them. Then we used the “FIND” method in design mode to see if any were sitting on the report. But there was one field still in use that didn’t show up in either method.

Next I tried a very old method. I exported the report to a “Report Definition File” and opened the exported file in NotePad. This usually works, but not this time.  I was surprised to find that there were no references to that formula anywhere in the report definition.

So now I was curious.  Where could you use a field that wouldn’t show up anywhere? So I started chopping:

• I saved the report under a new name
• I deleted every object from the report layout.
• I deleted all the groups.
• I deleted any extra sections so I was down to the basic 5.
• For each of the 5 basic sections I inserted a new section below it and then deleted the original section. At this point there would be no conditional formatting formulas.
• Then I deleted all of the formula fields. But I still couldn’t delete the problem formula because it was still “in use”.

So I looked for other things to delete. After deleting the parameters I noticed that there was a lone running total field. The running total wasn’t being used, but as soon as I deleted it the problem formula was no longer marked as in use. So I did an “undo” to check the running total and found that my problem formula was the summary field of the running total. So I did a bit of testing and here is what I learned:

If you do a regular summary of a formula (Insert > Summary) that formula will show up in the Report Definition export.

Also, if you use a formula field in one of the the condition formulas of a running total it will show up in the Report Definition and also in the “Find in formulas” fly-out.

But if you use the formula as the summary field in a running total, that formula will NOT show up in the Report Definition or any of the fly-outs. As far as I know there is no way to find that using Crystal Reports without checking every running total (or going to a third party tool). But now I know one more place to check.

I try to avoid copying text directly from a formatted document (Word, PDF, etc) directly into a Crystal Reports text object. While Crystal can accept many of the formatting characters from other applications, they can sometimes cause unpredictable behavior.

I recently had a customer show me a text object in Crystal that refused to display the first few characters of the text. We would see these character when the text object was in ‘edit’ mode but they would disappear when we exited and went to preview. We couldn’t find the formatting characters that were causing this behavior, and if we copied the text to a new text object the behavior followed. We finally solved it by copying the text to Notepad, and then copying from Notepad to a new text object. This stripped out all the hidden formatting.

So now whenever I have to transfer formatted text to a report, I first paste it into a simple text editor like Notepad. Then I will copy the text and paste that into Crystal. I can then add any needed formatting within Crystal Reports.