** RPT Management Tools
** Advertising on KenHamady.com
** Reporting on XML data
** Let me create your Crystal Reports
** Numeric parameters without commas
** My library of Crystal Reports materials
** Percentages in Crystal Reports vs Excel
** Preventing Drill-Down
** Other recent blog posts
    Dealing with data types that change at runtime
    Modification date/time bug
    CR rebranded as SAP Crystal Reports, v13 in 2011
    Unlocking the chart "marker size" property


RPT Management Tools

Have you ever wanted to:
1) Find all the reports that use a table, field or formula?
2) Compare two versions of a report to see what was changed?
3) Make a change to several reports at once?

The more reports you have, the more likely that you will face these challenges, and you can solve problems like these with several tools on the LINKS page of my site under RPT Management Utilities. I just posted a blog article that provides a brief rundown on the core features of each product as well as what sets each product apart. I have also posted a detailed feature matrix that compares the products and provides pricing information.

The tools being compared are:
My Data Dictionary
Report Miner
Report Analyzer
Object Compare
Crystal SpellChecker
Search and Replace
Mass Verify
.rpt Inspector 3 Professional Suite


If you have tried one of these products, or are currently using one,  I would love to get your feedback – on or off the record.

Advertising on KenHamady.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. 


Reporting on XML data

I am writing this because I have found little meaningful documentation on how to report on XML data in stand alone CR.  I would be thrilled if someone wants to show me where it is hiding.  But here is what I have found from trial and error.

In Crystal Reports there are 3 different methods to connect to XML data.  The first two ("ADO.NET (XML)" and "Database Files") allow you to connect to a single file, either XML (data) or XSD (structure). When you connect to an XML file then you can read the data, but all the data types will be strings.  When you connect to an XSD you will see the data types specified in the XSD but your report will have no actual records.  The XSD is used this way if you plan to pass the data to the report from an application, but it means you have to design the report without being able to see the preview.

The third method, "XML and Web Services", allows you to read from local files or from a web based files.  It requires both the XML and an XSD file to work.  You can either point CR to both the XML and XSD, or you can reference the XSD within the XML file.

If the XSD can't be read at runtime the report may still run. One of my customers had an application that ran the report but would lose all formatting at runtime. That is a symptom of the report reading only the XML file.  The data is all read as strings so all the numeric and date formatting is dropped. This customer was not allowed to change the application so our solution was to write a formula for each field that was not a string and convert the data to the appropriate data type in the formula.  Then we used these formulas in place of the database fields.

One last point. If you have an XML file and don't know how to generate an XSD then you might find an XSD generator helpful.  I found several and this one is a free and open source XSD generator.


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.


Numeric parameters without commas

It is easy to change the format of numeric fields that appear on the report.  Even a numeric parameter that is placed on the report can be formatted just about any way you can imagine.  But for some reason the format of a numeric parameter's "List of values" (LOV) is much more difficult to control.  It annoys me when a numeric parameter input for an invoice number is formatted with commas and (in some cases) decimal points.

In most versions up through v10 there was one less-than-ideal way to control of the format of numeric parameter lists:

1)You went into File > Options and selected the "Field" tab.
2) You clicked the "Number" button and the "Number" tab.
3) You selected the format you wanted (or went into "customize" to create the desired format).

The problem with this method is that it affects ALL numeric parameter inputs in all reports on this PC.  It also affects how any new numeric fields will be formatted when the are first added to the report from the Field Explorer.   And since this is a local setting, it only works on the local PC.  Move the report to another PC and the format you have set will revert to settings on that PC.

And starting with v11 even this bit of control seems to have gone.  Making the change described above still changes the format for new numeric fields placed onto the report, but it seems to have no effect on the input format for a numeric parameter.  In versions 11 and 12 I could not find a way to control the formatting of a numeric parameter which now has commas and no decimals.

The only workaround I know is to use a string parameter and convert it to number in a formula.  This get's tricky if the parameter allows multiple values.  And if the parameter is part of the selection formula it could slow the report down.  But a string parameter input won't have commas or decimal points to contend with.

Of course with a string parameter there is nothing to prevent a user from entering the letter O instead of a zero.  So if you use this method I also recommend that you give the parameter an "edit mask" of something like 999000.  This tells CR to accept only numeric characters as inputs for the string parameter.  The zeros are required positions while any positions with nines are optional.


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.

Percentages in Crystal Reports vs Excel

Percentages in Crystal behave slightly differently than they do in Excel.  Say you calculate 13/21 and get .619.  In Excel you can click the percent symbol on the toolbar and get the value to show as  61.9%.  And if you multiply another value by this you will still by multiplying by .619.

In Crystal, clicking on the % on the toolbar does not move the decimal.  So if you have the same calculation of .619 and you click the percent symbol CR will simply add the symbol, showing as .619%.   If you want the displayed value to shift the decimal you have to change the calculation.  One way is to use:

13 % 21 //(using the % in place of / in the calculation).

This will result in a number that is 100 times larger, or 61.9.  Then you can add the symbol using the toolbar. Some folks keep the slash and multiply the result by 100, which does the exact same thing.

Note that the CR decimals are "what you see is what you get".  So if you multiply by a value that shows as 61.9% on the report you are actually multiplying by 61.9 not .619.  In Excel you are multiplying by the underlying .619 value.  In other words, the percent symbol in preview means nothing to Crystal calculations.

One last tangentially related note.  Don't accidentally use a backward slash as a divide operator.  This is a valid operator that does something called an "integer divide" which I have discussed before.

Preventing Drill-Down

Drill-Down is a wonderful feature, but it can confuse those who don't expect it.  Even when you suppress sections a user can drill-down to an empty screen and not realize what they have done.  Or they will accidentally drill down when the details are not hidden.  They end up on a new tab and don't know how to get back.

The simplest solution I have found is to cover all the fields in the group header or group footer with a single large text object. Make sure this object is in front of the the other objects in the section.  Crystal won't drill-down on a text object so putting this in front of all of the fields prevents the user from launching a drill-down  - intentionally or accidentally.

And if your users will be running their reports from within CR it is also a good idea to make the text object "Read Only".  This way they won't double click on it by mistake and get into 'edit' mode.


Other recent blog posts

    Dealing with data types that change at runtime
    Modification date/time bug
    CR rebranded as SAP Crystal Reports, v13 in 2011
    Unlocking the chart "marker size" property