Archive for the 'Tips' Category
Several of my customers read Excel spreadsheets, Microsoft Access MDB files and even text files using DAO connections. This are simple connections made by pointing directly to the data file. Unfortunately, these direct connections are no longer supported in Crystal Reports 2020. I was hoping that might be a temporary situation, but this link on the SAP website provides confirmation:
Crystal Reports 2020 is a 64bit application, and therefore it is no longer possible to connect to Excel or Access using DAO, since there is no 64bit version of this Microsoft Technology. This is why the option “Access/Excel (DAO)” is not available in Crystal Reports 2020.
You can still connect to these data sources using ODBC or OLEDB, but you will need the new 64-bit Microsoft driver. OLEDB actually works in a similar way to DAO, but you will need to do a “Set Datasource Location” in each report to convert it from DAO to OLEDB. I posted instructions in February for using OLEDB to connect to the Xtreme Sample Database (mdb) and the steps would be similar for other types of DAO connections.
If you run both 32-bit and 64-bit versions of CR you might want to switch to ODBC connections (DSNs). With ODBC you can create a 64-bit DSN on one PC and a 32-bit DSN on another PC using the same DSN name. That way you can maintain one rpt file and it can run in both the 32-bit and 64-bit Crystal environments.
I just started a project of reading through my old blog posts from the very beginning. I am deleting things that are obsolete and updating posts with newer links and information. I even expected to find a few useful things that I had written and forgotten. I found one in my 3rd post.
In August of 2006 I wrote about some new features introduced in Crystal Reports v11.5 (XI r2). One of them I don’t remember at all: hot-swapping fields.
Say you have a field on your report. It is the right size, in the right position and with all the right formatting. Then you realize that you should have used a different field. Crystal allows you to hot-swap another field into that place. All you have to do is drag the field onto the report from the field explorer and hold down the shift key. When the new field gets to be over the old field the new field will snap into place and you will see two gray arrows pointing in opposite directions. If you release the mouse at that point the new field will take the place of the old field, keeping the same size, position and most formatting properties. You can swap any type of field with any other (but not text objects).
I say most formatting properties because it will not keep conditional formatting formulas. But it will keep the formatting properties you can set from the toolbar (font, size, underline, color, etc.). If you want to apply the conditional formatting properties to the new field, you can copy the original field and paste it somewhere else before you swap it out. Then after the swap you can use the “Format Painter” (paint brush) to apply all the original properties to the new field. Then delete the extra copy of original field.
One other issue has to do with column headings. Some fields are tied to a column heading that were automatically generated by Crystal Reports. This happens when you start a report using the report wizard or if you place a new field on the details band. When you hot-swap a field tied to a column header the header will automatically change to the field name of the new field. If you prefer the original column heading you can make a copy of the heading before you hot-swap and place it somewhere else on the page. Then after the hot swap you can delete the new column heading and add the original one.
I had a project this week where I had to replicate the look of a very colorful spreadsheet. It featured 4 different shades of green. Matching an existing color in a Crystal Report usually involves a fair amount of trial and error, but I can usually get close. This time I was having a hard time matching a light green and it dawned on me that there might be a web site to identify colors. I had just gone to the paint store with a chip of drywall and came home with a gallon of paint that exactly matched a faded wall color, so I figured the odds were good.
I did a quick search and found many free sites where you can upload an image file and click on a color to get the RGB / HEX / CMYK codes for that color. I took screenshots of several spreadsheet cells, uploaded them and got instant matches. The customer was impressed and I didn’t have to do any guess work.
You can find a page full of sites with a search on “image color picker”. I like these two in particular because you can put in a URL for an online image file as well as uploading an image. Not all the sites had that part working well.
I just heard from Adam Butt of APB reports, who pointed out that there is a color picker tool in most image editing programs, including the classic MS Paint, Paint.net and Paint 3D. I have been using classic MS Paint forever and never paid attention to the eye dropper icon:
This lets you click anywhere in the image and gives you the color codes at that spot.
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.