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.





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:

WhileReadingRecords;
{?YourParameter}

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.





Web based deployment options compared (2020)

Wednesday 29 January 2020 @ 12:40 pm

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.

I have created a page on my blog that lists and compares these products, and I update it every January with info from the vendors. This year the list features 10 products:

Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Server – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
Bezlio – a SaaS Web viewer
Report Launch – a bridge between BO server products and server based applications
RapidStack – Web Portal service built around Business Objects Enterprise

The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (xls) that shows some of the specifics for comparison, including prices.

If you have any feedback to share on these tools I would be happy to hear from you.





Grouping times into half hour periods

Saturday 25 January 2020 @ 9:37 pm

When you create a group in Crystal using a Date field you get grouping options for different periods like by day, week or month. If the field is a Time or DateTime you get options like by Hour, by minute or AM/PM. But there isn’t an automatic option to group on the half hour.

Below is a formula that will divide all the time values in an hour into two groups. For example, it will turn all time values between 6:00 and 6:29 into 06:00. It will, and will turn all time values between 6:30 and 6:59 into 06:30. You can use this for creating groups, charts or cross-tabs.  Note that the leading zero will keep the groups in the chronological order.

Totext (Hour({@time}),'00') &
(if Minute({@time}) < 30
then ':00'
else ':30')





«« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server