Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2003.08 (August 2003)
an independent source
for Crystal Reports Information by Ken Hamady
Contents for August, 2003:
**
NEW
- Expert Techniques for Crystal Reports - (my favorite Tips and Tricks)
** Some new details about SQL Server Reporting Services
** "..used terms I could understand"
** Crystal Decisions bought by Business Objects
** Crystal Enterprise Add-In for Microsoft Excel
** The Expert's Guide to Crystal Reports Formulas
** Visual Cut now exports to PDF with Bookmarks
** New Search feature on Crystal's website
** Chart bug when using "Side by Side" install of Crystal
** Using Mozilla to avoid Pop-Up windows
** "..solved my problem within 2 minutes"
** Read back issues at http://www.kenhamady.com/news.html
NEW - Expert Techniques for Crystal Reports - (my favorite Tips and Tricks):
I
recently
prepared a few Tips and Tricks for a User Group presentation. The
presentation
received such an enthusiastic response that I decided to publish a
small
book of my favorite techniques. This month I am releasing a
collection
of 30 articles, called Expert Techniques for Crystal Reports (Volume
1).
Some of these articles are expanded versions of my favorite newsletter
items.
Others are brand new items inspired by user questions or
problems.
Each is now illustrated with a sample report.
The material is in PDF format, and includes and 32 sample reports, each
saved
with data. The techniques can almost all be used in versions 7
through
9. The sample reports are saved in version 7 format so that
everyone
can open them. While I wrote Volume 1, I came up with another 25
techniques
and the list keeps growing. By the end of September I hope to
have
Volume 2 ready. Each volume is only $19 and can be downloaded
immediately
from my web site.
Here is the list of articles in Volume I:
Cross-tabs:
1. Page numbers for Cross-tabs placed in the Report Header
2. Titles on Cross-tabs that spills onto Virtual Pages
3. Labeling multiple Summary Field in a single Cross-tab row
4. Using Cross-Tabs to create running totals (for advanced users)
Subreports:
5. Marking subreports so they don't get lost
6. Finding or listing the subreports in a report
7. The Stealth Subreport - runs but is invisible.
8. Why shared variables pick up the value from the prior subreport
9. Why Shared variables repeat when a subreport is blank
10. Keeping the Page Header from a subreport
Parameters:
11. How to get a parameter to select all, including Null values
12. Printing user selected values for Multiple or Range Value Parameters
13. Sort/Group in the order that a Multiple Value Parameter was entered
14. Selecting Group/Sort fields with a parameter
15. Adding or Deleting group levels with a parameter (Group Cloaking)
16. Selecting Ascending or Descending order based on a parameter
17. Select the Summary Operation used in a TopN report based on a
parameter
18. Letting the user choose the number (the "N") in a TopN report
19. Finding where a stray parameter (or formula) is being used in a
report
20. Setting the expiration date for a report
Formulas:
21. Forcing a formula to return a Null value
22. Printing symbols, like check marks, from formulas
23. The advantages to grouping on a formula
24. How to prevent the "divide by zero" error
25. Adding up the Distinct Counts of several Groups, to get a grand
total
26. Printing "Continued on next page" or "Continued from previous page"
27. The situations when a "TopN" can be based on the result of a formula
28. How to display only the duplicate records
29. How to display Gaps in a sequence of Numbers or Dates
30. Finding unmatched records in another table
See the following link for more details, or to purchase this material.
http://www.kenhamady.com/expert.html
Some new details about SQL Server Reporting Services:
Those
of you who are already members of PASS, the Professional Association
for
SQL Server, will want to check out the recent online chat regarding SQL
Server
Reporting Services (dated 6/25/2003). Several product managers
from
Microsoft took questions from users, and there were a few interesting
answers
that filled in the gaps. So here is my updated take on SRSS:
SQL Server Reporting Services (SSRS) is taking aim at "server-based
managed
reporting products" which sounds like Crystal Enterprise, Business
Objects,
Cognos and a few others. The best quote in the recent chat was:
Q. How is it different from Crystal Enterprise?
A. It will be from Microsoft. :)
The 3 core components of SSRS will include a Graphical Report Designer,
a
Report Server, and a Report Server Database.
The Report Designer will only be available within Visual Studio
.NET.
(We will have to see if Visual Studio will have room for both Crystal
Reports
and SSRS). SSRS Reports will be stored in XML and will connect to
data
using XML, OLE DB, ODBC, Oracle, SQL, and any other .NET data
provider.
The reports will output to XML, HTML, PDF, Excel, RTF among
others.
The real surprise to me was that the report designer will support
presentation
quality features like grouping, snaking columns, cross-tabs and a full
interactive
charting library with drill through capabilities.
However, there is no plan to release an Ad Hoc report designer, and no
ready-made
web based designer. You would have to write your own web based
application
to give your users ad hoc capabilities.
The Report Server is a web service that runs within ASP.Net and
IIS.
It is URL addressable and provides for scheduling and caching
reports.
End users only need IE to view the reports.
The Report Server Database handles security, scheduling, cache, history
and
session information.
This sounds enough like Crystal Enterprise that I expect some pricing
pressure
on Crystal Decisions and the entire Enterprise BI market.
Microsoft
has not formally announced licensing and pricing for SSRS, but several
of
us have gotten the impression that it might simply be part of the SQL
Server
license. They don't seem interested at all in the stand alone Ad
Hoc
market, or even in the "fat client" based application market.
That
may be where they leave room for Crystal Reports.
If you are not a member of PASS you can use the following link to read
other
publicly available articles on SSRS:
http://www.kenhamady.com/rosetta.html
"..used terms I could understand" - Intro Class - July 2003:
I
don't
have any unhappy customers. If you want to take one of my
September
classes in Frederick, MD or Leesburg, VA you can use the site below to
review
dates, course outlines and registration info:
http://www.kenhamady.com/public.html
Or better yet - schedule a private class at your office, with your
data.
This is my favorite way to teach, and I charge about half of what
Crystal
charges for on-site classes. I have personally taught over 1500
satisfied
students from Tampa to Anchorage so don't assume that you are too far
away.
Call for more information at (540) 338-0194.
Crystal Decisions bought by Business Objects:
For
those
of you who missed the big news this month, Crystal Decisions was
purchased
by Business Objects. This purchase (or merger) combines two very
strong
players in the BI market. As you may have read in my prior
newsletters,
Crystal Decisions had already filed the paperwork to go public.
Since
this isn't the ideal market for IPOs, the offer from Business Objects
made
sense. These two companies, when combined, make a solid defense
against
the Microsoft venture into the BI Market. Hopefully Business
Objects
won't make the same types of mistakes that Seagate made when they owned
Crystal,
like trying to rebrand the products under a new name.
Crystal Enterprise Add-In for Excel
This
brand new feature allows Crystal Enterprise users to insert a live
report
into a spreadsheet, and then use the report data in spreadsheet
calculations.
Each time the report is refreshed, the calculations are also
refreshed.
This allows end users to work with live report data in the more
familiar
environment of a spreadsheet. The report data can be refreshed
manually
or automatically using event triggers or a fixed schedule. All of
the
formatting and calculations remain intact when report is refreshed.
I have read that you can even add multiple reports into one Excel
workbook
and you can combine data from different reports and even different data
sources
in your calculations. While I haven't been able to confirm this,
it
would seem to to eliminate the need for many subreports and shared
variables.
The spreadsheet could be your container.
Once a spreadsheet is complete, you can publish it into Crystal
Enterprise
directly from Excel. The spreadsheet would now be available to
others
in the Crystal Enterprise environment, depending on the user's rights.
Of course, there is a catch. This feature is only available at
the
highest end of the Crystal Enterprise product line. You need
either
CE Premium Edition (roughly 40% above the price of CE Pro) or you need
CE
Pro with the "Smart Reporting" option (roughly 20% above the price of
CE
Pro).
The Expert's Guide to Crystal Reports Formulas:
Learn
to write and understand formulas like a Pro with "The Expert's Guide to
Crystal
Reports Formulas". I take you from the basics all the way through
variables,
arrays and looping logic. I explain HOW, WHY and WHEN to use
these
techniques and provide tips and tricks that are rarely taught in
classes.
The guide includes 28 annotated reports and 45 no-nonsense pages of
detailed
explanations. The cost is only $36 and you can download it
immediately
with most Credit Cards. For more details, visit my website at:
http://www.kenhamady.com/form00.html
Visual Cut now exports PDF Bookmarks:
Ido
Millet
at Millet Software has added another nifty feature to his Visual Cut
report
manager. You can now export to PDF and convert your report's
group
tree into the bookmark tree in your PDF document. See my LINKS
page
for a link to the Visual Cut web site, and all of the other available
viewers,
schedulers and managers.
New search feature on Crystal's website:
Crystal
has now consolidated their website search feature to allow you to
search
all documents related to a topic. The search now covers Knowledge
Base
Articles, Technical Documents and Downloads in one pass. For a
link
to the Crystal Reports Support page, see the LINKS page of my web site.
Chart bug when using "Side by Side" install:
In
most
cases, CD does not recommend that you install multiple versions of
Crystal
on the same PC. This does not mean that it won't work, but CD has
never
wanted to support the annoying DLL conflicts that might arise.
However,
CRv9 is designed so that it can be installed and run side by side with
a
prior version. It accomplishes this by renaming all of the DLL
files
so that they do not conflict with the CRv8 DLL files.
Apparently they made an exception for the charting library DLL
files.
The CRv9 chart DLL files are named the same as the CRv8 chart
DLLs.
So, if write a CRv8 report with a chart, you actually end up using the
v9
dlls. This won't cause a problem on your PC, but if you install
that
report on another PC that only has the CRv8 chart DLLs, the report may
not
run correctly. To read the details and the solutions, read the
Knowledge
Base article c2013453. If you go to their new search utility and
search
on this article ID, it will take you directly to that article.
A Free Browser with no Pop-Up windows:
This
isn't a Crystal Reports tip, but I think that some of you might be
interested.
As you know, Internet Explorer is not the only free browser you can
use.
My choice for the past year has been Mozilla. Mozilla is
virtually
identical to Netscape (actually Netscape is just a commercial version
of
Mozilla). However, Mozilla has 2 very nice features that make it
unique:
1. It has a setting that allows you to prevent web pages from opening
"unrequested
windows", also called pop-up adds.
2. You can open pages in a new window or in a new TAB of the same
window.
If you open links in new tabs, you can click on any tab to view that
page.
And, once you have a set of open tabs, you can bookmark that set of
tabs
as one bookmark. Then you can come back later and open all of
those
pages at once - by clicking on that bookmark.
If you want to try Mozilla, you can download it for free from the
Mozilla.org
web site. You can even use it to create web pages. All of
the
pages on my web site were created in Mozilla.
"..solved my problem within 2 minutes..":
Two
recent
comments from purchasers of my VB Quick Reference Guide:
"..solved my problem within 2 minutes of opening the reference. I
was at
my wits end. I have to admit that I was skeptical and all ready
to
say hey I want my $16 back, but I am very satisfied with the material."
Aileen H. - July 2003
"I trawled through 4 different VB6 Bibles and guides without finding
a
solution that I could understand. I finally gave in and
downloaded
your 'Quick Reference Guide to Crystal Reports in VB' and within 2
hours had
the solution working and installed. Thanks for writing a
worthwhile
document, well worth the $16".
Simon L. - July 2003
For a clear and simple guide to Crystal Reports in Visual Basic go
to:
http://www.kenhamady.com/vbref.html
Removal
instructions:
I have sent you my newsletter
because you are a former client, or because you have contacted me
regarding
Crystal Reports. If I am mistaken, or if you don't wish to
receive
the newsletter, please reply to this message with the word
'unsubscribe'
in the header.
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 2003 by Ken Hamady
All rights reserved - Republishing this material requires written
permission