The Crystal Reports Underground News - Volume 2016.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2016:
** Comparison of desktop-based schedulers (2016 update)
** Turn a Crystal Report into a web dashboard
** My library of Crystal Reports materials
** 2016 updates to Find it EZ
** Sage 50 update might disable some reports
** Let me create your Crystal Reports
** Find the first/last date that meets a condition
** Linking tables without dragging
** Open enrollment Crystal classes in Frederick, MD
** Document from SAP on Excel exports
** Shared array variable vs recurring subreport
Gems from the Archives:
A few tricks I learned from my students (May 2005)
Copying and pasting cross-tabs to Excel (June 2005)
Comparison of desktop-based schedulers (2016 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 12 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.
Turn a Crystal Report into a web dashboard
Adam Butt of APB Reports in Norway has developed some sophisticated web dashboards using Crystal Reports exported to txt format. You have to see them to appreciate them. They are interactive and the data behind them can be refreshed automatically as often as needed to keep them up to date.
Adam accomplishes this by creating special text objects in the report to hold the needed HTML code and javascript. The report is then scheduled to export in txt format, but is named as an HTML file. The file includes all of the data and code to run the dashboard. The files can then be automatically uploaded to a website by the same scheduler software that created the export. Adams process is built around Visual Cut as the export engine, but you could probably accomplish something similar with other scheduling tools.
And if you don't want to learn the techniques, you can hire Adam to create dashboards for you.
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.
2016 updates to Find it EZ
Find it EZ Software has released a 2016 edition for their product suite. They are now offering the software in three tiers:
Code Search Pro Desktop
Code Search Pro Server
Dev Surge 365 Enterprise
You can download a comparison of the three tiers for more specifics. So what is new?
Dev Surge 365:
This is a new offering that covers all the features of the previous products. It adds the ability to mass update connection information within a BOE repository (Server, DB UID, PWD). It can do this at the report level or at the scheduled event level. It also adds the ability to do mass comparisons of objects. This includes Crystal Reports, database objects, several programming languages and office documents. In all cases you can drill down to granular differences between the objects. It runs on Windows desktop or Windows Server OS.
In all three editions you can now go beyond extracting the SQL from report expressions and commands. You can also now extract the SQL from database objects, like views and stored procedures that are called by the report. So now you can keep track of the fields used by a report, even if that field is only mentioned in SQL stored in the database.
Free trials are available on the Find it EZ website.
Sage 50 update might disable some reports
If you are a Sage 50 user and rely on Crystal Reports you might want to pause before you apply a specific recent update. I have been told by Dave Smith at Phase One Computing that Sage 50 US Edition, update 2016.2 can prevent Sage from running reports if the reports use any of the "GetPeach" functions. If you have not already installed that update, you might want to wait and check your critical reports to see if they use these functions.
Dave says that Sage is aware of the problem but we don't yet know how they will respond.
Update on 2/18 from Dave Smith:
SOLUTION FOUND: When this Sage 50 update is installed, which requires a conversion of the database, make sure that in addition to resetting your Crystal Reports User Security Settings (reset the password with the Change button), that you also click on the "Install Sage 50 Data Functions" link to the right of the screen. This will update the u2lpeach.dll file with the new Sage 50 Data Functions, which directly impacts Crystal Reports. After doing this, the Crystal Reports which use the GETPEACH expressions (this accesses the u2lpeach.dll file), will operate correctly.
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.
Find the first/last date that meets a condition
A customer had a number of different appointment types in their data. They wanted Crystal Reports to calculate, for each client, the most recent date for several different appointment types. They asked why this formula didn't work:
If {APP.TYPE} like "Assessment"
then Maximum( {APP.DATE} , {CLIENTS.CLIENT_ID} )
The expected this syntax would give them the date of the last Assessment, and they were close. But in Crystal you have to create the conditional column (formula) first and then you apply the maximum function to that column. The conditional formula would look like this:
//Formula called {@AssessmentDateMax}
If {APP.TYPE} = "Assessment"
then {APP.DATE}
This creates a column of dates where only rows that have the type of “Assessment” will show a date. You can then create a summary (Insert > Summary) that calculates the maximum value of this column for each client group. Or you can write the following formula to calculate the same summary inside a formula field:
Maximum( {@AssessmentDateMax} , {CLIENTS.CLIENT_ID} )
If you want to find the most recent date for a second appointment type, like "Intake", you would need a separate formula and a second summary.
So that is how you calculate the last date. To find the first date you need to change the formula. If you calculated the minimum of the {@AssessmentDateMax} formula shown above you would get a blank. That is because all of the records that are not assessments would have a blank value (date zero), and that would be the lowest date value in the column. The formula below will work correctly with the minimum function, but will not work if you want to do a maximum:
//Formula called {@AssessmentDateMin}
If {APP.TYPE} = "Assessment"
then {APP.DATE}
else Date (2050,1,1)
If you are willing to do a bit more work there is a way to create a single formula that you can use with both the minimum and maximum functions. It requires that you create a separate {@NullDate} formula, which has the data type of "Date" but that contains a null value. Null values are skipped over by the Crystal summary engine so they don't count toward the minimum or the maximum. Here are the three steps to create a {@NullDate} formula:
1) Create a new formula, name it {@NullDate} and save it with nothing but the following function:
CurrentDate
2) Use it in the ELSE line of a formula like this and save that formula:
//Formula called {@AssessmentDate}
If {APP.TYPE} = "Assessment"
then {APP.DATE}
else {@NullDate}
3) Now go back into the {@NullDate} formula, delete the function and save it empty.
This generates a Null date. You can now use {@AssessmentDate} with both Minimum and Maximum functions and in both cases the summary function will ignore records that are not assessments. You can use the same {@NullDate} in the ELSE of several different date formulas if needed.
Linking tables without dragging
Unless you are using a command, you will need to link your tables in the "Links" tab of the Database Expert. And typically you create the link by dragging a field from one table and dropping it on a field from another table. This works fine in most cases, but can be awkward when:
The tables are far apart and not visible at the same time, like when you add a new table
The field you are linking to is near the top or bottom edge of the table
You are working on a slow connection
The first item usually requires dragging tables around the window, which is slow. In the second case the table has a tendency to scroll up or down quickly when you drag a field near the top or bottom edge. In the last case a slow connection or a screen delay can interrupt the signal, causing the field to drop in the wrong spot. All three of these can be fixed by using a nifty feature added to recent versions of CR.
When you want to drag Field A and drop it on Field B you can right click on Field A and in the fly-out menu you will see the words "Start Link". You select that and then scroll around until you see Field B. Then you right-click on Field B and select "End Link". This will create a link between those two tables, wherever they are on the screen. You can then hit "Auto Arrange" to bring the newly joined tables closer together.
And here are some other tips that you can use on the LINKS tab.
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.
Document from SAP on Excel exports
I have written several times over the years about exporting reports to Excel. My first post was in 2005 and follow-ups in 2008, 2012 and 2015. It took much trial and error but I eventually found solutions to most of the challenges.
So I was a bit surprised to find a 15-page document dedicated to exporting to Excel on the SAP website. It was originally published years ago, but exactly when is hard to say. The web page that hosts the document says it was created in 2008 and updated in 2012. But the page footer inside the PDF has a time-stamp from 2006 right next to a copyright of 2004. Obviously it isn't new and at least parts of it were available when I was struggling with these exports. It would have been nice to at least know about the document. In some ways it goes into more depth, like explaining how all of the export options work.
But my posts cover several points that are not covered or not covered clearly in the SAP document. For instance, they don't provide any help when it comes to subreport data. And, the document points out 4 reasons you will get extra rows:
1) Objects not horizontally aligned in a section
2) Objects in a section have different heights
3) Complex conditional suppression formulas.
4) Extra space at the bottom of a section (mentioned in the text).
But the document never clearly explains that extra space at the TOP of a section causes the same problem. It took me several years to figure that one out. In other words, the objects could be all the same height, be perfectly aligned, have no conditional suppression and all be touching the bottom of the section. Despite all that you would still get extra rows if there was a sliver of space between the top of the objects and the top of the section. To fix this you can use the "Size and Position" property to set the Y position to zero, but this can only be done one object at a time. Or you can just push the objects against the top of the section. One trick I use is to select all the objects in a section and align them, and then hit [CTRL]>[up arrow] until the row jumps to the next section. Then hit [CTRL]>[down arrow] once. They should jump right back into the original section and should end up at position zero.
So you should certainly download this document. I have, and have added it to my Useful Links page. But you should also note the links above to get the full picture.
Shared array variable vs recurring subreport
Sometimes a subreport has to be run for each group. For instance you might need to run a subreport once for each employee in a report. If you have a large number of employees the subreport is slow, it can make the report crawl. Customers often ask if we can run the subreport once for ALL employees and then pass all of those value to the main report.
In theory you can. You could write a subreport to read all employees at once, and then use an array in the subreport to store a value for each employee. You could then pass the entire array back to the main report by using a shared array variable. The challenge comes when you try to use those values in the main report. There is no simple way for the main report to match up the elements in the array to individual employee records in the main report.
The complicated solution is to create two arrays with the same number of elements. One stores the employee IDs, while the other stores the value for each ID. Each employee ID in the first array should line up with the corresponding value in the second array. To retrieve an employee's value in the main report you have to write a 'looping' formula. This formula will loop through the first array and count how many elements it has to check to find the matching employee ID. By using that count Crystal knows where to find the corresponding value in the second array. There is an example what the formula looks like on my blog, courtesy of Gordon Portanier of Crystalize in Toronto:
Not only is it complex to write, but it takes the report a long time to process, especially if you have hundreds of employees.
But, sometimes you get lucky and you can use a simpler method. I recently had a customer with a recurring subreport and that had to run over a thousand times, once per employee. The report took hours to run. As we talked about our options I realized that the employee ID was a number. This meant that I didn't need to create two arrays in the subreport and I didn't have to write a looping formula in the main report. Here's why:
Say the first employee in the subreport has an ID of 78. I could create an array of 1000 elements and put his value in element 78, based on his ID. If the next ID is 95 I can put that value into element 95, and so on. I use the ID as the position in the array. In the main report when I get to employee 78 I don't have to loop to find the right element, I just retrieve element 78.
The one challenge that we had to work around was that the numeric ID went up to 9000. In Crystal an array can only hold 1000 values. So I created 9 different arrays, each with 1000 elements. When I went to put a value into the array I took off the first digit. I used that digits to determine which array to use. I used the other 3 digits to specify the correct element to store or retrieve the value.
So if you have numbers under 10,000 as your ID (or if you can create an ID like this) you can use the simpler method for passing a list of values from the subreport to the main report.
Gems from the Archives
A few tricks I learned from my students (May 2005)
Copying and pasting cross-tabs to Excel (June 2005)
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 2016 by Ken Hamady
All rights reserved - Republishing this material requires written permission