A way to speed up repeated Crystal Reports installations

Sunday 19 April 2020 @ 8:56 pm

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:


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.

Using the Picture() function

Sunday 12 April 2020 @ 8:37 pm

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.

Comparison of desktop-based schedulers (2020 update)

Sunday 29 March 2020 @ 5:39 pm

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

There are 11 active products in the list this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison, including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

The number of business hours between two DateTimes (updated)

Thursday 26 March 2020 @ 10:19 pm

A customer recently wanted to adapt my “Business Hours Between” calculation for their environment. The main challenge was that their business day ends early on Friday. My formula doesn’t currenlty support working days that are different lengths, so they paid me to redo the formula so it would work for them. Once I had their version I decided to take it a bit further and create a new “Business Hours Between” formula for my web site.

This new version lets you separately specify a business start time and business end time for each of the 7 days of the week. It also includes additional logic to deal with events that start and/or end outside the business day (e.g. on a weekend, a holiday or after hours). The formula is now about 70 lines long. Fortunately, you only need to make changes in the first 20 lines or so. Here you can specify:

  • The field to use for the BeginDateTime
  • The field to use for the EndDateTime
  • The Start and End times assigned to each day of the week that you consider a business day
  • The list of Holiday dates which can be for multiple years

The output is a numeric value in minutes which you can use in subtotals and grand totals. You can also write a separate formula to divide this value by 60 to get the value in hours as a decimal.

If you want to show the value in HH:MM format you can use the “Elapsed Time String” formula on my site to convert this value into that format. Remember to multiply this formula’s result by 60 since the input for the “Elapsed Time String” formula is seconds.

If you need help implementing this formula or any of my formulas you can always call to schedule a short consult.

Trouble installing CR 2016

Monday 16 March 2020 @ 12:20 am

I worked with a frustrated customer last Friday. He was having trouble installing Crystal Reports 2016 on a new PC. He wanted me to give it a try.

The first challenge was the error messages, which give no help. The main two are:

Failed to update setup engine executables
Failed to update cache for execution

After some web searching we found we could check the install log.  These errors happened when Crystal tried to move or rename a folder. We found lots of people asking about these same errors going back several years. We even found SAP’s official response. The solutions that worked for some people were:

1) Turning off all firewalls and AV programs **
2) Running the install as an Administrator
3) Changing the permissions on several folders to allow full control.

We tried all of these things and a few others which leads to the second challenge. The download from the SAP web site is an EXE. Before it can do the actual install it has to unpack all the files. This takes about 5-7 minutes which isn’t in itself a problem. But when the install fails you can’t just run setup again because most of the unpacked files are deleted after the failure. Your only option is to run the EXE again and let it unpack all the files … again … every time.  This makes troubleshooting very time consuming***.

I never got to see the last hurdle. My customer said that one time he got further but hit an error that mentioned a blocked port.  Another search  turned up that Crystal 2016 needs to communicate on a specific port to complete the installation. If this port is blocked you slide down the chute back to square one. We never got that far.

I had to punt and give him a link to buy a tech support incident from SAP. He will have to pay to get the product installed. Maybe SAP will be embarrassed enough to refund the incident charge.

I appreciate that installs are complex and that PCs can have infinite variations – but these errors have been around for years. By now they should have made it into the prerequisite test that runs before the install starts. If CR needs port X to be open then the routine should check to make sure that port is open before starting. If you need admin privileges or folder permissions, then it should check to see if it has them.

**Update 3/17:  The customer checked with his IT department and found a layer of AV/Firewall protection that was still active.  Once that was disabled the install went smoothly.   I still think the install routine could be improved, but I can’t blame the mess above on SAP.

***Update 4/26: See this post for a way to get around the need to unpack each time you repeat an install.

Cross-tabs can total formulas that you can’t normally total

Tuesday 10 March 2020 @ 1:44 pm

One of my first 10 blog posts explained why some formulas could be totaled and others could not. Two of the things that prevent a formula from being summarized (totaled) are if the formula itself refers to a subtotal, or if it uses the functions Previous() or Next().

But I was reminded recently that both of these types of formulas can be summarized in a Cross-tab. Take these two formula examples:

// Rebate:
if Sum ({Orders.Order Amount}, {Customer.Customer Name}) > 25000
then {Orders.Order Amount} *.05
else 0

//Days Between Orders:
if {Customer.Customer Name} = Previous({Customer.Customer Name})
then {Orders.Order Date} - Previous ({Orders.Order Date})
else val({@null})

If I wanted to do a grand total of my rebates or an average of the days between orders I wouldn’t be able to use normal summary functions.   Even Crystal running total fields won’t work with these. In most cases people would resort to using variable to accumulate these totals. However, both of these formulas can be summarized using a cross-tab. You could do a simple cross-tab with a single cell to show the grand total and no row or column fields.  Or you could do breakdowns by other fields.

Not only does this save you dealing with variables, but a cross-tab can put these totals on the first page (Report Header), while variables will only be complete on the last page (Report Footer).  One more reason to use my favorite objects.

Charting based on a parameter value

Saturday 29 February 2020 @ 10:51 am

I have had several customers over the years who wanted to add a fixed line to a bar/line chart. This is typically some type of target or standard that they want to compare the actual data. If this value is in the report’s query results, then it is pretty straightforward. But what if the user wants to be prompted to enter the value to be used in the chart? Crystal won’t let you add the parameter directly to the chart. Even if you put the parameter value in a formula, Crystal might not allow that formula in the chart.

The reason is that parameters come before the data is read. This means that they are evaluated “BeforeReadingRecords” by default. Values that come before the data is being read are not eligible for grouping, sorting, totaling or charting. But if you put the parameter in a formula you can change the evaluation time of the formula to “WhileReadingRecords”. This will make it into a column of values. And even though they are all the same, they will be available in the chart expert as a value to be summarized. Something like this:


Then in your chart you can either summarize it by using a Maximum, or checking the “Do Not Summarize” check mark.

If you need to do this and have trouble then call me to schedule a short consult, or Email me your report and explain your requirement.

Using the ‘parent’ section in the Section Expert

Sunday 23 February 2020 @ 7:24 pm

If you have ever done a page break or a suppress condition in Crystal Reports, you have been in the Section Expert. This is where you can change any of the formatting properties of the sections. And if you have ever split a section into subsections, you know that each subsectioin has their own set of properties in the Section Expert.

But users don’t always notice that whenever you split a section into subsections, the parent section still exists in the Section Expert. So when you split the Details section in to Details A and Details B, there is still an item called Details in the list of sections, and this ‘parent’ section has properties that apply to all of the subsections at once. For example:

  1. If you check “Keep Together” in the parent section Crystal will try to keep all the subsections together on a page, and if they won’t fit will move them ALL to a new page.
  2. If you put a suppress condition in the parent section it will suppress ALL of the subsections at once.
  3. If you check “New Page After” in the parent section it will generate a page break after the last detail subsection, even if you rearrange the details.

You might find that this helps simplify formatting sections with subsections.

“New Page After” on group 2 orphans group footer 1

Tuesday 18 February 2020 @ 8:24 am

If you have two group levels and you put a “New Page After” on Group Footer 2, you will find that Group Footer 1 gets orphaned onto it’s own page. To fix this put a “New Page After” on Group Footer 1. Then instead of putting the same check mark into Group Footer 2 you use the following condition on the “New Page After” property:

{Group1.DatabaseField} = Next ({Group1.DatabaseField})

Of course you use your own database field from Group 1 in the formula. This prevents Group 2 from doing a page break on the last record of the group. On that record the next value for Group 1 is different, so the page break is handled by Group 1.

The humble beginning

Monday 10 February 2020 @ 5:56 pm

I now have a copy of the very first version of Crystal Reports – still in the box with shrinkwrap. I had never even seen this version before. This original version of Crystal Reports was released in 1991.  I didn’t even hear about Crystal till 1994.  I did collect all the versions from 3 on but never ran across v1 or v2.  So it was a nice surprise when Dan Smith of Crystal Certified Solutions in Buffalo sent me a copy.  Thanks, Dan.

Next Posts »» «« Previous Posts
Recrystallize Pro

Crystal Reports Server