Archive for February, 2012



More on controlling colors from formulas

Thursday 23 February 2012 @ 6:44 am

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




CR versions over time

Wednesday 15 February 2012 @ 9:52 am

One of my customers recently asked me how quickly I thought people were going to the newer versions of CR.  I didn’t really have any basis to answer him, so I was pleased last week when one of my LinkedIn groups started a survey, asking people to select their primary version of CR.  After nearly 100 responses I was surprised to see the persistence of version 11 (XI) which is shows up as the primary version in use.  This is a full 7 years after XI was released.

Only then did I remember that I ask my newsletter subscribers to list their version when they sign up.  So I did my own analysis based on the 1200 users that answered this question in the past 5 years.  I used the same 5 categories used in the LinkedIn survey.  You would think that since I am only counting new subscribers each year, that the data would skew slightly toward newer versions.  But version XI is still a clear winner, even when looking at the new subscribers so far this year.

Also note that as version 14 starts to appear, its increase seems to come at the expense of version 12 more than version 11(XI).  I currently don’t have an explanation for the persistence of version XI (I prefer v12/CR 2008) but if I come up with a plausible explanation I will share it.

Chart of CR versions by year




Creating a hanging indent

Monday 13 February 2012 @ 5:00 am

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.




Vendor Responsiveness (good and bad).

Wednesday 8 February 2012 @ 5:00 am

Got this last week:

“Ken, thank you for this comparison. I wish I had had it before I selected my previous Crystal Viewer…. I purchased quite a few licenses of a product and intended to schedule reports on it. The scheduling aspect never worked and no matter how many messages and emails I sent to the support people, no one EVER called me back or seemed to care that I was having problems. They literally ignored me.”

This vendor tells me that they responded to the Emails they received, and at this point the customer has moved on. However, if this customer had contacted me at the time, then their would have been no question about a response from the vendor. I review Continue Reading »
Vendor Responsiveness (good and bad).




Stripping numbers off the end of a string

Sunday 5 February 2012 @ 8:00 am

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




Visual Cut now exports to XLSX format

Friday 3 February 2012 @ 11:01 pm

Millet Software has just released version 6.5 of Visual Cut, one of my favorite tools for automating Crystal Reports.  There are about 50 fixes and new features but the one that stood out was the ability to export to XLSX format.  This format supports more than a million rows in a single tab and consumes much less disk space.

Another handy new feature, pointed out by Jared Faulkner of JF Electric , is the “After_Success_SQL” command that allows you to run a SQL statement after a completed report, or for each group in a burst. This gives you the ability to update the database with tracking information for each e-mailed or exported item. Doing this before required having the CUT Light UFL (also by Millet Software) but now this is built into Visual Cut.

If you want to learn more about Visual Cut or one of the other similar tools, then you should read my annual review of Desktop Schedulers.  The review is done each March so it will be updated next month.




Errors in a PeachTree upgrade.

Wednesday 1 February 2012 @ 8:00 am

I have quite a few customers who use PeachTree Accounting and PeachTree comes with Crystal Reports. So I have developed a bit of a specialty creating custom reports for people who use PeachTree. In January I saw something new.  PeachTree eliminated a field in their database that has been there for ages. The field is called QtyOrdered and it shows the Quantity of each item on an order. But starting  with the new 2012 product this field doesn’t exist and you have to use another Quantity field in its place.  The challenge was that there are a handful of different “quantity” fields to pick from so it took some experimenting to find the right one (JrnlRow.Quantity).

This means that any custom reports that use the original field will generate an error.  To run the report you have to edit all formulas and features that use QtyOrdered, and replace it with the new field.  If you need help with this change, or with creating custom reports from PeachTree, please give me a call.





Recrystallize Pro