The Crystal Reports Underground News - Volume 2020.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2020:
** Comparison of server-based schedulers (2020 update)
** Repeating the first columns of a cross-tab for each value in the second column
** My library of Crystal Reports materials
** Highlighting a phrase within a larger field
** Using the Picture() function
** Let me create your Crystal Reports
** Reading an Excel column as a true DateTime rather than a string.
** The Crystal Reports label wizard
** Individual Training with an expert.
** Page Header plus Page Footer is too large for the page
** A way to speed up repeated Crystal Reports installations
Gems from the Archives
Using negative subscripts (2010.01)
"ALT" key temporarily releases guidelines and grid (2010.01)
Comparison of server-based schedulers (2020 update)
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.
Repeating the first columns of a cross-tab for each value in the second column
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. (If the images don't appear, see this link)
I found a relatively simple way to do this:
- I created a formula the combined the two column fields into one string, with a dash between them.
- 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.
- I then clicked the"Options" tab and checked the option "Customize Group Name Field".
- I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
- I went to the the last tab in the Cross-tab expert named "Customize Style" and highlighted the concatenated formula in the "Rows" box.
- I checked the option "Suppress Subtotal" (if not already checked) and clicked OK.
The cross-tab then looked like the one on the right.
My online library of Crystal Reports materials:
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the "Library" page of my site.
Highlighting a phrase within a larger field
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.
Using the Picture() function
One of my former students sent me a question. He wanted to know the easiest way to add the dashes back into a Social Security number when it is stored as a 9 digit string. I was going to have him parse it into 3 pieces using substring brackets and then assemble those around the dashes. Then I remembered the picture() function. I don’t use it much, but it has been on my potential blog topic list for a while.
The picture() function allows you to insert characters and punctuation into a string so that it has a specific format. The two most common uses I have seen are adding the parentheses and dash back to the US Phone number: “(000)000-0000” or adding the dashes back to a SSN: “000-00-0000”. It will work with any consistent pattern.
To use the picture function you give it two arguments. The first is the field or string you want to format and the second is a pattern string. The pattern string has an ‘x’ for every character in the field. You then insert the other characters among the ‘x’s at the appropriate places. Crystal will insert those characters automatically at those positiions.
The picture function for the Phone Number would look like this:
Picture({table.phone}, '(xxx)xxx-xxxx')
The picture function for the SSN would look like this:
Picture( {table.SocSec} , 'xxx-xx-xxxx')
Note, that if the field is empty or short the formula will still show all of the inserted characters.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Reading an Excel column as a true DateTime rather than a string
I have written before about data type changes in Excel. One article explained how to convert a column of numbers into character values so that they can link correctly to another column of character values.
Today I was asked how to do something similar with dates. Two spreadsheets were being linked on a date field, and Crystal was reading one field as a true date and one as a string date. I found a simple way to get Crystal to recognize the string as a date. I created another column that was simply the Date String column plus zero [e.g. =A3 + 0]. This turned the new column into a number representing the date. Then I formatted that column as dates and that allowed Crystal to recognize the new column as a date.
It surprised me to find that I had to format the field as a date. When it comes to dealing with strings and numbers the formatting is usually irrelevant, but apparently with dates the format is important. When I tried to skip formatting the column as a date, Crystal read the new column as numeric values.
The Crystal Reports label wizard
Every version of Crystal Reports in the past 20 years comes with a neat little label wizard that allows you to automatically set up about 30 common Avery label sizes. But there are a few things you need to know to avoid problems.
Tip #1) A report created using the label wizard can't be changed to a regular report, and a regular report can't have one of the label sizes automatically applied. You have to decide to use the label wizard when you first create the report. The wizard is found under the menu options "File > New > Mailing Label Wizard".
Tip #2) The wizard lets you pick the fields you want, but it will put each field you select on its own line. So, if you create an address label and add the fields City-State-Zip, the wizard will put those fields on 3 separate lines. I add only the first field of each line while I am in the wizard. Then when I preview the report I can add the other fields, or write a formula to combine all of the fields that you need.
Tip #3) This label wizard always puts the label fields on subsections of the details band. If you want to print one label per group, don't try to move the fields to group headers or you will make lots of unnecessary work. Instead you can make the detail labels appear as if they are at the group level by only printing the first detail label of each group.
To do this you:
- Add the group to the report
- Suppress both the Group Header and the Group Footer.
- Create a running total field that counts the records in each group(reset 'on change of group').
- Suppress the Detail sections or the Detail parent section with a condition that says:
{#running total} > 1
Only the first record (label) of each group will print. This allows you to use the wizard to get the layout automatically, but still print one record per group.
Individual Training with an expert
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
Page Header plus Page Footer is too large for the page
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.
A way to speed up repeated Crystal Reports installations
Last month I wrote about a difficult time I had installing Crystal Reports. One of my complaints was that if the install fails, you have to start over - including waiting 5-7 minutes while the setup 'unpacks' all the files. It seems like there should be a way to keep the files unpacked when an install has to be repeated.
Well SAP doesn't provide an official way to do that but one of my readers (who apparently does lots of CR installs) shared his method. Here are his steps:
1. Run the install .exe.
2. Wait until everything has been unpacked. If you aren't sure, wait until it asks for the license key. Then go to this folder, substituting your user name in the path:
C:\users\<username>\AppData\Local\Temp
Look for a folder in Temp that starts with "RARSFX".
NOTE: AppData is a hidden folder, so you'll have to set Explorer to view hidden items.
3. If there's more than one "RARSFX" folder, delete all of them except the most recent one. These are failed installs that didn't get deleted correctly.
4. Copy the remaining folder somewhere else. You cannot cut and paste because the installer has those files open. You might want to give the folder copy a more meaningful name.
5. For subsequent installs, go to this new folder and run setup.exe.
So if you are troubleshooting a balky install, or if you have to install CR on several different workstations, the steps above will speed up the iterations. And, thanks to Dell Stinnett-Christy, a senior manager at Protiviti, for sharing this method.
Gems from the Archives
Using negative subscripts (2010.01)
"ALT" key temporarily releases guidelines and grid (2010.01)
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2020 by Ken Hamady
All rights reserved - Republishing this material requires written permission