Archive for the 'Tips' Category
When you preview a report in Crystal the left side of the screen should show you the “group tree”. This lists all of the groups in the report. It also allows you to go directly to the first page of any group, just by clicking on that value in the tree.
But a few times a year I work with a report where the group tree is in “Only Drill-Down” mode (ODD). In this mode, every entry in the group tree is accompanied by the drill-down indicator (a magnifying glass). Clicking on an entry no longer takes you to the first page of that group but instead it takes you to a drill-down tab for that group. To get to the correct page for a group I have to do a search.
It is a minor irritation so I have let it go for years. It just never seemed worth the time to figure out why some reports do this. But I figured it had something to do with the Hide/Suppress properties of the Group Header (GH) and Group Footer (GF). This week I got an ODD report from a customer, and so I decided to test all the combinations and see which ones were ODD.
I found four rules that control this behavior:
- If either the GH or the GF is visible you get the normal group tree.
- If both of those sections are suppressed you get the normal group tree.
- If both of those sections are hidden you get the ODD behavior.
- If one of those two sections is hidden and the other is suppressed you get the ODD behavior.
I can’t explain the reasoning behind this pattern (or even the purpose for the ODD behavior) but at least now I know how to change it when I see it.
My customers use a wide variety of Crystal Reports versions. Just this past week I fixed a report for a customer using CRv8.5, which was released somewhere around 2001. There have been 8 newer releases since then and most have included new design features. Sometimes I forget which versions are needed to do certain things. Often I have tried to update a report for a customer, only to realize that my Plan A won’t work in their version. Whenever I need to check when a specific feature was introduced, I refer to the front page of my site which has links to my reviews for each new Crystal release. These articles include the list of new features for each version. But, this still means scrolling through through several pages to find each feature.
To make the process simpler I have created a grid that lists all of the new features added in the past 15 years, roughly 50. For each of the features I show when it was introduced, and which versions that support that feature. Not only will this help me identify which features I can use for specific customers, it will also help me answer questions about the value of upgrading. I can point a customer to the grid and they can quickly see all the features that have been added since their version. They can then decide if those features are worth the cost of the upgrade.
You can check out the grid on my site. The features listed in blue are the ones I use the most.
I have written once before about using the “Order Links” feature of the database expert. In that article I used “Order Links” to help me prevent a SQL error in the pervasive database engine. This past week I used the same feature to improve the performance of a report.
This report had many tables but there was one, the Dept table, that provide the primary filter for the report. The user would select a specific department each time they refreshed. But when I looked at the SQL generated by CR, I noticed that the Dept table was the last table joined into the data set. In my mind, that meant that Crystal was bringing in thousands of linked records that would eventually be discarded because they were linked to the wrong department. If the SQL could apply the filter up front we would greatly reduce the number of linked records we would generate, and that would speeds things up.
So I suggested to the user that they go into the “Links” tab of the Database Expert and right-click to find the “Order Links” option. I had them move the Dept join to the top of the joins list and they immediately saw a dramatic improvement in performance. It may not work in every report or with every database (this report was Oracle based), but when there are lots of tables and the report is slow, it is another option to try.
One method for selecting multiple objects in Crystal (like most Windows programs) is to “lasso” them. To lasso objects in Crystal you click and hold your mouse button in an empty spot and then drag the cursor to expand an orange rectangle. When you release the mouse button, all of the objects inside (or touched by) the orange rectangle become selected objects.
The problem I often have with this method is that drawing objects (lines and boxes) often get selected as well. For instance, if you lasso a group of objects that are inside a box object, that box will often be selected. Usually this happens if you select the highest object in the box, even if you don’t touch the box itself. If your lasso box touches or surrounds line objects those lines are also selected.
Often I want to select a column of numbers for alignment or formatting and I want to skip over the lines. Before now my options have been to lasso the objects and then carefully deselect the lines and boxes by holding my [ctrl] key and then clicking those objects. Or I could skip the lasso method altogether and select that group of objects by clicking on them individually while holding down the [ctrl] key.
But this past week I discovered something I had never noticed before. If you use the lasso while you are in preview mode, the lines and boxes are NOT selected. I had created a grid of 40 numbers (4 across and 10 down) and had separated the rows and columns with horizontal and vertical lines. Plus there was a box around the perimeter. To select all the numbers individually in design mode would have taken 40 clicks. The lasso would take 13 clicks to deselect all the lines and the box. But while in preview I could select all the numbers in one lasso and none of the drawing objects were selected.
This made it easy to align the columns or change the format for the numbers as a group.
I was looking for a list of the tables and fields in SAP B1 and found a great site:
The person that maintains this site also maintains a similar site for the JD Edwards application
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.
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.
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.
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.
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.
In recent years, I have been doing lots of SQL command-based reports, which means writing lots of queries in every imaginable flavor of SQL. It was initially frustrating trying to keep the syntax changes straight and I got tired of doing web searches for the same functions over and over.
My solution was to create a “cheat sheet” grid to store my most commonly used SQL functions and the correct syntax to use in each of the database platforms. Once I looked up a function, I would add it to the grid and now the grid answers most of my questions. There are about 40 functions listed and I have most of them completed for the 7 flavors of SQL I see most (plus Crystal Reports formula syntax):
- Microsoft SQL Server
- PostGre SQL
- MS Access
I have empty columns for Providex and DB2. They are empty because I haven’t had any recent projects on those platforms and don’t have an environment where I can experiment.
I hope some of you will find this grid useful. You can also share the file with others as long as you leave the heading in place. If any of you want to fill in some of the gaps or suggest an improvement, your contributions would be appreciated. And thanks to John Pelot of Skyward, Inc for filling in many of the progress functions.
I wrote a long time ago about the “Stealth” subreport. This is an invisible subreport that runs in the background and provides a value to the main report as a shared variable. But if you have a few of these subreports in the same container report it can be difficult to do troubleshooting. To see what the subreports are returning, you have to go into each of the subreport’s key sections to unhide or unsuppress them.
But I received a suggestion from Gordon Portanier that makes it simple to activate and deactivate “stealth” mode in all of the subreports in one place. First you write a formula like this and place it in the report header of the main report:
Shared BooleanVar Stealth;
Stealth := True;
Then you go to each of the sections in the subreport(s) that you want to turn on and off when troubleshooting. Instead of hiding or suppressing those sections, you put in a suppress condition that says:
Shared BooleanVar Stealth;
As long as the first formula isn’t modified, those sections should stay suppressed and the subreport should stay invisible. When you want them to appear you comment out the bottom line in the first formula, the one that assigns the value TRUE to the variable. Because Boolean variables are FALSE by default the variable will revert to FALSE and all the sections where you used that condition will now appear.
So, thanks again to Gordon Portanier of Crystalize in Canada for sharing this suggestion.