The Crystal Reports Underground News - Volume 2018.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
** Contents for November 2018:
** Crystal Reports formula function libraries (2018)
** New functions in Cut Light
** My library of Crystal Reports materials
** Selecting ALL with a parameter field
** SQL Server dates show up as strings
** Let me create your Crystal Reports
** Setting default export options for a report
** Improving report performance with a subreport?
** Individual Training with an expert.
** Date formulas in Accpac without pwFormatDate()
** Flaw in XML Exports
Gems from the Archives
Grouping by a non-calendar week (November 2008)
Temporarily ignoring the grid and guidelines (September 2008)
Crystal Reports formula function libraries (2018)
It is time for my annual comparison of formula function libraries. If you aren't familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:
1) Carry values from today's report to tomorrow's report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Generate bar codes without having to install any fonts
If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:
Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)
The only product that has changed since last year is CUT Light, which can now generate 3 types of charts, crop images and process text through Google's "sentiment analysis" engine.
If you need help deploying one of these functions in a project let me know.
New functions in Cut Light
Ido Millet at Millet Software has recently added some unique new functions to the Cut Light UFL. Cut Light now adds 130 new formula functions to Crystal Reports. Here are the ones most recently added.
1) Creating charts from formulas.
This includes advanced gauges, sparkline charts (bar and line) and bullet charts. These charts are generated in real time by a formula function. The functions do not rely on Crystal's integrated chart engine. They are generated as image files which are automatically read into the report. These charts are simple and have no customizable features. There is also no drill-down capability. But these charts can be generated on any series of numbers, including numbers that come from print time variables or even shared variables . Ido has posted some image examples: And you can read the details in the user manual.
2) Image cropping.
Allows you to read an external image file and crop it before displaying it in the report. This can be used with the chart images above and also with existing Cut Light functions that allow you to read the image properties and resize an image. Here is the relevant section of the Cut Light user manual:
3) Google sentiment analysis.
This allows a formula to process a block of text through Google's "sentiment analysis" engine. The result will be 2 numbers which represent the score (positive, negative or neutral) and the "emotional magnitude". This can be used to evaluate any comments, Email messages or product reviews you have in your database. It does require that you have an ID to use the Google API, but there is no charge for the first 5,000 calls per month. You can read more details in the user manual.
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.
Selecting ALL with a parameter field
The most common use for parameter fields is in the selection formula, where they are used to filter the records. So if I have a parameter called {?State}, I can use it in the selection formula like this:
{Customer.State} = {?State}
But what if the user wants to be able to select ALL states? If the user enters the word "ALL" in the State parameter with the selection formula above, the query would look for records with a state value of "ALL", and likely wouldn't find any. But you can modify the formula to read:
( if {?State} = "ALL"
then True
else {Customer.State} = {?State} )
With this selection formula, the user can enter the word "ALL" and will get ALL states (including those that are blank and null).
Note that this rule is contained in a pair of parens. Those are only strictly necessary when there are other rules in the selection formula but it is safer to add them so they aren't forgotten. Without the parens a rule that follows the logic above will be considered part of the else and won't be applied all the time.
One other note. This works for string fields. If your field is a numeric or date your options are to:
Use a string parameter and convert user values in the selection formula to the correct data type.
Use a unique number (e.g. 99999) or a unique date (e.g. 1/1/2001) as your "All" value.
Make the parameter an optional parameter and then have the blank value represent "All".
SQL Server dates show up as strings
Before SQL server 2008 all date values were stored as DateTime values, even if you didn't need the time portion. Starting with SQL Server 2008 you could a column either as a Date (with no time) or a DateTime. But I have noticed, recently, that anytime I create a field with a "Date" type, Crystal sees the field as a string instead of a date. So even though I usually don't need time values, I typically create my table fields and calculations as DateTimes. That way Crystal can format the fields with date options and do date calculations in the report.
But one of my customers recently asked me about this. She found that this only happens if you use the SQL Server ODBC driver. Apparently, the SQL Server Native Client doesn't convert date fields to strings. So I did a test by creating two DSNs to a test database and a test table. One DSN uses the SQL Server ODBC driver (10.00.17134.01) from 2018. The other uses the SQL Server Native Client 11 (2011.1102100.60) from 2011. Sure enough, a report using the Native Client maintained the date value as a date, while the ODBC driver converted it to a string.
Then I read this page where Microsoft now recommends using OLEDB:
When I first tried OLEDB I saw two providers. They gave me the same two results as above, which told me that these providers were using the same two drivers I had just tested with ODBC. That is when I realized that the article was talking about a newer OLEDB driver. I downloaded and installed this driver but if you aren't careful it is easy to miss it in the list of providers. It looks very much like the old one. The only difference between the new one and the old one is that the new one uses the word "driver" while the old one uses the word "provider".
Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
The name in parens is what you see under connection "properties" in Crystal's "Set Datasource Location" window. When I used the new MSOLEDBSQL driver I got date values.
And, thanks to Laurie Weaver, a developer at Wyse Solutions, for letting me know this behavior was driver related.
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.
Setting default export options for a report
All versions of Crystal Reports since 2005 allow you to save default export options for each report. These options are stored in each RPT file so they only apply to the report that is open when they are saved. Once these options are saved the user only has to remember which export format to use. They no longer have to remember all of the specific export options. The saved options will be shown by default.
Here are the steps to save export options for a report:
1) Open the report
2) Go to File > Export > Report Export Options
3) Select a format and click "OK" (note – only formats that have options are listed)
4) Set the desired options and click OK
5) Save the report
The next time you export that report into that format, the options screen will already be filled in for you with the defaults that you saved. You are allowed to overwrite them at runtime, but overwriting them does not change the stored defaults. To change the stored defaults you repeat the steps above.
Also, one report can store export options for several different export formats at the same time. So if you have a report that is exported several different ways, you could store default settings for all 10 of the export formats that have an options dialogue.
Improving report performance with a subreport?
In most cases, subreports are a last resort. Typically they slow things down by adding an extra query to the process. But this week I found that moving some tables to a subreport actually sped things up.
The data came from the fundraising software Raisers' Edge, which uses data exported to an MDB. The customer had designed a new report and found that it ran for over an hour without completing the query. Nothing looked wrong in the structure so I did some troubleshooting. I started with one table and then added the other tables a few at a time to see which table was the problem. All was fine until I reached the last 17 tables which were all linked back to a single table. We only needed one record from each of the 17 tables and they all had about 500 records.
I was able to add the first three tables without issue, but beyond that the report would slow down more with each table added. It only took a few more tables to realize that we couldn't add all 17 tables to the report and expect it to complete. I double checked the links, confirmed the indexes were in place and still couldn't find any cause for the slowdown.
Finally, I removed those tables from the report and created a subreport that included just those tables. I also included the table that linked them all together. The subreport ran instantly both on it's own and when inserted in the main report. My guess is that the MS Access engine was struggling with the number of joins, so splitting them into two separate queries made it more manageable.
Individual Training with an expert.
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
Date formulas in Accpac without pwFormatDate()
If you write Crystal Reports against Accpac, now called Sage 300 ERP, your reports have probably seen or used the function pwFormatDate(). This is a custom function installed by Accpac that converts the numeric dates in Accpac into true date values. The raw data is an 8 digit numeric like 20161231 which is converted into a date by a formula like this:
pwFormatDate ({BKTRANH.TRANSDATE})
Sometimes my Accpac customers will try to send me a report saved with data. But because I don't have Accpac installed, I don't have the dll that provides this custom function (u2lcapw.dll). Crystal can't load the saved data because the formulas that use this function generate errors. In researching this topic I have found that even some Accpac users get this error, despite the fact that the dll is installed.
Fortunately, you don't need the dll or the custom function to convert these numbers into dates. I have had formulas on my web site for years that convert string and numeric dates into true dates. I just recently updated the formula that can replace pwFormatDate(). The formula above could be replaced by the following formula. It is longer, but it doesn't require installing or troubleshooting any dlls:
Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
Local StringVar DateString :=
If input < 19500101 then '19500101' else Totext (Input, 0, '');
Date (
Val (DateString [1 to 4]),
Val (DateString [5 to 6]),
Val (DateString [7 to 8]) )
Also, using this formula instead of using pwFormatDate() allows your reports to be opened with saved data on any PC that has Crystal Reports. So, if you are getting errors that say this dll can't be found you can convert to using the formula above and be done with the dll.
Update 10/12/208:
Ido Millet of Millet Software suggested an alternate approach:
Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
If input < 19500101
then Date (0,0,0) else
Date(Picture(ToText(input,0,""),"xxxx/xx/xx"))
Flaw in XML Exports
I have a customer who had me create report with a complex layout and lots of optional sections. In addition to using the report itself, he wanted to export the report values into a format where they could be read by another program. We decided to use XML so that the program could search for specific field name tags and extract the matching values. When we started testing the program we noticed some discrepancies between the report values and the XML export values. It mainly had to do with variables that were accumulated in the details and then displayed in the group or report footers.
It took over an hour to identify the root of the problem. Even after fixing the problem I couldn't explain it, so I created a very simple report to test it. It had one group, and the the following two formulas:
//Accum
WhilePrintingRecords;
NumberVar Accum;
Accum := {Customer.Customer ID}
//Display
WhilePrintingRecords;
NumberVar Accum;
I placed the Accum on the details band and hid that section. I placed the Display formula on a group footer. This exported to XML and showed all the Group Footer values correctly.
Then I split the detail band into A and B subsections. Both subsections were still hidden. The preview of the report looked the same, but when I exported to XML all the group footer values in the XML were zero. It didn't matter if I put the Accum formula in Details A or Details B. Whenever the Details section was both SPLIT and HIDDEN, the Accum formula would increment correctly in preview but NOT for the XML. If I used "suppress" instead of "hide" the Group Footer values exported correctly to XML. But even though the details did not appear in preview, they would now be included in the XML.
This behavior looks like a bug to me. That means there may be other situations where variables don't behave correctly in XML. So, if you are going to use XML exports, and you are using variables in your report, you need to test the output carefully to confirm that the variables export correctly.
Gems from the Archives
Grouping by a non-calendar week (November 2008)
Temporarily ignoring the grid and guidelines (September 2008)
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
Copyright 2018 by Ken Hamady
All rights reserved - Republishing this material requires written permission