an independent source for
Crystal Reports Information
by Ken Hamady, MS
Contents for March 2020:
** Comparison of desktop-based schedulers (2020 update)
** The number of business hours between two DateTimes (updated)
** My library of Crystal Reports materials
** Cross-tabs can total formulas that you can't normally total
** Charting based on a parameter value
** Let me create your Crystal Reports
** "New Page After" on group 2 orphans Group Footer 1
** Using the "parent" section in the Section Expert
** Individual Training with an expert.
** The humble beginning
** Trouble installing CR 2016
Gems from the Archives
Formula for today's date in contract format (2009.10)
Customizing Crystal Reports Start or Splash screen
(2009.10)
Comparison of
desktop-based schedulers (2020 update)
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)
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 currently 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.
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.
Cross-tabs can
total formulas that you can't normally total
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
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.
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.
"New Page After"
on group 2 orphans group footer 1
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.
Using the
"parent" section in the Section Expert
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:
- 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.
- If you put a suppress
condition in the parent section it will suppress ALL of the
subsections at once.
- 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.
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.
The humble
beginning
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.
Trouble
installing CR 2016
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.
Gems from the
Archives
Formula for today's date in contract format (2009.10)
Customizing Crystal Reports Start or Splash screen
(2009.10)
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