Archive for the 'Tips' Category



How to remove the SAP BW toolbar in Crystal Reports 2020

Friday 21 August 2020 @ 10:13 pm

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 :

crdb_bwmdx.dll
crdb_bwmdx_res_en.dll
BWQueryReportWrapper.exe
BWQueryReportWrapper_res_en.dll

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.




Objects highlighted, but only in design view

Saturday 25 July 2020 @ 6:16 pm

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.




Repeating the first columns of a cross-tab for each value in the second column

Friday 8 May 2020 @ 11:44 pm

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.

cross-tab without repeating the first column

I found a relatively simple way to do this:

  1. I created a formula the combined the two column fields into one string, with a dash between them.
  2. 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.
  3. I then clicked the”Options” tab and checked the option “Customize Group Name Field”.
  4. I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
  5. I went to the the last tab in the Cross-tab expert named “Customize Style” and highlighted the concatenated formula in the “Rows” box.
  6. I checked the option “Suppress Subtotal” (if not already checked) and clicked OK.

The cross-tab then looked like the one on the right.




Reading an Excel column as a true DateTime rather than a string.

Wednesday 29 April 2020 @ 10:43 pm

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.




The Crystal Reports label wizard

Sunday 26 April 2020 @ 10:36 pm

Every version of Crystal Reports in the past 20 years comes with a neat little label wizard that allows you to automatically set up about 30 common Avery label sizes. But there are a few things you need to know to avoid problems.

Tip #1) A report created using the label wizard can’t be changed to a regular report, and a regular report can’t have one of the label sizes automatically applied. You have to decide to use the label wizard when you first create the report. The wizard is found under the menu options “File > New > Mailing Label Wizard”.

Tip #2) The wizard lets you pick the fields you want, but it will put each field you select on its own line. So, if you create an address label and add the fields City-State-Zip, the wizard will put those fields on 3 separate lines. I add only the first field of each line while I am in the wizard. Then when I preview the report I can add the other fields, or write a formula to combine all of the fields that you need.

Tip #3) This label wizard always puts the label fields on subsections of the details band. If you want to print one label per group, don’t try to move the fields to group headers or you will make lots of unnecessary work. Instead you can make the detail labels appear as if they are at the group level by only printing the first detail label of each group.

To do this you:

  • Add the group to the report
  • Suppress both the Group Header and the Group Footer.
  • Create a running total field that counts the records in each group(reset ‘on change of group’).
  • Suppress the Detail sections or the Detail parent section with a condition that says:

{#running total} > 1

Only the first record (label) of each group will print. This allows you to use the wizard to get the layout automatically, but still print one record per group.




A way to speed up repeated Crystal Reports installations

Sunday 19 April 2020 @ 8:56 pm

Last month I wrote about a difficult time I had installing Crystal Reports. One of my complaints was that if the install fails, you have to start over – including waiting 5-7 minutes while the setup ‘unpacks’ all the files. It seems like there should be a way to keep the files unpacked when an install has to be repeated.

Well SAP doesn’t provide an official way to do that but one of my readers (who apparently does lots of CR installs) shared his method. Here are his steps:

1. Run the install .exe.

2. Wait until everything has been unpacked. If you aren’t sure, wait until it asks for the license key. Then go to this folder, substituting your user name in the path:

C:\users\<username>\AppData\Local\Temp

Look for a folder in Temp that starts with “RARSFX”.

NOTE: AppData is a hidden folder, so you’ll have to set Explorer to view hidden items.

3. If there’s more than one “RARSFX” folder, delete all of them except the most recent one. These are failed installs that didn’t get deleted correctly.

4. Copy the remaining folder somewhere else. You cannot cut and paste because the installer has those files open. You might want to give the folder copy a more meaningful name.

5. For subsequent installs, go to this new folder and run setup.exe.

So if you are troubleshooting a balky install, or if you have to install CR on several different workstations, the steps above will speed up the iterations. And, thanks to Dell Stinnett-Christy, a senior manager at Protiviti, for sharing this method.




Using the ‘parent’ section in the Section Expert

Sunday 23 February 2020 @ 7:24 pm

If you have ever done a page break or a suppress condition in Crystal Reports, you have been in the Section Expert. This is where you can change any of the formatting properties of the sections. And if you have ever split a section into subsections, you know that each subsectioin has their own set of properties in the Section Expert.

But users don’t always notice that whenever you split a section into subsections, the parent section still exists in the Section Expert. So when you split the Details section in to Details A and Details B, there is still an item called Details in the list of sections, and this ‘parent’ section has properties that apply to all of the subsections at once. For example:

  1. If you check “Keep Together” in the parent section Crystal will try to keep all the subsections together on a page, and if they won’t fit will move them ALL to a new page.
  2. If you put a suppress condition in the parent section it will suppress ALL of the subsections at once.
  3. If you check “New Page After” in the parent section it will generate a page break after the last detail subsection, even if you rearrange the details.

You might find that this helps simplify formatting sections with subsections.




Deleting fields without losing (linked) column headings

Tuesday 24 December 2019 @ 5:14 pm

Whenever you drag a new database or formula field onto the details band, Crystal will add a column heading for you, automatically. This heading is just a text object but it will stay internally linked to that field. If you move the field horizontally, or change its width, the heading will adjust to match.  If you delete the field, the heading disappears as well.

This can sometimes present a challenge. For example, when you have formatted and aligned column headings and you need to replace the fields below them. When you delete the original fields the headings will also be deleted and then you have to recreate them and reformat them.  I was in this situation twice in the past few days. In one case I had to replace a group of tables with a SQL command, in the other I replaced a SQL command with a subreport. In both cases the database fields on the report had to be replaced. And as soon as I deleted the original fields I saw that the headings also went away. I didn’t want to recreate the headings to match the old ones, so I hit undo and did some experimenting.  I learned two things that allowed me to keep my original headings in place even after the original fields are deleted:

  1. If you copy the headings and paste a second copy of them somewhere else on the report, Crystal will only delete one set.
  2. If the new headings are in a separate section Crystal will delete the headings in the higher section.

So my new approach for this scenario is to:

  • Split the section that contains the headings to create a new temporary subsection.
  • Drag the new subsection to be above the original section.
  • Copy the headings and paste the copies into the new blank section.
  • Insert the new fields into the new subsection (so they won’t create new headings)
  • Use the format painter to format the new fields based on the format of the originals.
  • Use the menu (format > make same size) to size the new fields to match the originals.
  • Delete the original fields, which should delete the new headings in the higher section.
  • Move the new fields to the original section and align them with the original headings.
  • Delete the temporary subsection.

This process adds a few extra steps, but it keeps your original headings exactly as they were and allows the new fields to look exactly like the ones that they replaced.




No column headings on the last page (v2.0)

Tuesday 29 October 2019 @ 7:29 pm

Last month I wrote an article about suppressing the page header on the last page when there are no details. This is handy if your last page is a subreport, a chart or a cross-tab. After my newsletter went out one of my readers shared her approach to the same problem. She uses the group header of a dummy group, and sets it to “repeat” on each page.

Any time you have a Group Header you can set it to repeat on each page. One feature of  a repeating GH is that it won’t appear on the last page of a group, unless that page has at least one detail record.  I wrote about that behavior in another article long ago.  So the only trick is to create a primary group that includes every record in the report. Then you set this group header to repeat on each page and it behaves just like a page header, with the exception of not printing on the last page.

So how do you create a group that includes all the records in the report? You group on a value that doesn’t change.  If you have a DB field like “company” that doesn’t change you can use it.  But you can always create a formula that isn’t tied to any data fields.  My favorite dummy group formula is:

WhileReadingRecords;
"All"

The word “All” can be any value. Just keep in mind it will appear as the overall node of the group tree so you might want it to make some sense. The WhileReadingRecords function allows the report to see this static value as a recurring value, which makes it eligible for grouping.

Once you create the formula you use it as Group 1 in the report and then go into Group Options and check “Repeat Group Header on each new page”.  If you put your column headings in this Group Header they will appear on every page, but won’t appear on the last page (unless there are details printing on that page).

And thanks to Tina Nordyke, the DBA for Advocates for Basic Legal Equality, Inc for suggesting this method.




Table filters in the database expert

Tuesday 8 October 2019 @ 9:06 pm

This week I worked with two different customers, helping them locate data buried in a large system with hundreds of tables. In both cases I had to use the table filter method to help me find the correct tables. For instance, when we needed to find the vendors table we looked first in the V section of the table list. However, in the system we were using all of the tables names had a 2 character prefixes for a dozen or more different modules. It wasn’t clear which module might contain this table. So I added a filter to the list of table names shown in the Database Expert. This way only table names that contain VEND somewhere in the name would be displayed. It made it easy to spot the correct table.
To add a filter to the table list you need to go into File > Options > (Database Tab).

In the middle section you will see two boxes on the right, and the top one is for table filters. It is labeled [Table Name LIKE:]

In this box you enter the characters you are looking for with a percent sign before the characters and/or after the characters. This percent sign is a wild card symbol. So since we only wanted to see tables that contained VEND anywhere in the name we put in a filter like this:

%VEND%

If I wanted all tables that have TMP on the end I could enter a filter with only one wildcard, like this:

%TMP

The only downside I have seen when using these filters, is that it is easy to forget about the filters. It then takes a bit to figure out why the table list is missing or incomplete. So don’t forget to take the filter out when you are done.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server