The Crystal Reports Underground News - Volume 2018.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2018:
** Server-based scheduler comparison (2018)
** Formula to group into 2-month periods.
** My library of Crystal Reports materials
** Adding static and dynamic images to Crystal Reports.
** Listing all formula changes with Notepad++
** Let me create your Crystal Reports
** Column headings for cross-tabs
** Using a "display string" turns off HTML interpretation
** Open enrollment Crystal classes in Frederick, MD
** No current Crystal Reports certifications
** New SQL Server OLEDB provider supports TLS 1.2
Gems from the Archives:
Conditional page footer (May 2008)
Cascading parameters that lose values (March 2008)
Server-based scheduler comparison (2018)
I have just updated my comparison of server-based scheduling tools for 2018. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.
There are 11 products on the list this year (one dropped and one new) and a few few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.
Formula to group into 2-month periods.
A customer asked me to group the records in a report into 2-month periods. It sounded simple but there were questions to clarify, like:
Should the 2-month period start on the even months or the odd months?
Do you label the period based on the beginning of the period or the end?
This customer decided to have six 2-month periods in each year, with the label showing the beginning date of each period. The following formula will do that if you substitute your date field in the first line:
Local DateVar x := Date ({Your.DateField});
x:= x - Day(x) +1;
x:=
If Remainder (Month(x) ,2) = 0// 0/1 start periods with odd/even months
then Date(DateAdd('m',-1,x)) // Outputs date at beginning of period
else x;
As it is written, this formula will create 2-month periods for Jan-Feb, Mar-April, May-June, etc. Changing the zero at the end of line four to a one will cause each period to start with an even month instead of an odd month, so the periods will be Feb-Mar, Apr-May … Dec-Jan. I expect most people will want to start with odd months so that you don't have periods that are part of two different years.
In the example above the date returned by the formula is the first day of the period. If you prefer to return the last day of the period you can add the following additional line at the bottom:
Date(DateAdd('m',2,x)) -1 // Changes output to date at end of period
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.
Adding static and dynamic images to Crystal Reports
There are two ways to put images into reports.
1) A static image stored in the RPT.
2) A dynamic image read from a path [formula] at runtime.
If the report always uses the same image, like a company logo, then option 1 is the simplest. Use the menu options
"Insert > Picture " and select any of the supported file types. Once the file is placed on the report, the image can be resized as needed.
If the image periodically changes then you need option 2. For example, if different users want to be able to assign a different logo image file to the report without changing the report. Or if the image is based on a a file name name stored in the database like the image of a specific part. This method was added over 10 years ago with the release of CR XI (v11) so it will be available in most modern versions of CR.
To use option 2 you start by insert any image as a placeholder in the appropriate spot. Then you use the menu options:
"Format > Graphic > [picture tab]" and click the formula button to the right of the word "Graphic". Here you can enter a file path or a URL to any of the Crystal supported image file types. The best part is that this path/URL can be a calculated string. This is how you can change the path or the file name based on values coming from the database. For instance, if the you have a folder called "PartsPix" and each part has a JPG image named as the part number, your 'graphic' formula could be something like this:
"z:\partspix\" & {Parts.Partno} & ".jpg"
The path will be different on each record, and so the image shown will change accordingly. Note if the image files are different sizes then the images shown will also vary in size.
Listing all formula changes with Notepad++
I have written several articles about the usefulness of Notepad++. I often use it to write or test complex formulas and SQL commands. I have also used it to see the affect of a logic change, by comparing the output (text) of the report before and after a change. I have even created a custom language interpreter so that Crystal formulas written in Notepad++ look better than they do in Crystal's formula editor.
This week I found a new use that I should have seen before. A customer sent me two different versions of a complex report and we weren't sure which one to use. We needed to see the differences between the formulas in the two reports. So I exported both reports to the format "Report Definition". This export creates a text file that lists all of the major settings of the report, lists the objects in each section, and includes the text of all the formula fields that are actively being used by the report.
Once I had the two Report Definitions, I opened them up in Notepad++ and used the "Compare" add-in. This took me straight to the handful of formula differences. It was easy to show these differences to the customer to see which version he wanted to use.
If you want to try out the Notepad++ with the Compare plug-in you will need to start by downloading Notepad++ version 7.4.2. This is the latest version that includes the plug-in manager. Once you have Notepadd++ installed you can use the plug-in manager to install the Compare plug-in. After that you can update to the latest version of Notepad++.
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.
Column headings for cross-tabs
I recently shared a method for adding a Title to a Cross-tab that spill onto horizontal pages. One of my readers shared a similar method that he had written up for his team. This method allows you to add column headings over the row grouping fields. He even includes a description of how to have these headings repeat on horizontal pages. The approach combines two separate section settings.
See this PDF for a detailed explanation of the steps.
And, thanks to Ralph Wahlert of Catholic Health Services in Long Island for sharing his approach.
Using a "display string" turns off HTML interpretation
A customer was deploying my calendar report for multi-day events when he noticed an issue in CR that I had never noticed. This particular calendar report takes advantage of several less commonly used features of CR. It appears that two of those don't play nicely together:
1) The Display String property (overrides the current value and displays something else)
2) HTML Interpretation (applies any HTML formatting codes found within the field value).
The customer told me that if he tried to change any of the formatting properties he would lose the HTML interpretation. Also, the entire paragraph tab would disappear so that he could not re-activate HTML interpretation. So I did some testing and found the following related to these two properties:
1) Whenever you format an object and add or change the display string formula ("common" tab) Crystal will reset the HTML interpretation property back to 'none'. It will also no longer show you the "paragraph" tab which prevents you from re-activating HTML interpretation.
2) You can comment out the display string and save it. This brings back the paragraph tab. You can then re-activate HTML interpretation. Finally, you can go back to the Display String formula and take out the comment marks. This will give you both features at the same time.
3) But the next time you then change ANY formatting property (other than adding or deleting the comment marks in the display string) Crystal will again reset the HTML interpretation back to 'none'. You have to make your formatting changes, save them, then go back in and comment out the display string so you can re-activate the HTML interpretation.
4) If you comment out the display string formula and save it, but then switch to the paragraph tab without clicking the OK to exit the "Format Field" dialogue, the paragraph tab is not only reset but it won't even display default values. Where you normally see zeros the boxes will be blank. And the radio buttons for "Reading Order" will both be blank. However if you click OK, and then use Format > Field to get back to the paragraph tab, it will show all of the default values.
5) If you select multiple fields, and use "format object" to format them all together, the behaviors described above do not appear. You can still see the 'paragraph' tab even after changing the display string. The HTML interpretation doesn't get reset. Thanks to Guillaume Boucher of W3COM.fr for pointing this out to me. This explains why I was able to develop all 3 calendar report variations and never notice this issue.
Very strange.
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.
No current Crystal Reports certifications
I have never been a fan of the SAP certifications for Crystal Reports. But if you were looking to be certified in the near future, it looks like there isn't currently an option. I read this comment in an SAP discussion on certifications:
"Exam C_BOCR_13 [for CR 2013) is retired and no longer available and we do not have any information on an updated version."
Once an exam is retired you can no longer take it. And if there is no 'updated version' then there is no way to take an exam for CR 2016, at least not through SAP. If anyone hears about an updated exam, let me know and I will update this article.
New SQL Server OLEDB provider supports TLS 1.2
If your organization handles sensitive information on the web (Credit Card info, HealthCare info, etc) you are probably using the latest TLS protocol (1.2). And if you are connecting to MS SQL Server through OLEDB, you may have had trouble connecting recently. This is because the SQLOLEDB provider and the SQL Server ODBC driver are no longer supported in TLS 1.2.
At the end of March, Microsoft released a new OLEDB Driver (MSOLEDBSQL) that does support TLS 1.2.
Thanks to Lyle Hardin of Foslyn LLC for sharing this info and links.
Gems from the Archives:
Conditional page footer (May 2008)
Cascading parameters that lose values (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