Find it EZ partners with rePORTAL

Sunday 15 October 2017 @ 10:52 pm

Find it EZ (search and documentation) has recently partnered with rePORTAL (scheduling and distribution), which means some new Find it EZ features and some discounts for rePORTAL customers. Find it EZ now directly integrates with the rePORTAL scheduling database. This allows rePORTAL users to extract scheduling information (run times, recipients, destinations, etc.) into user-friendly spreadsheets and also to search and document changes in the underlying database model.

rePORTAL customers can also get a 20% discount on any new license purchase of the latest Find it EZ products, by using the discount code “rePORTAL” at checkout:

Code Search Pro ( Server only )

Dev Surge 365 Enterprise

You can read the full press release here.

SQL Function “cheat sheet” for writing commands

Sunday 8 October 2017 @ 5:44 pm

In recent years, I have been doing lots of SQL command-based reports, which means writing lots of queries in every imaginable flavor of SQL. It was initially frustrating trying to keep the syntax changes straight and I got tired of doing web searches for the same functions over and over.

My solution was to create a “cheat sheet” grid to store my most commonly used SQL functions and the correct syntax to use in each of the database platforms. Once I looked up a function, I would add it to the grid and now the grid answers most of my questions. There are about 40 functions listed and I have most of them completed for the 7 flavors of SQL I see most (plus Crystal Reports formula syntax):

  • Microsoft SQL Server
  • Oracle
  • MySQL
  • Pervasive
  • Progress
  • PostGre SQL
  • MS Access

I have empty columns for Providex and DB2. They are empty because I haven’t had any recent projects on those platforms and don’t have an environment where I can experiment.

I hope some of you will find this grid useful. You can also share the file with others as long as you leave the heading in place. If any of you want to fill in some of the gaps or suggest an improvement, your contributions would be appreciated.  And thanks to John Pelot of Skyward, Inc for filling in many of the progress functions.


Updated comparison of Crystal Reports viewers (2017)

Tuesday 26 September 2017 @ 5:08 pm

You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?

The most cost effective method for letting a user run reports is to install a third-party client-based viewer. They are offered by nine different vendors.  Don’t get sidetracked by the “viewer” that is put out by SAP because that tool won’t refresh reports.  Every viewer in my list allows you to refresh reports.

Every September I compare the features of these viewers and post the results. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (PDF) that shows some of the specifics for comparison, like prices. I have even included a glossary of features in case you aren’t familiar with the terminology.

There are 9 active products in this year’s review and 4 “ghost” products that are mentioned as warnings.  A ghost product has a web site but it hasn’t changed in years and no one responds to repeated requests for information.

The active vendors are:

Crystal Corral by Groff Automation
rptView by Pursuit Technology
cView by Chelsea Technologies
ViewerFX by Origin Software
CrystalKiwi Viewer by CrystalKiwi
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
RTag Report Viewer by RTag
DataLink Viewer by Millet SW

If you have feedback to provide on any of these products, I would love to know what you think.

25% off Find it EZ products

Thursday 21 September 2017 @ 12:26 pm

SAP recently selected Find it EZ products for their first cloud platform partner spotlight. To celebrate, Find it EZ is taking 25% off the price of any new license purchase for Code Search Pro or Dev Surge 365 Enterprise. The offer is currently till October 30.

Here are links to the two product links, but they only work if you are signed in to an SAP account:
Code Search Pro (Desktop or Server editions)
Dev Surge 365 Enterprise

Also note these products may be sold as purchases or subscriptions. For a regular sale the discount is off the purchase price. For subscriptions the discount is on the first year’s license.

Doing a Maximum “horizontally” as opposed to “vertically”

Thursday 14 September 2017 @ 6:17 pm

Most of the time when you see the Maximum () function in a formula, it is to calculate the highest value in a column. It could look like this:

Maximum ( {Svc.LabDate} )

which would calculate the latest lab date in the entire column. Or it could look like this:

Maximum ( {Svc.LabDate} , {Patient.ID) )

which would calulate the latest lab date for each patient’s group of records.

In both cases the Maximum is looking up and down a single column. But there is also a way to use the Maximum() function (and other summary functions) looking across several values in the same row. For example, say that your table had 4 different date fields and you wanted to find the latest date of each row. You could write a formula like this:


This would return the date value that was the greatest (latest) out of the 4 fields. Notice that you need both square brackets around the list of dates (the ‘array’) and then you need parentheses around the square brackets for the function to work. This syntax also works for Minimum, Count, Average, Sum, and several other summary functions. To see the complete list of functions that can be used this way, go into the formula editor and open the function list node labeled ARRAY.

One other note. When you write a formula that use a vertical summary function you can no longer total that field with a vertical summary operation. But if your formula is doing a horizontal summary like the last example above, that formula can still be used in a summary operation.

Free software for the Houston area

Tuesday 5 September 2017 @ 11:14 pm

From the R-Tag web site:

R-Tag is providing free licenses to companies in the Houston area. The offer is valid until the end of September 2017.

The eligible products are: R-Tag Documentation and Search and R-Tag Crystal Reports Data Source updater.

We already have a free Crystal reports viewer and scheduler (R-Tag Community edition), which might be useful too.

R-Tag Documentation and Search and R-Tag Crystal Reports Data Source updater are useful with Crystal reports migration, backup processing and report development and we hope they might be helpful to companies in the Houston area with on-site servers.

If you are in the Houston area and want to take advantage of this offer, please see the R-Tag contact page.

Crystal Reports Language for Notepad++

Sunday 27 August 2017 @ 9:59 pm

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.

Using NotePad++ for formulas or SQL Commands

Thursday 24 August 2017 @ 5:50 pm

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  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.

Set Location in a Progress database

Friday 18 August 2017 @ 10:08 pm

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.

Additional parameter values are needed ….

Sunday 13 August 2017 @ 11:42 pm

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.

«« Previous Posts
Recrystallize Pro

remiCrystal reporting solution