5 Crystal Reports Hints and Tips

Version 4

    Environment: Crystal XI (11) but many features may be available in older versions too

     

    I wanted to share some little simple tips for when using Crystal but I only make use of certain parts of the product, so I'd love to hear any other good ones you might want to share too.

     

    1.  Formatting a field based on one or more criteria being met

     

    You can have a field change appearance on the report based on some database or runtime criteria being met.  This helps to highlight certain data on the report.  An example use of this could be to turn the colour of a field red if a response level of Priority 1 is applied.  To do this:

    • Right-click on the field and select Format Field.
    • Click the Font tab and the Formula button next to the Color property.
    • Type your formula which must have an outcome that matches the property you are in i.e. a colour.
    • I've shown an example formula below and the resulting output.

     

     

    Crystal1.png

     

     

    crystal3.png

     

    2.   Hiding a section of the report based on one or more criteria being met

     

    In a similar method to step 1, you can hide a whole section of your report based on some database or runtime criteria being met.  I find this feature useful if you want to give the user who is running the report the option to either view all the details of the data or to view just the totals on the report.  You can do this by hiding the details section if a parameter is picked to indicate that they want to.  To do this:

    • Create a new parameter field.  I've chosen to create a boolean but you could also create a string.
    • Give this a positive and a negative value to show or hide your details.  NOTE: You may want to set a default value as True or False if you think the users will most likely want the details shown or suppressed.
    • Select Report - Section Expert and select your Details section.  NOTE: You may also need to run through these steps for the section that contains your headings or you will get your headings shown on the report with no data.
    • Click on the Formula button next to the Suppress property.
    • Type your formula, selecting your parameter field, and the formula must have a True and a False outcome
    • I've shown an example formula below and the resulting output.

     

     

    crystal4.png

     

    crystal5.png

     

     

     

    crystal6.png

    3.  Setting some useful Database options

     

    After installing Crystal reports, I find it useful to switch off the Automatic Smart Linking and switch on the Sort Tables and Fields Automatically so that you can locate then link your tables and find attributes easier.

     

     

     

    BIG SQL-2011-07-26-11-48-57.png

     

    4.  A few tips for when writing formulas

     

    Here's a few tips that I find handy when writing formulas:

    • When browsing your database fields if you right-click the attribute you think you want you can select browse data to see the type of data held in that field.

     

    crystal7.png

    • When browsing the data in this field (as above), you can click the paste data button to actually insert the value that you see in the database column into your formula - this avoids possible typos.
    • You can write formulas which compare the values of groups on your report but you can't further summarise a group.  For example, you could write a formula for: if {groupnamex} = {pm_process.pm_description} then 'yes' but you couldn't do Maximum({groupnamex}).
    • The IsNULL function is useful for populating the field on the report with a useful value even if the database value is NULL.  This is also really helpful if you are going to be grouping on this field.  An example formula could be: If IsNull({lc_response_level.lc_description}) then 'Blank response level description' else {lc_response_level.lc_description}.

     

    5.  Show SQL Query is useful for troubleshooting

     

    If the records being returned on your report are not what you expect you could try selecting Database - Show SQL Query.  This will show the select statement that it is running against your database.  You can cut and paste this query to run it directly against your database (for example in SQL Enterprise Manager).  Here is another document which also outlines some other ways to troubleshoot when your report is not retrieving the right records.