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.
"..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