Archive for May, 2020



Server-based scheduler comparison (2020)

Saturday 30 May 2020 @ 9:58 pm

I have just updated my comparison of server-based scheduling tools for 2020. 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.




Highlighting a phrase within a larger field

Wednesday 27 May 2020 @ 1:58 pm

One of my readers shared a solution he recently found for highlighting a specific string within a larger field (like a comment, notes or memo field). To provide an example, I wrote a report against the Xtreme database and highlighted the word “graduate” the first time it occurs in the employee notes field.

There are two parts to his approach. First you write 3 formulas that split the memo field into three separate pieces: the target string, everything before it, and everything after it. Then you reassemble these three pieces by dropping them into a single text object. The target string formula can then be formatted separately so that it stands out within the text.

I used 3 additional formulas to make the process more generic. Here are the formulas I used:

//Target String - replace with your own target
"graduate"

//Target Start - replace Notes field with your Notes field
Instr ({Employee.Notes}, {@Target String})

//Target End
if {@Target Start} = 0
then 1
else {@Target Start} + Length ({@Target String}) -1

//Output Before- replace Notes field with your Notes field
if {@Target Start} <= 1
then ""
else {Employee.Notes} [1 to {@Target Start}-1]

//Output Target - replace Notes field with your Notes field
if {@Target Start} = 0
then ""
else {Employee.Notes} [{@Target Start} to {@Target End}]

//Output After - replace Notes field with your Notes field
{Employee.Notes} [{@Target End}+1 to -1]

The 3 “Output” formulas are dropped into the text object. Make sure the text object is set to “Can Grow”.

The process can be made more sophisticated if the target string varies in length, as long as you can clearly identify the character pattern that marks the beginning and the end of the target string. If you need help with that let me know and we can schedule a short session.

There is also an alternate approach where you embed HTML tags before and after the target string and then use Crystal’s HTML interpretation to change the format at runtime. However, this is limited to the HTML tags that Crystal supports.

Finally, thanks to Doug Weiner at Beacon Legal Software Services for suggesting this post.




Page Header plus Page Footer is too large for the page

Sunday 17 May 2020 @ 10:06 pm

I had a customer call me recently about this error. Usually this error is quick to resolve.  It is almost always caused by a an object that “Can Grow” (e.g. subreport, memo field) in the Page Header. The customer sent the report to me with saved data and it opened fine on my PC. Both sections were empty and both sections were also suppressed. That made no sense to me. I assumed they had sent me a different report by mistake.

So we did a remote session and I confirmed it was the same report. When it opened on their PC the error appeared. After some poking around I went into “Page Setup” to check the page size and noticed that the bottom margin was set to 11 inches. The error message about the PH/PF turned out to be a red herring. The real issue was a combination of the margin setting and the selected printer. Once we entered a reasonable margin setting, the report behaved normally in their environment.

So if you get this error and can’t find anything that would cause it, you have one more place to check.




Repeating the first columns of a cross-tab for each value in the second column

Friday 8 May 2020 @ 11:44 pm

This is better illustrated with pictures. Recently a customer had a Cross-tab that looked like the image on the left below. But they wanted the first column to repeat next to each value in the second column, like the image on the right below.

cross-tab without repeating the first column

I found a relatively simple way to do this:

  1. I created a formula the combined the two column fields into one string, with a dash between them.
  2. I went into the Cross-tab expert and clicked the “Group Options” button for the first column field. I changed it to the new formula.
  3. I then clicked the”Options” tab and checked the option “Customize Group Name Field”.
  4. I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
  5. I went to the the last tab in the Cross-tab expert named “Customize Style” and highlighted the concatenated formula in the “Rows” box.
  6. I checked the option “Suppress Subtotal” (if not already checked) and clicked OK.

The cross-tab then looked like the one on the right.





Recrystallize Pro