Archive for the 'Tips' Category
When working with customers I often re-open recently used files. Recently it seems that some of my customers versions of Crystal don’t show the recent files in the file menu. This puzzles me because I am on CR 2016 and I have always seen the list of recent files at the bottom of the File menu. There is a short list on the start page as well, but the longer list has always been in the menu.
Yesterday one of customers shared with me that she had been struggling with the same issue and then stumbled across the recent file list in a new place. There is a yellow folder icon on the toolbar which represents “File > Open”. Next to this folder is a small drop-down arrow. Clicking that arrow shows the recently used files. Neither she nor I had noticed this so I am betting that we are not the only ones.
And, thanks to Laurie Weaver, a developer at Wyse Solutions, for pointing this out.
On a related note, you can change the number of files that are shown in this list. The default is 5, but you can increase this number in the registry.
The key is here:
Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\
Crystal Reports\Recent Files
The value to change within that key is called “FileCount”.
But there is also some strangeness about the filecount value. You can put in any number, but the registry only has 10 slots. So any number beyond 10 has no where to go. Then, the list in the file menu can only display the first 9 items of the 10 so I am not even sure why they have a 10th item. The other list, the one in the box on the Start Page, can only show the first 5 of the 10.
The other mystery I haven’t solved (yet) is why my install of CR 2016 still shows the recent files in the “File” menu, while many Crystal installs do not.
In older versions, all you had to do to retrieve your Crystal Reports license key was go into Help > About. That screen would show the key and your registration number (if you registered the software). In more recent versions the key is no longer there. There is a license manager under the [Help] menu but it only shows you the first few and last few characters of your license key. I assume this was intended as a security measure. However, if you need to reinstall Crystal Reports when you upgrade your hardware you might struggle a bit. Here are three other ways to find your key:
- Check your Email. Most installs are downloads and the key is Emailed to the person making the purchase. You might have received that Email or had it forwarded to you.
- Call SAP Sales. If you purchased it directly from SAP (the most common option these days) they should be able to look up your account and give you the key.
- Or, my favorite, pull it from the registry. You will probably find it in this registry key:
Suite XI 4.0/Crystal Reports/Keycodes/CR Dev
The registry key will contain the license key followed by an 8-digit numeric date, separated by a colon.
Whenever you add a group to a Crystal Report, Crystal creates and inserts a field called the “Group Name” as a heading for each group. These values are also what you see in the group tree along the left of the screen. In most cases the group name simply shows the values of the group field, but there exceptions. For instance, when you group on a date field you can choose to group “for each month” or “for each year”. The group name would display only the month or year from the field. If you do a TopN or use Specified Order you may get a final group called “Others”.
Crystal also gives you the ability to customize the group name. You can either pick a different database field to display as the group name, or you can write a formula to be the group name. For instance you might group by a Customer ID, but choose the Customer Name field as the group name. Or you might group by Employee ID, but create a formula that combines First Name and Last Name to be the Group Name.
To make this happen you go into Group Options (Report > Group Expert, Options button then Options tab) and check “Customize Group Name Field”. You will be given the choice of selecting another database field or creating a formula expression using the X+2 condition button.
Here’s a tip – when I want to use a formula expression for a group name, I typically write the formula as a separate formula field. Once that is saved I will go into the group options and put my formula field into the condition formula. If the logic ever needs to be changed it is simpler to modify a formula field than it is to get into the expression editor within the Group Options.
Note that you can also customize group names in cross-tabs, using the “Group Options” button for either the row or column fields. The interface is the same.
There are several ways to create totals in Crystal. This week I solved problems for two different customers by changing the type of total they were using. I will give a short explanation here, but if you want to really understand this topic you should download the Expert’s Guide to Totals in Crystal Reports from my website ($12). It comes with example reports and exercises.
The primary method for creating totals in Crystal is to add summary fields. In the menu use Insert > Summary or find the Sigma symbol on the tool bar. Some users default to using running totals because they see them listed in the Field Explorer. But summary fields have several advantages over running totals. Summary fields can be:
1) placed in the Group Header as well as the Group Footer
2) used in the Group Selection formula as a group level filter
3) used in Group Sorting to rank the groups in order based on a subtotal value
4) Copied to other group/report sections to create additional summaries
You can’t do these with running totals, so my first choice for creating any total is to use a summary field.
But there are specific situations when a running total will solve a problem that you can’t solve with a regular summary field:
1) If you want to watch the value change, row by row, like the balance in a checkbook
2) If the column you are totaling has duplicates and you need to skip the duplicates
3) If you use Group Selection and then want a total of just the groups that meet the criteria
4) If you do a TopN without others and need a total that doesn’t include the others
Some users also use running totals because you can apply a condition directly to the total. This way the total only includes records that meet a specific criteria. But if you write an If-Then formula with your condition you can use a regular summary field and get the same result. This gives you the conditional total without giving up all the advantages of summary fields.
And, if you get really stuck on a total issue, you can call me for a short consult.
In my last post I wrote about overriding the limit on List of Values for a dynamic parameter. That requires changing a registry key. In doing the research for that post I found an SAP web page that lists 140+ registry keys for Crystal Reports. Most of the keys have a short comment about their purpose and a link to a knowledge-base article. Unfortunately, many of the articles no longer exist on the SAP web site.
Some of the keys are esoteric, and won’t be useful to most users. But I found a few that I thought were interesting.
For instance, I have written about something called the ‘batch interface’ for parameters. This is the little control panel that appears whenever you have more than 200 values in your parameters list. This control shows you the values in batches of 200. Apparently the number 200 is a registry value that you can change using the node and key:
…\Crystal Reports\Reportview\ – PromptingLOVBatchSize (200)
Then there are several nodes that remember where toolbars and formula editor panels were located the last time they were used. They should open in the same place the next time. But sometimes when you change screen resolution or switch from two monitors to one, these locations might be off the screen. The following registry settings are sometimes helpful in getting them back.
…\Crystal Reports\Formula Workshop
Editor Position (10,10,10,10)
…\Crystal Reports\Formula Workshop\Formula\
Field Tree: Toolbar-Bar2
Function Tree: Toolbar-Bar3
Operator Tree: Toolbar-Bar4 – Docking Style (f000)
You can check out the complete list on the page above. And if you do decide to experiment with your registry – make sure you create a backup of it first.
I have written before about dynamic parameters and the fact that they are limited by default to 1,000 values. This is more noticeable when the dynamic parameter is a cascade of several columns. The cascade is pulled from a query that assembles all the valid combinations of the fields in the cascade. It is the total query that is limited to 1,000 records by default, not each field in the query. If you want to raise that limit you have to go into the registry and add a key.
There are two branches in the registry where you can make this change. One branch (Current user) makes changes that only apply to a specific user. Another branch in the registry (Local Machine) makes the change apply to ALL users.
I was making this change recently for a customer and had some trouble with the Local Machine branch. The Current User branch worked fine. What I eventually realized is that the Local Machine branch node I was using was for 32-bit computers. I am so used to thinking of Crystal as a 32-bit product that I used the 32-bit location out of habit. But you have to use a different node when you are running Crystal Reports on a 64-bit computer. In this registry change it doesn’t matter if Crystal is 32-bit or 64-bit, if you are on a 64-bit computer.
I updated my original article to give the correct locations for both.
If you have installed the new Crystal Reports 2020 you may have noticed a new menu option and toolbar for SAP BW.
Gordon Portanier of ReCrystalize decided to do some experimenting to see if he could get CR to run without those options appearing. He found that when he renamed these 4 dll files, both the menu option and the toolbar didn’t appear :
He found the dlls in this folder:
C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64
The program seemed to run normally without these dll files. So if you decide to upgrade and don’t have a use for the extra menu options, this is something you can try.
I like to highlight key objects in my reports. For instance I highlight small subreports that might be confused with regular fields, or that might not be noticed at all. I also highlight formulas that involve variables as a reminder that they shouldn’t be deleted or moved. (Usually a specific location is required for variables to work correctly.) I will sometimes highlight several formulas with the same color so that I can quickly see which formulas work together. This is simple when the formulas are suppressed or when they are in a suppressed section, but sometimes the object is visible. In that case the object highlighting should only be visible in design mode and not in preview mode. Here’s how you do that.
Most Crystal formatting properties have a formula condition button [x+2] to the right of the normal controls (check mark, drop down, etc). This allows you control that property based on parameter values or data values. When you use a condition formula there is no need to set that same property using the normal controls. If you set a formatting property both ways the normal control setting will apply to design view while the formula will apply to preview.
So say I would like a field to have an aqua background in design view but no color in preview. I would first format that field by selecting aqua from the drop down. Then I would go into the condition formula and type: CrNoColor
The field will have an aqua background color in design view but no background color in preview.
This is better illustrated with pictures. Recently a customer had a Cross-tab that looked like the image on the left below. But they wanted the first column to repeat next to each value in the second column, like the image on the right below.
I found a relatively simple way to do this:
- I created a formula the combined the two column fields into one string, with a dash between them.
- I went into the Cross-tab expert and clicked the “Group Options” button for the first column field. I changed it to the new formula.
- I then clicked the”Options” tab and checked the option “Customize Group Name Field”.
- I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
- I went to the the last tab in the Cross-tab expert named “Customize Style” and highlighted the concatenated formula in the “Rows” box.
- I checked the option “Suppress Subtotal” (if not already checked) and clicked OK.
The cross-tab then looked like the one on the right.
I have written before about data type changes in Excel. One article explained how to convert a column of numbers into character values so that they can link correctly to another column of character values.
Today I was asked how to do something similar with dates. Two spreadsheets were being linked on a date field, and Crystal was reading one field as a true date and one as a string date. I found a simple way to get Crystal to recognize the string as a date. I created another column that was simply the Date String column plus zero [e.g. =A3 + 0]. This turned the new column into a number representing the date. Then I formatted that column as dates and that allowed Crystal to recognize the new column as a date.
It surprised me to find that I had to format the field as a date. When it comes to dealing with strings and numbers the formatting is usually irrelevant, but apparently with dates the format is important. When I tried to skip formatting the column as a date, Crystal read the new column as numeric values.