The Crystal Reports Underground News - Volume 2020.01
an independent source for
Crystal Reports Information
by Ken Hamady, MS
Contents for January 2020:
** Web based deployment options compared (2020)
** Script errors on the start page and how to customize it
** My library of Crystal Reports materials
** Grouping times into half hour periods
** Save money on rePORTAL
** Let me create your Crystal Reports
** Column breaks in a multi-column report
** Deleting fields without losing (linked) column headings
** Individual Training with an expert.
** PDF problems with Viewpoint's Spectrum Software
** Sage 50 GetPeach() functions in Windows 10
Gems from the Archives
Customizing the Crystal Reports "Splash" image (2009.10)
Formula for today's date in contract format (2009.10)
Web based deployment
options compared (2020)
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.
Script errors on the
start page and how to customize it
It has been 5 years since I first wrote about the script errors that
affect the Crystal Reports start page. This issue comes up again
periodically and several customers have had these issues recently.
So if you are having issues with the start page here are the
articles that should help:
https://kenhamady.com/cru/archives/2615
https://kenhamady.com/cru/archives/147
The fix is pretty simple and
involves renaming two HTML pages in one of Crystal's application
folders. This prevents Crystal start page from trying to access the
web.
Since the start page is written in HTML I decided to have a
look at how it worked. I was hoping to modify the page to show links
to my most frequently used reports. After much experimenting I found
a way to create hyperlinks that would open reports based on
specified path and file name. The key was to 'borrow' the custom
javascript function that is used to open your most recently used
reports. The function is called fncrOpenReport.
So I edited the HTML in the start page and put in a few lines like
this:
<A href="javascript:fncrOpenReport('C:/sample.rpt')">Sample Rpt</A>
The next time I started
Crystal the start page included a section of new hyperlinks. The one
above appears as the words "Sample Rpt". When I click that hyperlink
it opens the RPT specified in the fncrOpenReport function. This
section works much like the "My Recent Reports" section but these
reports won't roll off the page as other reports are opened.
The only odd part about using this function is that reports opened
this way cannot use "save as" until after you click "save". If
you click "save as" without having clicked "save" the dialogue just
doesn't open.
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.
Grouping times into
half hour periods
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')
Save money on rePORTAL
The developers of
rePORTAL have just released an upgrade (v6.4) of their
web portal and scheduling software. They are offering a 10% discount
on all server licenses between now and the end of January 2020.
Contact them
through their website if you are interested in trying or
buying their software.. You can read more about the features of
rePORTAL in my these annual comparisons:
http://kenhamady.com/cru/comparisons/server-based-deployment-options
http://kenhamady.com/cru/comparisons/server-based-scheduling-engines
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.
Column breaks in a
multi-column report
Crystal allows you to have multiple columns on a page. This is handy
when you have a narrow list or a label and you want to pack more
data on each page. To set up multiple columns you go to the section
expert, highlight the 'details' section and check the option to
'format groups with multiple columns'. This exposes a tab on the
right that is labeled "Layout". Here you can set the width of the
column, the space between columns, and if the columns should go
across then down (filling the top of the page first) or go down then
across (filling the left side first).
Here you can also determine if the group headers and footers are
part of the columns or if they go across the entire page and are
separate. Usually, you want the groups to be included when you are
using "down then across" and not when you are using "across then
down".
If you are using "down then across" and your groups are part of the
columns you might want to have each group start in a new column.
Crystal has a specific feature when you want a page break after a
group, but not when you want a column break after a group. My
workaround is to create a very thin group footer and then set it to
"print at bottom of page". This will force the next group to be at
the top of a column.
Since long groups might start on one page and continue on the next,
you should probably also set the group option to "repeat group
header on each page". This way the group spilling on to the next
page has a label to identify the group.
This method isn't perfect. You might occasionally get an empty
column if the records fill the column exactly. Or you might want to
line up the top of each group's second column with the top of its
first column, or maybe even repeat the headings with each column.
For help with these issues see Expert Techniques Volume 2.
Deleting fields
without losing (linked) column headings
Whenever you drag a new database or formula field onto the details
band, Crystal will add a column heading for you, automatically. This
heading is just a text object but it will stay internally linked to
that field. If you move the field horizontally, or change its width,
the heading will adjust to match. If you delete the field, the
heading disappears as well.
This can sometimes present a challenge. For example, when you have
formatted and aligned column headings and you need to replace the
fields below them. When you delete the original fields the headings
will also be deleted and then you have to recreate them and reformat
them. I was in this situation twice in the past few days. In
one case I had to replace a group of tables with a SQL command, in
the other I replaced a SQL command with a subreport. In both cases
the database fields on the report had to be replaced. And as soon as
I deleted the original fields I saw that the headings also went
away. I didn't want to recreate the headings to match the old ones,
so I hit undo and did some experimenting. I learned two things
that allowed me to keep my original headings in place even after the
original fields are deleted:
If you copy the headings and paste a second copy of them
somewhere else on the report, Crystal will only delete one set.
If the new headings are in a separate section Crystal will
delete the headings in the higher section.
So my new approach for this scenario is to:
Split the section that contains the headings to create a new
temporary subsection.
Drag the new subsection to be above the original section.
Copy the headings and paste the copies into the new blank
section.
Insert the new fields into the new subsection (so they won't
create new headings)
Use the format painter to format the new fields based on the
format of the originals.
Use the menu (format > make same size) to size the new fields
to match the originals.
Delete the original fields, which should delete the new
headings in the higher section.
Move the new fields to the original section and align them
with the original headings.
Delete the temporary subsection.
This process adds a few extra steps, but it keeps your original
headings exactly as they were and allows the new fields to look
exactly like the ones that they replaced. In cases where the
formatting requirements are very specific, these steps can actually
save time.
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.
PDF problems with
Viewpoint's Spectrum Software
Any time Crystal Reports exports to PDF there is a slight reduction
in font size. So when a customer complained that their fonts were
exporting inconsistently to PDF, I figured it would center around
this topic. But, what I found was something different. The
report was being run from within Viewpoint's Spectrum Software for
construction. The report looked fine if we exported it to RPT format
and opened it in the Crystal Reports designer, but the PDF version
had inconsistent font sizes.
One example was a large block of text with four paragraphs. It was
all formatted with the same font and the same size in Crystal. In
PDF format the first paragraph was in one size and all the other
paragraphs were in a different size. I cut the first paragraph out
to make it a separate text object and left the remaining three
paragraphs in one text object. But the font change just moved
down to the next paragraph break. I had to make each paragraph
it's own text object to avoid font changes.
A second problem was that text objects and field objects would end
up being reduced by different amounts, even though they started out
the same size. For example, just before the text objects was a
database memo field that was supposed be the same font size. If I
started them both at font size 9.5 in the report, the memo field
would end up at 8.5 while the text object would end up at 8.
If reduced the memo field to 9 the font would end up as 7.5. I tried
several other combinations and couldn't find one where the text
objects and fields would end up the same size in the PDF.
The odd part was that I couldn't replicate any of these problems in
my local environment. I opened their rpt file saved with their
data in my Crystal Reports designer and it exported just fine. I
even tried it in Crystal v10 which is 15 years old and uses Adobe
4.0 format. It still exported here with consistent font
sizes. So it sounds like this might have something to do with the
runtime environment associated with Spectrum.
Conclusion: If you are using Viewpoint's Spectrum Software, watch
out for PDF font sizes. If you need help with these issues,
give me a call.
Sage 50 GetPeach()
functions in Windows 10
For some reason the combination of Sage 50 (PeachTree) and Windows
10 generated many calls from customers. Most of these calls
were for reports that used the special GetPeach() functions and that
stopped working. These functions have come with Sage/PeachTree
for years. I was told by several Sage consultants that these
functions would no longer work in the current versions of Sage 50.
But one of my customers ended up solving the problem on his own and
has allowed me to share what he found (he didn't want to be cited).
So here is how he got these functions working again:
- A system PATH variable must be assigned to C:\Program Files (x86)\Sage\Peachtree. This folder contains the DDFs (data dictionary files) for the interface between Peachtree and Crystal Reports and other special functions. Without these DDFs, any effort to run a report containing GetPeach() functions will fail with the error message "The Specified Module Could Not Be Found".
- In a single user
environment U2LPeach.dll and the associated .ini file
(U2LPeach.ini) must be included in the folder
C:\Windows\Crystal. This is the default location the files are
placed by Sage 50 when data functions are updated.
- If operating in a
terminal services environment, U2LPeach.dll and the associated
.ini file (U2LPeach.ini) must be included in
C:\Users\\Windows\Crystal.
- When U2LPeach.dll is
loaded correctly, the .dll will appear in the list of dlls
loaded by Crystal Reports. This list can be found by going to
"Help > About Crystal Reports" and clicking "more information".
Alternately you can edit any formula and look in the additional
functions node for the GetPeach functions.
If you are having problems with Sage 50, and the above doesn't help you, let me know. I have several colleagues who are Sage 50 specialists so we should be able to resolve it for you.
Gems from the Archives
Customizing the Crystal Reports "Splash" image (2009.10)
Formula for today's date in contract format (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
