Archive for the 'Tips' Category
When you set up Windows you get to select a Regional Format for things like dates and times. For instance, English(US) format will show today’s date as 7/21/2021 while English (UK) will show today’s date as 21/7/2021. In Crystal, certain date objects can be assigned to use these formats, so that they automatically change from one format to another based on the regional format setting of the PC running the report. This is handy if you have users in different countries. To use this feature, right-click on the date field, choose “Format Field” and on the “Date” tab select the “System Default Long/Short Format”.
However, I just had a user complain to me that he couldn’t get CR 2016 date fields to respond to the regional setting. So, I did a quick test in my own environment and found that changing my regional Format from English (United States) to English (United Kingdom) had no effect on date format in CR 2016. I closed Crystal and reopened it, created a brand new simple report and put the print date in the Page Header – and it still showed in US Format. To make sure I was using the right setting I tried the same test in CRv10 and the print date came up in UK format. I repeated the test in CRv8.5 and it also came up in UK format – but not in CR 2016.
I did an online search and found an SAP thread where someone else had the same issue when they upgraded to CR 2016. SAP’s response focused on the database client (?), but the issue didn’t appear to have been resolved.
I wonder if Crystal is somehow pulling the regional format correctly when it is installed, but then isn’t updating the region if it is changed after the install. If anyone is seeing different behavior or has insight to share, please let me know.
Some of my customers are transitioning from 32-bit versions of Crystal Reports to Crystal Reports 2020, which is 64-bit. This creates some issues since ODBC/OLEDB drivers are either 32-bit or 64-bit. Switching to CR 2020 requires different drivers and (if using ODBC) different ODBC Data Source Name (DSN) entries.
The same applies to UFL function DLL files. For instance the free ufl named u2lwin32.dll only comes in a 32-bit version. Some commercial ones come in both 32-bit and 64-bit editions. Switching to CR 2020 requires that you use 64-bit UFL libraries.
The good news is that RPT files in CR 2020 are backward compatible with earlier versions, probably back to Crystal Reports v9. So you can modify and run a single RPT file in both CR2020 and earlier versions. But you do need different connectivity. What my customers are doing is naming the connections the same in both the 32 and 64 bit environment. That way users in both environments can run the same report without modification.
If you run into any strange behaviors in using CR 2020 or in the transition, please let me know and I will share with others.
I finally had a use for a feature in Crystal Reports that I never use. It is called the Workbench. It is a place where you can create shortcuts to rpt files, and then organize them into projects. I was working on a report that was similar to some other recent projects and I wanted to keep the example reports handy (but not all open). By adding all the reports to the Workbench I could open and close them as needed without having to hunt for them each time. And these shortcuts didn’t roll off like files in the recently used file list.
To activate this feature you go to the VIEW menu and select “Workbench”. You can right-click to add a new project, or to add reports to an existing project. You can also move report shortcuts from one project to another by dragging them up or down. To open a report you right click on the shortcut and select open. The interface is simple and intuitive.
I work with many different CR users. It seems that whenever I open the Report Explorer view in Crystal Reports, the user is a bit surprised. I get the impression that not many people use or know about this feature. I wrote about it once before (a decade ago) but since then I have found two more uses that I tap into regularly.
1) Selecting one of several superimposed objects.
One report I created for an educational assessment company had 4 superimposed picture objects in different colors. They were all in the same spot, but had suppress conditions so that only one would appear at a time. Trying to select a specific one of these objects is a challenge. But when you open the Report Explorer for that section, the objects are listed separately. You can select the object in the list of the Report Explorer and it behaves the same as when it is selected in design mode. You can also right-click on the object in the list and get all the same options you would get if you right-clicked the object in design mode.
2) Locating subreports
I recently had a very crowded report and was having trouble with a shared variable, that came from a subreport. The trouble was that the subreport was small and I was having trouble finding it. One of the features of the report explorer is that you can decide which of three object categories to have it show (Grids and Subreports / Fields / Graphic Objects). By turning off Fields and Graphic Objects the list showed only Grids (cross-tabs) and Subreports. This made the one lone subreport simple to find.
So if you haven’t ever used the Report Explorer, go into the View menu to activate it. You might find it useful.
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.