The Crystal Reports Underground News - Volume 2018.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2018:
** Web based deployment options compared (2018)
** Renaming formulas in the Formula Editor vs the Field Explorer
** My library of Crystal Reports materials
** Handy copy tricks in Crystal Reports
** The power of a double click.
** Let me create your Crystal Reports
** Finding tables and fields in SAP B1, JD Edwards and Great Plains
** Finding changes in report output
** Open enrollment Crystal classes in Frederick, MD
** How to fix the error: "A loop was evaluated more than the maximum number of times allowed"
** i-Net Crystal Clear Reports
Gems from the Archives:
The index that ate my data January 2008
Getting vertical lines to stop at the bottom of a group January 2008
Web based deployment options compared (2018)
There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The "official" options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.
I have created a page on my blog that lists and compares these products, and I update it every January. This year the list features 10 products, one of which is new since last January:
Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
rePORTAL CR – a traditional Web portal
Bezlio – a SaaS Web viewer
ReCrystallize Pro – a launch page generator for the web
ReCrystallize Server – a server-based web viewer
Report Launch – a bridge between BO server products and server based applications
RapidStack – Web Portal service built around Business Objects Enterprise
The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (PDF) that shows some of the specifics for comparison, including prices. This year there are several new lines in the matrix. They show which tools encrypt credentials, provide system monitoring and allow you to launch reports from an external URL. If you have any feedback to share on these tools I would be happy to hear from you.
Renaming formulas in the Formula Editor vs the Field Explorer
I write many formulas, and the more formulas there are in a report the more import it is to name them well. About 10 years ago I wrote an article with tips for naming formulas. I will often come back and rename formulas when I find that the original names aren't clear enough. One of the main points of that old article was "don't be afraid to rename formulas".
But there is one thing I didn't mention in that article. You get a slightly different behavior if you rename formulas while you are inside the formula editor as opposed to if you rename formulas in the Field Explorer. The difference has to do with the formulas being listed in alphabetical order. When you rename a formula in the Field Explorer that field gets repositioned immediately based on the new name. But, when you rename a field in the Formula Editor the field doesn't get repositioned immediately. The formulas won't be resorted until the next time you modify and save a formula's contents.
Say I have 50 formulas in my report and I want to rename 12 of them from "Payables 01", "Payables 02", etc. to "AP01", "AP02", etc. If I am in the Field Explorer and rename "Payables 01" to "AP01" that formula (and my cursor) would be immediately moved to the "A" section of the formula list. To rename "Payables 02" I would have to pick up the mouse and scroll back to the "P" section of the list to find it. That formula would also move immediately to the top of the list, and so on. But, by renaming these fields in the Formula Editor I can rename the first formula, hit "Enter" and simply use the down arrow to move to the next field. All 12 fields stay together during the renaming process and they don't get alphabetized right away. When I am ready to alphabetize the list again I select any formula and add a space to any line and then hit "Save". The list gets alphabetized and the 12 renamed fields move to their new positions.
Note – you can't just click save. You have to modify a formula and then save it. That is why I typically add a space and click save.
So next time you want to rename a group of formulas you can use this trick to save a bit of scrolling.
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.
Handy copy tricks in Crystal Reports
I have written before about copying objects, both within a single report or from one report to another. Below are some new tips for copying values instead of objects.
Copying literal values from preview mode:
Often when validating or troubleshooting a report I want to take a specific item from the preview screen (an invoice number, customer name, etc) and look it up within the application. I have learned that you can copy a value directly from the preview screen and paste that value into another application. Or, I may need a specific value from preview to use as a literal value in a formula. All you have to do is select the value you want and then copy it and paste it into the formula. This can be especially helpful when the value is long or difficult to type correctly. All three copy methods work (Ctrl-C, right-click or the edit menu).
The value that gets copied to the clipboard will match what you see in preview. Numbers and date values will be pasted in the same format as they appear in preview. Fields that are not large enough to display the entire field value will still copy the entire value of that field. So truncated text or large numbers that show as pound signs will both paste the full value.
Copying object names from design mode:
Often when writing a formula I find that I need to insert a database field or formula field that is sitting on the report. Rather than trying to remember the name of the correct field, it is possible to copy the name of the object while in design mode. Just select the field you want and copy it using one of the three methods mentioned above. When you copy a field in design mode you are copying the name of the field.
You can then paste the name into the formula editor and put curly brackets around it. Crystal should recognize the field. If it is a formula field your pasted value will include the @ symbol. I have found that the most efficient method is to first type the open curly bracket. This opens the auto-complete list with names of all available fields. Then you past in the copied field name and Crystal selects the correct field from the list. When you hit enter it adds the closing bracket automatically.
This is especially helpful in complex reports when you have many tables and fields, or when the field names are particularly long and hard to remember.
The power of a double click
I do lots of work using GoToMeeting and often the screen I see is reduced in size, making things hard to read. It can be frustrating when trying to highlight a word without including characters on either side. So I have developed the habit of using double-clicks to select words. I find that not everyone knows this trick.
When selecting normal text, like in MS Word or in a browser, you can double click on a word to select the entire word. In many applications the word selected also includes an extra space on the end. This allows you to move the word to another part of the sentence without having to add and delete spaces. (In a few applications you can even use a triple-click, which selects an entire paragraph. This works in most browsers and MS Word and Wordpad. )
I also use double-clicks when working in the Crystal Reports formula editor. If you double-click on literal word (within quotes), Crystal will select that word (but no trailing spaces). If you double click inside a field name Crystal will select either the table name (before the period) or the field name (after the period). If you double-click on the name of a variable or function, Crystal will select that name with no spaces.
I find this very helpful when copying and pasting values.
I also use Notepad++ to write both Crystal formulas and SQL commands. When you double click a word in Notepad++ it not only selects that word and highlights it; it also highlights every other instance of that word in the document. This makes it easy to see where a specific table, field or value is being used.
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.
Finding tables and fields in SAP B1, JD Edwards and Great Plains
I was looking for a list of the tables and fields in SAP B1 and found a great site:
http://www.saptables.net/
The person that maintains this site also maintains a similar site for the JD Edwards application
http://www.jdetables.com/
So while I was at it I thought I would see if anyone had done something similar for Great Plains. I found a few sites.
https://dyndeveloper.com/DynModule.aspx (some info members only)
https://victoriayudin.com/gp-tables/
http://gptables.azurecurve.co.uk/
This type of information is usually available directly from the vendor, but often those resources are restricted to licensed users with a support account. The sites above are open to the public. If you know of has a similar site for another application, let me know and I will add it here.
Finding changes in report output
I often work with large and complex reports. Sometimes making a minor change can have unexpected consequences. I like to be able to see that the only things that changed are the things I intended to change. If the report is long or dense it can be a challenge to identify changes. But I recently worked out a relatively simple way to identify all of the values on a report that have changed as the result of my formula changes. I use the "compare" add-on in NotePadd ++.
So the first thing I do is refresh the 'before' report so I know that I have up to the minute data. Then I export the entire report into TXT format, creating the file before.txt. Then I make my change(s) and export the entire report a second time into TXT format, creating the file after.txt. Now I open these two files in open NotePad++ and run a compare. All of the differences will be highlighted and it is easy to see all the changes.
For example, one of my upcoming assignments is to simplify the formulas in a complex report without changing the output. So my plan is to use this method after each round of changes. Since nothing should be different, any changes I find in the compare process will be a sign of a mistake.
If you haven't tried NP++ (which is free) you can read more about it here, including the best place to download it.
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.
How to fix the error: "A loop was evaluated more than the maximum number of times allowed"
A customer recently asked for help with a formula. She was creating an index at the end of a report to show page numbers for each item within the report. Her formula used nested FOR loops to put the elements of the array in alphabetical order. It was similar to this handy formula that I was given once to do a "bubble sort":
WhilePrintingRecords;
stringvar array MyValues;
numbervar i;
numbervar j;
stringvar temp;
FOR i:=1 to ubound(MyValues)-1 DO (
FOR j:=1 to ubound(MyValues)-i DO (
if MyValues[j] > MyValues[j+1] then (
temp := MyValues[j];
MyValues[j] := MyValues[j+1];
MyValues[j+1] := temp;
)));
join(MyValues,",")
The problem we had was that the formula would work fine for small arrays, but would generate an error on a larger array. The error would say:
"A loop was evaluated more than the maximum number of times allowed"
The maximum number of loops in a single formula evaluation is 100K, so usually this error means that you wrote an infinite loop. But in this case it was legitimate. Because an array can be up to 1000 elements, and we are doing one loop within another, this formula could theoretically generate up to one million loops. A correctly done bubble sort for 1000 elements should max out at about 500K, well over the limit.
To stay under the limit on longer reports, we separated the two loops. With the formula above we wait until the end of the report and do the sorting all at once. Instead I added a sorting loop to the formula that adds each element to the array. This way we sort the array as the elements are added. And because this formula evaluates each element separately, the maximum number of loops at one evaluation would be just the size of the array. Problem solved.
But in doing my research on this I learned something new. I learned that 100K loop limit isn't a hard limit. You can override this limit to make it both smaller and larger as needed. All you have to do is start the formula with a line like this:
option loop 200000;
This would double the limit from the default of 100k to 200K, making more nested loops possible. Crystal let me set the number at one trillion without complaining, although I didn't actually try to create a loop that ran that many times. I still prefer to avoid nesting loops when possible, but this might solve a problem some day.
i-Net Crystal Clear Reports
I received a call this week from a potential customer who said he wanted me to help him modify some reports. But then he started talking about iNet Clear Reports. I told him that I did Crystal Reports and had never heard of Clear. He said that Clear was "just like" Crystal and he was convinced that I could help him.
While we were talking I did a quick web search and found an image of the Clear design environment. I was surprised at how much it looked like the CR design environment. I also saw that it was previously named "Crystal Clear" which I remember reading about years ago. So I decided to remote into his PC and see what the tool was like.
As soon as I started exploring a report I found that most things were pretty much where I expected them. For example, the report sections were named the same way and had very similar properties. The field explorer had the same nodes. The formulas were written in either "Crystal Syntax" or "Basic Syntax". In the end I was able to make most of the required changes to the report, including adding a SQL Expression and fixing a formula. My learning curve was pretty mild.
After we were done I downloaded the iNet Clear Report Designer (Windows) and played around with it. It is clearly modeled on Crystal Reports, with many features copied exactly. There were many differences but they were both positive and negative. For instance there is no way to modify the report while in preview mode. And some simple changes generated odd errors.
On the other hand, the Clear designer can be run on Linux. I liked the ability to add SQL for a dynamic parameter right in the parameter window. I also liked that the Field Explorer shows the data type for all field types, including parameters, formulas and SQL Expressions.
I still prefer Crystal Reports, but I plan to study the features of iNet Clear Reports and eventually add it to my comparison of reporting tools. And since it is so similar it will probably end up listed on my consulting page.
Gems from the Archives:
The index that ate my data January 2008
Getting vertical lines to stop at the bottom of a group January 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