I have just updated my comparison of server-based scheduling tools for 2019. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.

There are 11 products on the list this year and a few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.

I recently found a function in a Crystal Report that I hadn’t noticed before. Technically it is an additional function (UFL) but I am pretty sure it has been installed automatically with Crystal Reports for a long time. The file is dated 11/8/2000 so it might have been introduced with Crystal Reports V8.

The function is called ExtractString (). It is designed to locate two character strings within a longer string and return all the characters in between those two strings. I have done something similar using the InStr() function but it is much more complicated. A good example of a use for this is when you have XML tags in the middle of a long memo field and you want to extract the value between two specific tags. Say it looks something like this:

“blah blah blah <price>19.99</price> blah blah blah <price2>29.99</price2> blah blah blah “

To extract the value for price2 you would use the following formula:

ExtractString ( {table.xmlField}, '<price2>', '</price2>' )

You give the function three arguments:

• The field you are searching
• The string that marks the start
• The string that marks the end

If it doesn’t find the start string it returns a blank (even if the end string is there).
If it finds the start string but no end string it returns everything after the start string.
if it finds both the start and the end it returns all the characters between them.
It skips over any end strings that occur before the start string.
If it finds multiple starts or ends it uses the first.

Next time I have to parse XML or do something similar I will use this function and save myself a few steps.

Update 7/4/2021:

I just found out today that this function can return a maximum of 510 characters.  If you think you might need to return more than this you can use this alternative approach.

I have written before about databases that take selection criteria from Crystal and then use the wrong index so that valid records are missing from the results. The solution is to write the criteria so those rules don’t make it into the SQL WHERE clause. Crystal can then apply that criteria locally so it is done correctly.

But today I ran into a similar problem that didn’t have a simple solution. I was creating a report to read Elliot data. Elliot is what used to be called Macola Accounting. We were connecting to a Pervasive SQL DB using an ODBC connection. We were trying to link the Item file to a second instance of the Item file to get a list of components for manufactured items. What we found is that when we joined the component ID from instance one to the part ID in instance two, the results would not return a single match between the two tables.

Looking at the tables separately showed the matching data was there. And when I tried to filter to a single PartID the results would not find that ID. This is when I realized that we had an index problem like the one I described above.

So I looked at the index tabs in the Database Expert and noticed that this table had two red index tabs, meaning there were two fields in the primary index. The tabs were on Item Number and Sequence number. We were linking from a table where there was a component item number but there was not a component sequence number. It appears that Pervasive SQL defaults to using the primary index for ODBC joins, even if the fields you are using for the join don’t completely match the fields in the index. So the link will fail every time. I even unchecked the option “use indexes or server for speed” to see if that would help, but it didn’t have any affect.

We were lucky that the table we were using had an equivalent view. We linked this view to itself and we were finally able to find matching records. I assume this worked because this view, like most views, are not indexed.

I solved another occasional mystery today.

Crystal allows you to put your groups in order based on the summary fields that exist for that group. This feature is in the “Report” menu under the label “Group Sort”. So if you group by customer and subtotal sales for each customer you can put the customers in order based on their sum. You can rank the customers this way in either ascending or descending order.

You are allowed to use any type of summary field for group sorting, and on occasion I have used a summary that is based on a date field. This could be the first (minimum) order date of for each customer or the last (maximum) order date for each customer. I have noticed that sometimes when I rank a group based on one of these date summaries that Ascending seems to behave like Descending or vice versa. For instance I might pick Descending and I expect the groups with the latest dates to be first. Sometimes they are and sometimes they aren’t. Before today I had never taken the time to figure out what was going on.  Now I know.

If your summary is the Maximum of a date field, like the last order date for each customer, then setting the group sort to ascending or descending will behave in the expected way. Ascending will put the groups with the earliest summary dates first and descending will put the groups with the later dates first.

But if your summary is the Minimum of a date field, like the first order date for each customer, then group sorting for that field will work in reverse. Ascending will put the groups with later dates first and descending will put the groups with earliest dates first. To get the groups to go the way you want you just have to pick the opposite direction.

A couple of notes:
1) This doesn’t happen with a minimum summary of numbers or strings, just a minimum of dates.
2) You can get the same summary value as the minimum by doing the Nth smallest (with N = 1). If you use the Nth Smallest summary for group sorting it does the ascending/descending the normal way, not reversed like the minimum function.