The Crystal Reports Underground News - August 2002
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for August:
** Evaluating your Crystal Instructor
** New Utility to Search/Modify an entire batch of Crystal Reports in one step
** Reporting on Data that Isn't There.
** My Quick Reference to Crystal Reports in Visual Basic
** Additional CALs now affect Broadcast License
** CUT Utility goes visual
** Public classes in Leesburg, Baltimore and Philadelphia
** Is there a subreport hidden in this report?
** Read back issues at http://www.kenhamady.com/news.html
Evaluating your Crystal Instructor:
A Crystal Reports class can be a significant investment, so it makes sense to see who will be teaching it. If you are planning on taking a Crystal class, or having a class on-site, I recommend that you find out the name of the instructor and ask the following 3 questions:
1) How long has this instructor been working full-time with Crystal Reports?
2) Roughly how many students has this instructor taught?
3) How often does this instructor teach a class using the student's own data?
I have been working with CR for 6 years (11 years if you count other reporting tools) and I have personally taught Crystal to over 1300 students. Training on location is my specialty and I don't charge extra to incorporate the student's data into the lessons. Many students write that my class as the best technical class they have taken. That is probably because Crystal is all I teach and also because I wrote the materials that I use.
I currently spend almost half of my time either writing reports for real customers or solving reporting problems for users. This includes a few hours each week answering questions at Tek-Tips.com, the busiest Crystal Reports forum on the web. You will find me ranked as the top expert out of over 23,000 members.
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
Search/Modify multiple Crystal Reports with RPT Inspector:
RPT Inspector is a new utility that allows you to modify an entire batch of reports in one step. Batch modifications can be made to almost any property - including both report properties and individual object properties. For example, I was able to search for and find all of the reports that used a particular function in a formula. I was then able to change these formulas in the entire batch in one step. I could see this being a huge time saver if you manage many reports.
RPT Inspector even exposes some properties that you can't otherwise modify in a Report. For instance, it allowed me to change the name of a subreport AFTER it was inserted - something that normally requires hex-editing the RPT file. It also exposes the height property of each section as a number, which makes it possible to give several sections the exact same height. This has always been a needlessly difficult task in CR.
See my LINKS page above for a link to this product's web site.
Reporting on data that isn't there:
(If you like this article you should check out Expert Techniques for Crystal Reports)
Crystal will not create a group unless there is at least one record for that group in the report. This is also true for Cross-tabs and Bar/Line Charts. However, it is not unusual for users to request that missing groups show up with a zero. There are several ways to do this and the approach to use depends on a number of factors. I will give a brief description of 6 different techniques below. If you need help applying these techniques to a specific report, drop me a line.
As an example, lets say that you need a report to list total sales for each salesman in a month. We want each salesmen listed - even those with no sales in the month. Here are your options:
1) Manually enter a zero sales record for each salesman each month:
This is not usually practical, but it is the easiest short term solution if the report is a one time item. Charts and cross-tabs will incorporate the zero groups automatically.
2) Expand your selection criteria to include irrelevant records to create the groups:
Assume that the transaction table includes both sales and calls, and that every salesman has at least one call per month. Your report could select the records for BOTH sales and calls. Then you can write a formula like:
if {Type} = "Sale"
then {Amount} else 0
When you subtotal this formula by salesman you will have a group for every salesman, but a total of only the sales transactions. If you are printing the details you would suppress the call details and only show the sales details. Again, charts and cross-tabs will includes the zero groups automatically.
3) Join a primary table that has all groups, and use an outer join:
If you have table that lists all of the salesman, you can use this table as the reports primary table. Link this table to your transactions with a "Left Outer" join, which tells the database that you want all records from the primary table and only matching records from the transactions. Again, charts and cross-tabs will includes the zero groups.
However, there is a weakness to this approach. If you put ANY criteria on the transaction fields, you cancel the effect of the outer join. So in our example, when you select transactions for one month, you would then lose all salesmen who had no sales in that month. To keep the outer join behaviour you have to eliminate ALL criteria from the 'Outer' table. That means including ALL transactions for ALL months. You can suppress the details of the records you don't want to see or use, but if your database is large, this makes for a very slow report.
4) Hard code a conditional total for each salesman:
If you don't have a primary table for salesman you could hard code the list into a set of formulas. Say you have 10 salesman. You could create 10 conditional formulas that each look like this:
If {Sales.Salesman} = "Ken Hamady"
Then {Sales.Amount} else 0
You write one formula for each Salesman and then create a grand total of each Formula. You can arrange the totals in the Report footer in any way you like, with labels next to them. The report no longer requires a group.
This technique is often used with dates or numbers in a series. Say you wanted to display the 14 days in a user specified 2-week period, including empty days. You could create 14 formulas that calculate the 14 required dates. Each formula would calculate a different date by adding a number of days to the start date selected by the user. Each of these formulas could then be used in a conditional formula like the one shown above. You would add a corresponding grand total for each formula.
The main downside to this technique is that you have to change the report every time you add a new Salesman or change the number of days. Another down side is that these 'groups' can't be used as row / column fields in Cross-tabs or as groups in Group Charts. They can be used in advanced charts, but the chart labels will include either the words "Sum of " or the @ sign.
5) Use Subreports to look for the details.
You can create a simple report from the Salesman master table that includes one record per salesman. This report could then have a linked subreport on the detail section which would launch a new subreport to get each salesman's transactions. The main report would show every salesman even if the subreport was empty.
The downside is that running multiple subreports is inefficient. If the list was long, the time needed to process all of the subreports might be prohibitive. Also, data from multiple subreports cannot be used to create a cross-tab or any type of chart.
6) Create imposter sections that fill in gaps:
This technique works when you have a consecutive series that can be calculated, like dates or numbers. You group by the date or numeric field and then you create several extra group footer subsections. Group footer "B" will print only when the gap between one group and the next is greater than one. It displays the missing value by adding one to the current group value in a formula:
{table.DateOrNumber} + 1
If you add a zero next to the formula, the two objects can be formatted and aligned to look like the group name field and the subtotal from the original group footer. To allow for a gap of 2 or 3 consecutive items, you would add group footer 'C' to display when the gap is greater than two, and a similar formula. Keep adding sections until you have accounted for the largest gap that will likely occur. Of course these imposter groups will not show up in cross-tabs and charts of your data.
VB Quick Reference Guide:
If you need to incorporate Crystal Reports into a VB application, you should get my Quick Reference Guide. It gives an overview of the integration methods, with syntax examples for the most important commands. It comes with source code examples, including a sample report viewer. It is only $16 and can be Emailed to you within 24 hours. Follow the link below for more details:
http://www.kenhamady.com/vbref.html
Additional CALS affect Broadcast License limit
Crystal Enterprise v8.5 was released last month. In the new license, Crystal Decisions has modified their approach with regard to the Crystal Broadcast License (CBL). They plan similar changes for the CRv9 license which will be released later this year. The CBL is now called the RDL or Report Distribution License.
The new license for CEv8.5 starts out the same as before. It allows you to distribute report output to up to 50 users for free. But in Crystal Enterprise this is now tied to your 5 free Concurrent Access Licenses (CALs). You can go beyond 50 if you increase your CALs, and each CAL is worth 10 external users in your distribution. So, buying an additional 5 CALs allows you to add 50 more external users to your broadcast, or 100 in total.
We will have to wait and see what happens with CRv9, but they are talking about dropping the annual fee in favor of a one time fee. Companies using CRv8.5 or CEv8 are still under the old CBL license model. My personal opinion is that restricting the use of the static output of Crystal Reports is an aggressive approach to licensing. Follow the link below to see how the CBL provision in the current Crystal Reports license is interpreted by the licensing team at Crystal Decisions:
http://www.kenhamady.com/license.html
CUT Utility goes "Visual"
In the June newsletter I mentioned the CUT UFL for E-mailing, exporting, electronic bursting of your reports. Millet Software has now released a visual application that has similar capabilities, called Visual Cut. The original CUT UFL gives you more control when assembling an Email text message, but the Visual application is much simpler to use and includes scheduling. Read about them or download the user manuals at:
http://www.milletsoftware.com/
Don't just take a class, learn from a Crystal Expert:
Click the "Public Classes" link above for dates.
Each class is $675 per student.
Or, call for more information at (540) 338-0194.
Is there a subreport hidden in this report?
Have you ever opened a report created by someone else and wondered if they used any subreports or how many there were? Sometimes small subreports are tucked in the middle of sections and are easy to miss. Here are 3 ways to to quickly find subreports in any report.
1) Export to "report definition" format and search the resulting text file for the word 'Subreport'. It will tell you what section the subreport is in.
2) Right click on any subreport and select "Change Subreport Links". At the top of the window you will see a selector box that will list ALL of the subreports in the report. If can't find any subreport to right-click on, add a simple subreport yourself, just to open this window.
3) Drag the expanding cursor box (or lasso) to select all of the visible objects as a single group. Then hit the delete key. If the objects go away with no warning message, then there were no subreport objects selected. If there is a subreport you will get a warning telling you that you won't be able to to undo what you are about to do.
Of course options 2 and 3 should be done with a copy of the report, not the original. You don't want to accidentally hit "Save".
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 2002 by Ken Hamady
All rights reserved - Republishing this material requires written permission