The Crystal Reports Underground News - Volume 2015.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2015:
** Comparison of desktop-based schedulers (2015 update)
** My new website layout
** Using regular expressions in Crystal formulas
** My library of Crystal Reports materials
** Returning the name of the database
** Extra headings for a cross-tab
** Let me create your Crystal Reports
** Ranking groups based on a 'ratio' formula
** Spreadsheet doesn’t list any tables
** Open enrollment Crystal classes in Frederick, MD
** ToText() function throws in an extra space.
** Calculating age for a ‘leap-day’ birth date
Gems from the Archives:
Using the Performance Info feature
Finding groups where the last record meets a criteria
Comparison of desktop-based schedulers (2015 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 is one new product this year for a total of 11 products. 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.
My new website
If you have been to my Website in the past week you may have noticed that it has a new look. My focus has always been on content, but after a helpful nudge from a colleague I realized that my 'classic' style (circa 1997) was not showcasing the content very well. So I took advantage of a slow week, learned some new software, and gave it an update. I think everything is working but there are 200 pages. If you see anything broken please let me know.
Using regular expressions in Crystal formulas
Regular expressions, for those of us who don’t see them often, are a group of symbols that allow you to efficiently define a complex string pattern. You can define this pattern with optional characters, varying lengths, and alternate spellings and punctuation. Below is a simple example showing how you would define the pattern found in a formatted height value using feet and inches:
^[0-9]+\'[ ]?([0-9]{1,2}[\"]?|)$
This says that the string must start with a digit. The digit must be followed by a single quote and then an optional space. The string must end with either 1 or 2 digits followed by an optional double quote.
Unfortunately, regular expressions are not a native feature of Crystal syntax. But Ido Millet of Millet software has just added several functions to the Cut Light DLL that make regular expressions available inside Crystal formulas. The functions allow you to test, search and replace strings based on the patterns defined using regular expressions. To read more about Cut Light as well as other DLLs that can add power to your Crystal formulas, you should read my article on User Defined Function Libraries (UFLs).
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 ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Returning the name of the database
This week we have a clever trick that was shared by Larry McCulloch of Lares Research as part of a Linked in discussion. The question was how to have the report automatically show the database being read. Apparently someone had run a report in a training/testing database and it had gotten mixed up with reports coming from the live database. If Crystal knew which database it was reading it could flag reports that were coming from someplace other than ‘live’. Larry’s suggestion was to add the following SQL expression in SQL Server environments):
{fn DATABASE()}
Then people suggested various was of doing the same thing in Oracle environments:
(SELECT ora_database_name FROM dual)
(SELECT sys_context ( 'userenv' , 'db_name' ) FROM dual )
(SELECT global_name FROM global_name )
I tested the SQL Server example but I don’t have an Oracle environment to test the others. If you try them or have some feedback, let me know.
Extra headings for a cross-tab
A customer had a large cross-tab and he wanted to add some extra headings above the first two columns (The ‘row’ fields). The row fields don’t normally get headings in a cross-tab but it isn’t difficult to put a couple of text objects above those columns in the empty “notch” above the cross-tabs row names. The first he had was that the cross-tab was in the report header and spilled over to the next page. Text objects in the report header would not repeat on subsequent pages.
Now, the simplest way to get something on every page is to put it in a page header. But page headers don’t start printing until the report header is done, so that wouldn’t help us. So I told the user to move the cross-tab to the report footer. This does the same thing as the report header, and when the report footer spills onto multiple pages it includes page headers.
So now we had the text headings on every page, but they were higher then they should be. He wanted them lined up with the other headers above the cross-tab summary fields. So the last step was to set the page header to “underlay”. This means that instead of printing above whatever section comes next, it would print superimposed over whatever section comes next. This made it easy to align things the way he wanted.
All this took less than 15 minutes. So if you find that you can’t get Crystal Reports to do what want, it might be worth giving me a call.
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.
Ranking groups based on a 'ratio' formula
Crystal allows you to put your groups in order based on the value of each group’s subtotal. This is found in the Group Sort Expert (or the TopN feature in older versions). But the group sort expert in CR can’t rank groups based on a formula – you have to use a subtotal as the ranking field. Sometimes you can convert your formula into a subtotal, but the formulas I get asked about most are ratios. People divide one subtotal by another to produce an average or a ratio and they want to use that as a group ranking field. Unfortunately, you can’t rank groups in CR based on ratios.
One solution is to use a cross-tab, because CR can rank rows of a cross-tab using a ratio. But not everyone can meet their layout requirements using a cross-tab. This week a customer needed to rank groups based on a ratio and I couldn’t get the layout they wanted with a cross-tab, so we had to go to the last resort. We calculate the two subtotals in SQL using a “Group By” and then crystal can calculate the ratio as a detail level formula. The groups in the original report would be rolled up in SQL by the “Group By” and given to Crystal as one record.
There were two challenges to doing the calculations in SQL:
1) The fields we were totaling had null values.
2) We needed to calculate the ratio using a distinct count.
I have written before about how much easier it is to do calculations in a Crystal formulas as opposed to SQL. One of the reasons is how much easier it is to deal with Null values in CR. But I did find a relatively simple method in SQL using the Coalesce function. If you want to choose between two values and choose the one that isn’t null this simple expression will work:
Coalesce (table.field1, table.field2)
If you want null values to default to zero (or any other value) you can use:
Coalesce (table.field1, 0)
And if you want to add two values together when either one might be null you can use:
Coalesce (table.field1 + table.field2, table.field1, table.field2)
This will check the values from left to right and return the first value that isn’t null.
Also as part of this project I found the syntax for a ‘distinct count’ in SQL, which I had never had to use before. All you do is put the word Distinct inside the parentheses, just before the field name like this:
Select count ( Distinct table.field ) as DesiredColumnName
If you are using a “Group By” it calculates a value for each group, and if you are not using a “Group By” it calculates one value using all records.
Spreadsheet doesn’t list any tables
I recently worked with a customer who needed to incorporate some Excel data into a report. Excel tables are not my first choice, but sometimes it is the only practical solution for the client. Since these were XLSX files we setup up an ODBC connection. But when we connected from Crystal we could not see any tables under that ODBC connection. We were thinking about re-saving it as an XLS when I did a quick online search and found the answer we needed in a forum post.
We had to go into File > Options > Database tab. Then under Tables and Views we had to add a check mark for ‘system tables’. Then the spreadsheet table showed up. I was surprised, because I have created many reports that read spreadsheets and I don’t ever remember having to do that. It is possible that there was something unique in that environment that required this change, but I never argue with success. And, if you ever run into something similar you have one more thing to check.
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 May 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 and dates.
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 Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.
ToText() function throws in an extra space.
You can use the ToText () function to turn dates into strings, which is handy in cases when you need to combine a date with other characters. For instance I use this pattern in IF-THEN-ELSE formulas, when I want to give the user a parameter to choose sorting on a date value vs a string value:
else ToText ({Tale.Date} , 'yyyy-MM-dd')
This turns the date into an 8 character string that can still be used as a sort field and will still sort the records in chronological order. The [yyyy] returns a 4-digit year, the [MM] in the middle returns a 2-digit (0 padded) month and the [dd] on the end returns a 2-digit (0 padded) day. The dashes can be any character that you want to use as a divider. So using the formula above the date value 2/4/2015 ends up looking like this:
2015-02-04
For purposes other than sorting you might want a 2-digit year or a month/day value that is not zero padded. In that case this formula:
ToText ({Tale.Date} , 'yy-M-d')
creates a date that looks like this:
15-2- 4
The digits are correct, but if you look closely you will see an extra space in front of the days value. For some reason a single-digit month value does not have a space but a single-digit day value does have a space. This must be a bug since I can’t think of any reason for the difference. And it is a long standing bug since I just tested this in v10 (2004) and v8.5 (2001) and both have the exact same behavior.
If you ever encounter this and want to remove the space from in front of a single digit day, you have to use a replace function like this:
Replace ( ToText ({Tale.Date} , 'yy-M-d') , " " , "" )
Calculating age for a ‘leap-day’ birth date
I have a formula for calculating an age integer that has been on my site for a decade. Yesterday I thought I had found a bug in it. After I fixed the formula I did some more research and found that the original was correct in most situations. The question centers on cases, when a person is born on a ‘leap-day’ (February 29) and when exactly they turn a year older in non-leap years where there is no date of February 29. In the UK and in Hong Kong their age would increment on March 1. In New Zealand or Taiwan their age would increment on February 28. In many other jurisdictions, like most of the US, this isn’t specified anywhere, although the norm seems to be March 1. My original formula will increment them to the next year on March 1. But I have added an optional 5 lines of code to the formula for those who want the change to happen on 2/28.
Gems from the Archives:
Using the Performance Info feature (Volume 2004.08)
Finding groups where the last record meets a criteria (Volume 2004.08)
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com