Archive for March, 2020



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.




Jeff-Net

Recrystallize Pro

Crystal Reports Server