Contents for September 2010:
** Crystal Reports Viewers
** Advertising on kenhamady.com
** Free download of Xcelsius
** Crystal Reports on Windows 7, 64-bit
** Let me create your Crystal Reports
** Fixed length text exports using Cut Light
** My library of Crystal Reports materials
** Setting the RGB color of multiple fields at the same time
** Mixing single values and ranges in the select expert   
** Other recent blog posts:
         Reportapalooza
         Embedding an XSD into an XML file


Crystal Reports viewers

You use Crystal Reports to create, change and run reports. But what if you have a user who doesn't need to create reports or even change them. This user just wants to refresh the reports and view/print/export the results. Does he need another copy of Crystal Reports? Do you need to configure an expensive web server?

The most cost effective method for letting users run reports on demand is to install a third-party client-based viewer. And I am amazed at how many Crystal Reports users are completely unaware that these are available, despite the fact that they are offered by a dozen different vendors.

I recently reviewed six viewers in my blog, including all of the ones on my 'favorites' list. The blog post provides a brief introduction to each product including what sets it apart.  I have also created a detailed feature matrix (PDF) that shows some of the specifics for comparison like prices and the install base.  The tools being compared are:

    Crystal Corral by Groff Automation
    cView by Chelsea Technologies
    ViewerFX by Origin Software
    Logicity Pro by SaberLogic
    Easy View by Easy Street SW
    DataLink Viewer by Millet SW

If you have already tried one of these products, or are currently using one, I would love to ask you a few questions, on or off the record.


Advertising on Ken Hamady.com

I have decided to sell some ad space on some of my more popular pages. There is a flat monthly fee for each group of pages, based on their visitor history. So if you have a product that you would like to promote to my visitors, give me a call. 


Free download of Xcelsius

If any of you are interested in playing with Xcelsius you can download it and try it out for free.  Note that it is no longer called Xcelsius, but is now known as "SAP Crystal Presentation Design".  Also note that a promotion like this usually indicates a new version of the software in the wings.

In general, I think interactive dashboards have their place.  But the demand for them isn't very strong within my customer base. People are much more likely to ask me for a good viewer or a good scheduler than for dials and gauges. Of course things do change.  I didn't think much of the first version of CR that I tried and now it is all I do.  I try to stay flexible.


Crystal Reports on Windows 7, 64-bit

I had to replace my primary laptop this summer and decided to try a Windows 7 machine with a 64-bit processor.  I had some concerns because I use lots of old software, including several old versions of Crystal Reports. I wondered how well everything would run in the new environment.  I remembered my upgrade to Vista a few years back, and that took lots of extra work to get all the pieces of my operation going again.

But this time I am happy to report that things went pretty smoothly.   I mainly run three versions of CR (v8.5, v10 and v12) which I find cover all the bases.  All three run fine on this machine.  And in general the transition from Vista to Windows 7 was much smoother than my upgrade to Vista.

The one glitch was the fact that 64 bit machines use 64-bit ODBC, while all of my programs use 32-bit ODBC.  The 32 ODBC connections are kept completely separate and the 32-bit ODBC administrator is not listed at all in the control panel.  So it took a bit of research to learn that my laptop had two different ODBC administrators, one for 64-bit ODBC and one for 32-bit ODBC.  To load the 32-bit ODBC administrator you have to find and run this program, usually in this folder:

        C:Windows\SysWOW64\odbcad32.exe

Other than that I had no trouble installing the versions mentioned above, running them, and doing customer projects in them.  If you have a different experience, let me know.


Let me create your Crystal Reports

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert?  Let me show you how I can mix and match techniques to create the reports you need.  Even the ones you were told "couldn't be done".  And since I am also a teacher I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly or seem overly complex.  Let me have a look at them and see if there is a more elegant solution.


Fixed length text exports using Cut Light

I have struggled many times while helping customers create fixed length text files from Crystal Reports. These files are standardized files used to transfer data from remote systems into centralized systems. Banks, insurance companies and government agencies often require the submission of data in fixed length files so they can be imported into legacy applications.

The challenge has always been preventing Crystal Reports from truncating the rows based on the page width of the printer driver. Crystal uses printer drivers to layout the page even when the export doesn't go to a physical printer.  I recently did days of research to write an article on how to get the widest possible text export from each version of CR and then published this in my Expert Techniques volume 4.  But with this method CR 2008 is limited to 367 or 377 characters depending on your version of the export dll.  Some older versions of CR can go up to 1500 characters if you get all the right pieces in place.

But I made a recent discovery while talking with Ido Millet about scheduling these exports.  Millet Software produces a DLL called Cut Light that adds several custom functions to the formula editor. One of them allows you to write a formula that can append lines to an external text file while the report is being previewed – bypassing the export feature altogether. The function is called FileAddText and this is an example of the formula I used to do a fixed length export.

   FileAddText ( "C:\archivetest.txt", {@test} , false, true)

There are four arguments.
1) The path and file name you are updating (it will be created if it doesn't exist)
2) The string you want to append within that file, in this case the formula that creates a single fixed-length row of data.
3) If you want the file to be overwritten each time
4) If you want to have a carriage return added with each row.

This process takes a bit longer than an export, but it has no limitations on length and is less sensitive to version changes.  It requires that the Cut Light dll be installed on the PC running the report.

I also wrote recently about a free dll that has a similar function called FileAppendLine. I haven't tested this but it I expect it works the same.  Don't expect support on a free product, but it is hard to argue with free.

If you need help setting this process up, give me a call.


My complete library of Crystal Reports materials:

Do you struggle with subreports?  Are you curious about cross-tabs? Why not let me explain these Crystal Topics to you with one of my Expert's Guides.  Each guide comes with clear explanations and sample files to illustrate the concepts.

    Expert's Guide to Formulas  ($36)
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    Expert's Guide to SQL Expressions, Options and Commands ($26)
    Expert's Guide to Totals ($24)
    Expert's Guide to Cross-Tabs ($22)
    Expert Techniques Vol. 1 - 4  ($19 each)
    Quick Reference to Crystal Reports in Visual Basic ($16)
    Quick Reference to Crystal Reports in .NET ($14)

You will find these on the LIBRARY page of my site.


Setting the RGB color of multiple fields at the same time

One of my customers had a dozen or so formula fields used as headings throughout a long report.  He wanted to be able to adjust the color of the font assigned to all of these objects in one place.  So we created a formula that stored three numeric values like this and put it into the report header (suppressed):

WhilePrintingRecords;
NumberVar RGB1 := 0;
NumberVar RGB2 := 64;
NumberVar RGB3 := 128;

Then we used the following as the condition formula for the font color of all of those objects:

WhilePrintingRecords;
NumberVar RGB1 ;
NumberVar RGB2 ;
NumberVar RGB3 ;
RGB (RGB1,RGB2,RGB3)

By changing the values in the first formula he could control the color of all the fields that were formatted with the second formula. But as we did this I noticed something I had never noticed before.  You can control the font color of any field or formula using the font color condition button.  But if you try to do the same thing with a text object, like a column heading you will notice that the properties on the "Font" tab do not have any conditional formatting buttons.  If you want to have text headings that change color in this way you will need to replace the static field headings with formula fields.  That way you can have the same static text, but ALSO have the ability to set font properties (like colors) based on conditions.

If you want to learn more about RGB colors you can read an earlier blog post about them


Mixing single values and ranges in the select expert   

There is a trick I teach my students to help them remember Boolean (true/false) syntax.  I have them enter criteria into the select expert and then click the "Show Formula"  button to see the same criteria written in Crystal formula syntax.  So if you forget how to write a formula that does "between", "is one of" or "not equal to" just put that into the select expert and copy the formula that it generates.

And just last week I learned something new.  The Select Expert will let you mix ranges and single values when using the "is one of" comparison.   So say I want to return 62 specific order numbers.  I want all orders numbered from 1001 to 1030, all orders numbered from 2001 to 2030 and two other individual orders numbered 2035 and 2037.  In the select expert you can put all of this in one rule by entering:

Order ID / is one of / 1001  to  1030 / (click the 'add' button) / 2001  to  2030 /

(click the 'add' button) / 20035 / (click the 'add' button) / 2037 /

(click the 'add' button)/ Click OK /Refresh.

I was surprised that "is one of" allows a user to enter a value like "1001  to  1030" in a single entry.  This works in v10 so it probably works in v9, but it does NOT work in the select expert of v8.5.

Then if you look at the formula it will look like this:

     {Orders.Order ID} in [ 1050 to 1080 , 2050 to 2080, 2700, 2800]

Which is a second thing I learned.  You can mix ranges and single values inside square brackets.  One caveat is that while it works in the Select Expert it only works the first time it is entered.  If you then go back to the Select Expert a second time you will notice that it no longer says "Is One Of" but now says "Formula" and shows the above formula.  It still works fine, but you can't add additional items to that rule using the Select Expert.  You would have to enter them into the formula by following the same syntax pattern.

But that syntax is what I consider the most valuable part.  This syntax can be used not only in the Selection Formula but also in any IF-THEN statement (after the IF).   So if you have to enter a long list of codes, and there are some that are consecutive, you can simplify the list by using the syntax shown above to enter ranges.  And this formula syntax CAN be used in the Selection Formula of v8.5 or in v8.5 formula fields, even though it can't be used in the Select Expert of v8.5.


Other recent blog posts

    Reportapalooza
    Embedding an XSD into an XML file