I have written before (several times in fact) about the challenges of exporting to Excel when you have subreports. It is difficult to avoid merge cells unless you get the alignment just right. Recently one of my readers told me that he exports to TTX format in this situation and finds it works better. I did a quick test using a report with a subreport on GF1. The subreport was just a bit below the other fields. When exported to:

1) Excel there were extra merge rows between the data rows
2) Excel (data only) the subreport was on a separate row
3) Tab Separated (TTX) the file would open cleanly in Excel

I am sure there are other variables but if you need to export to Excel format and there is a subreport involved, this is one more option to try.

A customer recently asked me how to use the vertical alignment formula functions in Crystal reports. He had found them on-line and was surprised that there were such functions available.  So was I.  But then I typed the following functions into a formula:

crTopAligned
crCenteredVertically
crBottomAligned

Sure enough, all three of them turned blue and were recognized by Crystal Reports as valid functions. But after the initial excitement I realized that having a function does no good unless there is a formatting property where the function can be used. For instance the function CrRed can be used in any color condition formula, and the function crCenteredHorizontally can be used in the condition formula for horizontal alignment. But there is no vertical alignment formatting property in Crystal Reports. So after some more on-line research we figured that there must have been a plan to add vertical alignment options to Crystal.  These function constants were added for them. But then the property never made it into the release and so the functions were orphaned.

And since SAP isn’t planning any major enhancements for standalone CR, I doubt that these functions will find a home any time soon.

Three months ago I mentioned plans for a new comparison matrix. The goal of this new matrix is to compare the features of Crystal Reports to the features of other reporting tools. I hope to find the right list of features to highlight the key differences in the products.

I am now ready to release the first draft of the matrix to get things rolling. I have also published a glossary defining the features included in this current draft. There are still blanks and questions, and the feature list will evolve, but I think we have a good start. The current matrix includes Crystal Reports and 5 other competing products:

• SQL Server Reporting Services (Report Builder)
• MS Access (reporting feature)
• QlikView
• List and Label
• R&R ReportWorks

There are also blank columns for Oracle BI Publisher and Tableau.  I plan to start those next (with some help).  If you are proficient in any of these tools (or another competing tool) here are the ways that you can help out:

You can fill in part or all of an empty column
You can review columns that are complete and see if any features are marked incorrectly.
You can suggest new feature rows that you think will highlight the differences in the products.
You can suggest an additional tool like the ones below.

LogiXML Ad Hoc
Cognos Impromptu
Indicee
Windward Reports
Target Reports
Zoho reports
Birt
Birst
Fast Reports

I will add tools based on interest level, and the availability of someone to review the features.

Formula #15 on my formulas page has always allowed you to convert a number into text and have it not display zero decimals on the right end.  So if you had the following three numbers:

1.0500
1.4050
1.2345

They would display like this:

1.05
1.405
1.2345

But the original formula could only handle 6 decimals. This week I need it to go out further and so I decided to improve the formula.  Now it can handle any number of digits and you don’t have to convert the field to text if you don’t want to.  There are now two formulas involved.  The first just determines how many non-zero places need to be displayed for the field in each row:

//Formula @digits
 Local NumberVar X := {Your.Field};
 Local stringVar Y := StrReverse (Totext (X - Truncate (X) , 9 , ''));
 if Val (Y) = 0
 then 0
 else Length(Totext ( Val (Y) , 0 , ''))

You can then use this to display your original field with the appropriate number of digits. There are two different methods to use this formula:

1) Format the original field by selecting: [ Format > Field > Number tab > Customize button ]. Then next to the decimals property click the [X+2] condition button and enter the formula field created above {@Digits} and nothing else. This will adjust the number of decimals displayed for each record, based on the number of digits needed.

2) Alternately you can use the first formula to create a separate formula field that converts {Your.Field} into text with the appropriate decimals:

Totext ( {Your.Field} , {@Digits} )

This creates a separate string field.  You can still use the original numeric in any calculations.