The Crystal Reports Underground News - Volume 2018.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2018:
** Comparison of desktop-based schedulers (2018 update)
** Lasso multiple objects without selecting lines and boxes.
** My library of Crystal Reports materials
** Using "Order Links" to improve performance
** Adding info to dynamic parameters
** Let me create your Crystal Reports
** Printing "group continues on next page"
** Trouble with the suppress condition for a line
** Open enrollment Crystal classes in Frederick, MD
** Blocking drill-down based on a condition
** Titles for Cross-tabs that spill onto horizontal pages
Gems from the Archives
Cascading parameters with duplicate field names (March 2008)
Converting a decimal to a fraction (March 2008)
Comparison of desktop-based schedulers (2018 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 10 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.
Lasso multiple objects without selecting lines and boxes
One method for selecting multiple objects in Crystal (like most Windows programs) is to "lasso" them. To lasso objects in Crystal you click and hold your mouse button in an empty spot and then drag the cursor to expand an orange rectangle. When you release the mouse button, all of the objects inside (or touched by) the orange rectangle become selected objects.
The problem I often have with this method is that drawing objects (lines and boxes) often get selected as well. For instance, if you lasso a group of objects that are inside a box object, that box will often be selected. Usually this happens if you select the highest object in the box, even if you don't touch the box itself. If your lasso box touches or surrounds line objects those lines are also selected.
Often I want to select a column of numbers for alignment or formatting and I want to skip over the lines. Before now my options have been to lasso the objects and then carefully deselect the lines and boxes by holding my [ctrl] key and then clicking those objects. Or I could skip the lasso method altogether and select that group of objects by clicking on them individually while holding down the [ctrl] key.
But this past week I discovered something I had never noticed before. If you use the lasso while you are in preview mode, the lines and boxes are NOT selected. I had created a grid of 40 numbers (4 across and 10 down) and had separated the rows and columns with horizontal and vertical lines. Plus there was a box around the perimeter. To select all the numbers individually in design mode would have taken 40 clicks. The lasso would take 13 clicks to deselect all the lines and the box. But while in preview I could select all the numbers in one lasso and none of the drawing objects were selected.
This made it easy to align the columns or change the format for the numbers as a group.
My complete 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.
Using "Order Links" to improve performance
I have written once before about using the "Order Links" feature of the database expert. In that article I used "Order Links" to help me prevent a SQL error in the pervasive database engine. This past week I used the same feature to improve the performance of a report.
This report had many tables but there was one, the Dept table, that provide the primary filter for the report. The user would select a specific department each time they refreshed. But when I looked at the SQL generated by CR, I noticed that the Dept table was the last table joined into the data set. In my mind, that meant that Crystal was bringing in thousands of linked records that would eventually be discarded because they were linked to the wrong department. If the SQL could apply the filter up front we would greatly reduce the number of linked records we would generate, and that would speeds things up.
So I suggested to the user that they go into the "Links" tab of the Database Expert and right-click to find the "Order Links" option. I had them move the Dept join to the top of the joins list and they immediately saw a dramatic improvement in performance. It may not work in every report or with every database (this report was Oracle based), but when there are lots of tables and the report is slow, it is another option to try.
Adding info to dynamic parameters
I created an unusual dynamic parameter for a customer this week. They wanted to select from a list of customers, but their customer list included lots of dead weight. To shorten the list they decided to include only customers who have sales of over $10K in the past 2 years.
To filter the results of a dynamic parameter you need to base it on a SQL command, rather than pulling it directly from a table. And because this filter involved a total our command needed a nested subquery. The inner query took all orders in the past two years, and did a sum of the order amount, grouped by CustomerID. Then the outer query selected records from the inner query when that sum was greater than $10K.
The field for our parameter was the Customer ID but Crystal parameters allow you to bring in a second 'description' field along with the filter field. The Customer Name was our description field.
This worked fine, but we noticed that if a customer had multiple accounts, the customer name appeared multiple times with slight variations. I thought it would help if the user could see the sales along with each name. Since the SQL we had written already included the total sales for each customer, I figured we could make that part of the description field. In the outer query I replaced the original description field (customer name) with an expression like this:
A.CustName + '($' + Ltrim(Str(A.TotalAmt/1000,5,0)) + 'K)' As CustName
This generated a parameter list that looked something like this, with total sales shown as part of the parameter description:
AMCB3 – AMC Bonding Co., Inc.($15K)
BKDA1 – BKD Associates($10K)
CRSU2 – Credit Suisse AG ($13K)
DEVG2 – Deveraux Foundation($25K)
FOSE1 – Four Seasons($19K)
I have done something similar when the description field needed to combined multiple fields, like FirstName and LastName. It can be done with any combination of fields.
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.
Printing "group continues on next page"
Many Crystal course books will have a lesson on printing a "Continued" message whenever a group takes up more than one page. This message usually prints at the top of each page in the group, except the first. But below is a different approach. It prints "Continued on the next page" at the bottom of pages when a group is going to go beyond the current page. There are actually several ways to do this, but the one that works in almost all situations is below. It uses 3 formulas, and these can be cut and pasted into your report:
The first formula goes on the Group Header (and can be suppressed):
WhilePrintingRecords;
BooleanVar Continued:= True
The second formula goes on the Group Footer (and can be suppressed):
WhilePrintingRecords;
BooleanVar Continued:= False
The third formula goes on the page footer:
WhilePrintingRecords;
BooleanVar Continued;
if Continued
then "Group continues on next page"
else ""
Trouble with the suppress condition for a line
I had a bit of a mystery this past week. A customer wanted to suppress a series of vertical lines in the page header, based on a condition (a field being null). SAP just added a suppress condition button for lines and boxes in the latest version of CR, CR 2016, so I figured it would be simple.
But when I put the condition formula in for one of the lines, it did not suppress when it should. I applied the same condition to the next vertical line and it worked as expected, but the first line did not. If I deleted and redrew the line it worked fine, but nothing I could do would get the original line to work.
So I spent a few minutes trying to figure out what was different about the line that didn't work. I found that while the top end of this line looked like it was in the page header, it was actually a hair higher in the report header. The report header was suppressed.
Normally, suppressing or hiding a section has no affect on formulas. It does affect some other things like subreports and cross-tabs. A hidden/suppressed section will prevent subreports in that section from running. To get a shared variable from a subreport without seeing the subreport requires that we use a 'stealth' subreport. Also, a hidden/suppressed section will prevent a cross-tab from doing any of it's calculations. But formulas are often put in suppressed sections and they are not normally affected. The suppress formula for a line or a box is an exception, apparently.
So, if you enter a condition formula to suppress a line or a box, and if that line/box has it's top in a suppressed or hidden section, then the suppress condition will be ignored. This isn't true at the bottom of the line/box, only at the top.
One more mystery solved.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my public classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near several major cities and are willing to travel. Call for details.
Blocking drill-down based on a condition
Here is a new twist to an old trick. I wrote several years ago about preventing users from drilling down on a section by putting a text object over the other objects in the section. I was recently asked if there was a way to have this happen only on certain groups. My initial reaction was no because a text object is either there or it isn't, but then I realized that more recent version of CR allow you to add to the width of an object with a condition formula. And, when you make the text grow based on a condition it still blocks the drill down for any objects that it grows to cover.
So say you want to allow drill-down on accounts that are open, but not on accounts that are closed. You could put a very narrow text object on the far left end of the group section for the account. Then you could go into "object size and position" and put the following formula into the width condition:
if {table.status} = "Closed"
then 1440 * 8.5 //1440 twips per inch
else 0
This formula calculates the number of twips to ADD to the existing width. There are 1440 twips in an inch, so I multiply the number of inches I want by 1440. This makes it clear how much it will add and makes changing the value easier.
Titles for Cross-tabs that spill onto horizontal pages
When a cross-tab gets too wide, it generates a 'horizontal' page to the right of the original. If you were to print the report to paper, these horizontal pages would print as separate pieces of pages. However, page header objects do not automatically repeat on these pages. So, any titles that you have in your page header will be missing on these horizontal pages. You have two options to get the titles to repeat:
1) If you want the fields to appear outside of the cross-tab, you can format them to repeat on each horizontal page. Right-click on the field, select "Format Field" and go to the common tab. You should see a property called "Repeat on Horizontal Pages". Checking this property will cause the object to print again automatically for every horizontal page.
2) If you want the title to be part of the cross-tab, you can put your title into a formula and use that as your first column field. This title can incorporate parameter fields and can also include a carriage return character to print on multiple lines. An example formula might be:
WhileReadingRecords;
"Sales by Region and Shipping Mode" & Chr(13) &
"Orders From: " & ToText ( Minimum ( {?Order Range} ) , 0 , "" ) &
" To: " & ToText ( Maximum ( {?Order Range} ) , 0 , "" )
The key is that the formula has to start with WhileReadingRecords. This makes it eligible to be used as a cross-tab column. You make this field the first "Column" field in the cross-tab. You should check the "suppress subtotal" property for this group level so that it doesn't generate duplicate totals (cross-tab Expert, customize style, highlight this formula and check the property on the left). And if the formula includes Chr(13) (to generate a second line) you will have to adjust the height of that cell to show the second line. It won't grow automatically.
Gems from the Archives
Cascading parameters with duplicate field names (March 2008)
Converting a decimal to a fraction (March 2008)
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 2018 by Ken Hamady
All rights reserved - Republishing this material requires written permission