One of my customers has reports scheduled to run every weekday. The rule is that all month long it should pull the dates for the current month. But once a month, at the beginning of the month, they want to run the entire previous month. So I wrote their selection formula to say “If today is the first of the month, then run LastFullMonth, otherwise run MonthToDate:

If Day (currentDate) = 1
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

This worked fine for several months until we got to April.  Because the report only Continue Reading »
Calculating the first weekday of the month

Exporting Crystal Reports to Excel can be tricky. I have written several articles on techniques to make it easier. But it gets even trickier when a subreport is involved.

Without a subreport I have found that you can get a clean export by using the “Data Only” option. But this doesn’t work with a subreport. I tried two different methods with the “Data Only” export that would not work. Continue Reading »
Exporting to Excel when you have a subreport.

Here is a clever tip I just received from a blog reader who is also a Crystal Reports developer. He wanted to create a title with something that looks like the “text-shadow” property.  He found that using an image didn’t print as clearly as a true font so he came up with a clever way to create the shadow.  Here are the steps:

1) Put the title text into a large bold font.
2) Copy the title text object and paste a duplicate directly on top of the original.
3) Right-click on the copy and select “Move – To Back”.
4) Changed the font color of the copy to silver.
5) Nudge the copy slightly down and to the right.

There you have a clean shadowed font.  And thanks to Adam Butt of APB Reports in Trondheim, Norway.

I have written before about using Bar Codes in Crystal Reports, but recently two different customers have asked me about including QR codes on their reports.  For those of you who have not seen them, QR codes are the square scan codes that have small boxes in each  corner.  Here is a QR Code for my Email address:

The advantage of QR codes over traditional bar codes is that they have extra error checking built in, which means that even a slightly blurry scan, such as one done by a smartphone or tablet camera, can still be decoded and used.

There are several ways that you can incorporate QR codes into Crystal Reports.
1) If you have a single QR Code, or a small number of them, then you can use a free web based QR Code Generator to generate the image.  You can then insert the image into the report.  If you have several you could Continue Reading »
Printing QR Codes within your Crystal Reports

In most cases I find it more efficient to let Crystal generate the SQL for reports.  This makes the reports simpler for most CR users to create.  It is also easier to add additional fields, and the Visual Linking window makes the query structure easier to understand. Finally, in most cases, the performance is essentially the same since CR can generate workable SQL.

But there are plenty of cases where the only practical solution is to base the report on a SQL Command, instead of on linked tables. When I need to do a UNION of two or more queries, or when I need to apply a filter to the ‘outer’ table of an outer join, I have to resort to SQL Commands.  I am not very fast at writing SQL so I usually create a regular report first, using the correct tables, joins and fields. Then I copy the SQL that CR generates and use that as the starting point for my command.  Recently I find myself using SQL more, so I have started to pick up tricks that make SQL queries work faster.

Last week I was working on a query that combined several fairly large tables.   The query was taking several minutes to return data and we were trying to see Continue Reading »
Making SQL commands more efficient

I recently wrote about a post about controlling the color of multiple objects from one formula.   One of my newsletter readers took the idea and ran with it and designed a custom function for his repository:

========================================
Just got the ‘Underground News’. The tip on creating formulas for set colors is fantastic – never occurred to me either. Opens up all kinds of possibilities – kind of CSS for Crystal Reports. So now I can Continue Reading »
More on controlling colors from formulas

Sometimes there are text blocks on your reports that contain more then one line of text (notes, memo fields, comments, etc.).  When you format any multi-line text field, you have a tab of options called  ‘paragraph’ formatting. The first 3 settings on this tab allow you to determine how far the text block should be indented. Your indent options are “Left”, “Right” and “First Line”. This can be misleading because “Left” and “Right” are calculated from the left and right edge of the object, while “First Line” is calculated from the indent set in the “Left” position.

Take this example: You want the first line of text to be indented 1.0 from left while all other lines in the block are indented .5 from left. You might try putting 1.0 in ”First Line” and .5 in Left indent.  What you would get is an indent of 1.5 on the first line.  This is because it adds the 1.0 in ”First Line” to the .5 in “Left”. If instead you put .5 in both places, then the first line will indent .5 + .5 or 1.0 inches.

And what if you want a ‘hanging indent’ where the first line is not indented but the rest of the paragraph is indented .5? For this you would put in .5 for the “Left” indent and -.5 (a negative number) for the “First Line”. The negative number added to the positive nets to zero so that the first line has no indentation, while the rest does.

Recently a customer needed to group records using part of a code field. The code field had alpha characters followed by a series of numbers. They wanted to to strip off any numbers on the end and group using only the initial string portion of the value. In the past I have done things like this using a FOR loop to check each character. This time another idea popped into my head. If you reversed the string so it started with the numbers you could put it inside the VAL() function. This would convert the numeric portion to a number and ignore the string characters that followed. You could then check the length of that number and know how many characters to take off the end of the value. To get the length of the number you would have to turn it back into a string and then use the Length function. So the calculation would nest four functions like this:

Length( Totext( Val( strReverse( {table.field} ) ) ,0, "" ) )

The only glitch was if the string ended with a zero, like “XXX1230″. In those cases Continue Reading »
Stripping numbers off the end of a string

Crystal limits how many values you have to choose from when you use a dynamic parameter.  The default limit is 1,000.  To show more than 1000 values you have to add a registry key called ‘LOV’ in the CR path for Database options.  This needs to be done on each PC that will be running the report.   The following examples are for version 11.5 (XIr2) and version 12 (2008). Your registry path may be slightly different depending on your Operating System and your CR version.

1) Add a new registry entry under the appropriate path on your PC to the DatabaseOptions key:

HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions\

HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\

2) Add a new key at that level called: LOV

3) Add a string to that key called MaxRowsetRecords

4) Set the value to whatever limit you want.

I have read that you can enter a zero or -1 to mean unlimited, but that hasn’t worked in my tests in CR 2008.

Also note that loading large lists this way can make the parameter run for a few minutes before the user can select a value. Also note that any dynamic list that has more than 200 values will activate the parameter’s ‘batch interface” discussed in a previous post.

(And thanks to Duane Fenner, an Account Manager and CR developer at LeaseTeam, Inc. for suggesting this post.)

For the past few days I have been helping a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal’s normal Gantt chart doesn’t allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color but was trying to think of the most efficient way to change the color in all the condition formulas on the fly. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned Continue Reading »
Controlling the color of many objects from one formula