Crystal Reports – How to order cross tab by total column (sort largest to smallest)

The following steps show how to order a cross tab in Crystal Reports from largest to smallest – by default the order would be by first column alphabetically.

For example:

This ensures that the largest value in the selected column appears at the top.

 

  1. After creating your cross tab, right-click on it and choose ‘Group Sort Expert’
  2. Under ‘For this group sort’ choose ‘All’
  3. Under ‘based on’ choose the column you want to sort by
  4. Then choose ‘Descending’ for the order
  5. Click OK to save the changes.
  6. The cross tab will now be sorted from largest to smallest.

Reference: http://stackoverflow.com/questions/1428184/cross-tab-report-how-to-control-row-order

Crystal Reports – How to display more than one line of text

The following steps show how to make a field in a Crystal Reports display multiple lines, for example, if you had a HTML field that contained paragraphs.

  1. Insert the field into the report (if it hasn’t already) and right-click on it and click on ‘Format Field’
  2. On the ‘Common’ tab, tick ‘Can grow’ – you can also set a limit to the field using ‘Maximum number of lines’
  3. Click ‘OK’ to save the changes
  4. The field will now automatically expand to show multiple lines, to the limit specified.

Crystal Report – How to render HTML tags in strings

By default when a Crystal Reports parses HTML tags literally – that is, it will output the tags without rendering the HTML.

If you want remove the tags from displaying by having them rendered, that is, <p> becomes a paragraph, <strong> becomes bold, then you need to change the text type to HTML.

The steps below show how to do this.

  1. Insert the field into the report (if it hasn’t already) and right-click on it and click on ‘Format Field’
  2. In the ‘Paragraph’ tab, under ‘Text interpretation’ choose ‘HTML Text’
  3. Click ‘OK’ to save the changes
  4. The HTML will now be rendered, in the process removing the HTML tags from being displayed.

 

https://support.navigatorsoftware.co.nz/article/remove-html-tags-using-crystal-reports

Crystal Reports – Display running total as 0 when null value

The following steps show how to display a NULL value (a blank value) as a 0.

This can be helpful when you need to use the value to perform calculations or need a consistent layout in the report.

In the example used the third row has a NULL value.

crystalreports-displaynullvalue01

Option 1: Use a formula field

This is the typical approach I take – I create a formula field and use a formula to say ‘if value is NULL then 0 else use value’.

  1. Right-click on ‘Formula Fields’ and choose ‘New’
  2. crystalreports-booleantick2
  3. Give the formula field a name, e.g. percent_fixed
  4. Use the following formula, replacing {Sheet1_.percent} with your own field
  5. if ISNULL( {Sheet1_.percent} ) then 0 else {Sheet1_.percent}
  6. Save and close the formula editor
  7. Add the new formula field to your report
  8. The NULL value will be presented as a 0
  9. crystalreports-displaynullvalue02

Option 2: Use report options to convert all NULL values

Whilst this option usually works fine, I have had issues using it with some more complicated reports. For this reason I usually avoid option.

To enable this:

  1. With your report option, click on the ‘File’ menu and then ‘Report Options’
  2. crystalreports-displaynullvalue03
  3. Tick ‘Convert Database NULL Values to Default’ and click ‘OK to save the changes.
  4. crystalreports-displaynullvalue04

Crystal Reports – How to display selected parameter values

The following steps show how to display selected parameter values in a Crystal Report.

For example, when running a report a parameter value is used to filter the report results – in this example, names of users.

This can make a report easier for people to understand when a filter has been applied – so there is an understanding of the limits applied to the displayed report.

  1. Right-click on ‘Formula Fields’ and choose ‘New’
  2. crystalreports-booleantick2
  3. Give the formula field a name, e.g. prompt_selected_name
  4. Now enter the following formula – replace {?prompt_name} with you parameter field
  5. join({?prompt_name},',')
  6. Save and close the editor
  7. Insert the formula field into the report and run the report
  8. crystalreports-displayselectedparameters1

 

Bonus formatting tip

To display each selected parameter on it’s own line, set the formula to

join({?prompt_name},',' + Chr(10))

and enable ‘Can Grow’ in the field formatting. (Right-click on the field, Format Field, Common tab, tick ‘Can Grow’)

crystalreports-displayselectedparameters2

Crystal Reports – How to format number as percentage

The following steps describe how to format a number so that it looks like a percentage.

The obvious thing to point out is that there is no true ‘percentage’ value in Crystal Reports – but you can take a number and format it so that it ends with a percent symbol. That is, 55.23 displayed as 55.23 %.

First – if your value is currently a decimal, e.g. 0.5523, you will need to times is by 100 to shift the decimal place to 55.23.

To do this,

  1. Right-click on formula field and give it a name, e.g. percent_value
  2. crystalreports-booleantick2
  3. add the field and times by 100. For example
{table.value} * 100

Now that the value has the correct decimal place you can add the field (or newly created formula field) to the report and apply the formatting.

  1. Right-click on the field and then click on ‘Format Field’
  2. On the ‘Number’ tab click on the ‘Customize’ button
  3. crystalreports-formatnumberaspercent1
  4. Open the ‘Currency Symbol’ tab
  5. Tick ‘Enable Currency Symbol’
  6. Change ‘Currency Symbol’ to %
  7. Change ‘Position’ to -123%
  8. crystalreports-formatnumberaspercent2
  9. Click ‘OK’ and ‘OK’ again to save the changes.
  10. You now have a number formatted as a percent.
  11. crystalreports-formatnumberaspercent3

Crystal Reports – How to fix ‘Division by zero’ error

Problem

When running a Crystal Report you receive the following error message

Division by zero.

crystalreports-divisionbyzeroerror1

Solution

Before describing how to work around this issue a quick explanation.

When you see this message it means a formula in the report is attempting to calculate a value using a field that has either a NULL or 0 value. You cannot divide by NULL or 0 – it is mathematical non-sense.

For example

1 / 0 =  mathematical non-sense

To fix the issue you need to find the formula that’s causing the issue and add a check at the start to say ‘if value is 0 then return 0 else do calculation’.

For example

IF {#field_1} = 0 OR {#field_2} = 0 THEN 0 ELSE
{#field_1}/{#field_2}

crystalreports-divisionbyzeroerror2

Crystal Reports – How to convert string date to real date value

Sometimes you may come across dates that are strings in your data source, for example a date may be ’01/01/2012′ in the data source instead of the standard database date format ‘2012-01-01 00:00:00.000’.

Generally dates should be actual date values, but there are various legitimate scenarios – in these cases you will need to convert the string to an actual date before using in any formulas.

To do this simply wrap the field in DateValue() – for example

DateValue({table.date_value})

crystalreports-convertdatestring

 

Crystal Reports – How to display boolean true/false as tick

The steps below show how to display boolean true/false values as a tick and/or cross.

By default these values are displayed as either ‘True’ or ‘False’.

crystalreports-booleantick1

  1. In the Field Explorer right-click on ‘Formula Fields’ and choose ‘New’
  2. crystalreports-booleantick2
  3. Enter a name for the formula, e.g. tick_user_likes_fruit
  4. Add in the following formula (replace {Sheet1_.user_likes_fruit} for your field)
  5. if NOT ISNULL({Sheet1_.user_likes_fruit}) AND {Sheet1_.user_likes_fruit} then
    
        Chr(252)
    
    else
    
        Chr(251)
  6. Save and close the window
  7. Add the formula field to the report.
  8. Change the font for the field to Wingdings.
  9. The field will now display ticks and crosses that correspond to the boolean value.
  10. crystalreports-booleantick3

 

Similar characters

The following values may also be used

Symbol Name Value
Ballot X 251
Check mark 252
Ballot box with X 253
Ballot box with check mark 254