Negative Number/Currency Formatting

[Former Member]

I'm using Crystal Reports in Microsoft Visual Studio 2005. The report I am working on is part of a money-counting application. I have two copies of the Amount field, overlaid on each other.

The first copy of the Amount field is formatted with "System Default Number Format" selected on the Crystal Format Editor - Number tab. This field is suppressed if exchange rates are used.

The second copy of the Amount field is formatted with "Custom Style" selected on the Number tab, and then the Custom Styles|Number tab has specific settings for Decimals (1.0000), Rounding (0.0001), and Negatives (choice of minus sign or parentheses). This second copy is suppressed if exchange rates are not used.

My testing shows that even though the SQL server dataset for the report has the Amount field defined as money, Visual Studio 2005 assigns the datatype System.Decimal. Therefore, when the first copy of the Amount field is formatted to use "System Default Number Format" the field formatting reflects the Windows Control Panel Regional Settings for Numbers when the report is viewed at runtime. This part is fine.

Here's where I need help: How can I make the second copy of the Amount field retain the Decimals 1.0000 and Rounding .0001 settings but reflect the Regional Settings for either Numbers: Negative Number Format, or Currency: Negative Currency Format?

P.S. There is a checkbox titled "Use Accounting Format" on the Custom Styles|Number tab, and the MSDN help for this says "When you select this option, the negative symbol used and its position is determined by the Windows Regional Settings (it will be either the minus sign or the brackets)." I was hoping this means that the report field when viewed at runtime will reflect Regional Settings for negatives, but what I think it actually means is that the Negatives setting shown in the Crystal Format Editor|Custom Style|Number tab reflect your Regional Settings at the time.

[Former Member]

My co-worker came up with the following solution:

Create a new formula field, place the first copy of the Amount field inside the CCur formula, and check the "System Default Currency Format" box. As before, this field is suppressed if exchange rates are used.

Create a new formula field with ".Dec4" appended to the name, place the second copy of the Amount field inside the CCur formula, and check the "System Default Currency Format" box. As before this field is suppressed if exchange rates are not used.

Then add the following code to the appropriate part of your project where reports are managed:

Private Sub ApplyNumericFormatting()

Dim i As Int32

Dim field As FieldObject

'If you use Crystal custom number format settings at design time in order to

'override some settings, none of the

'regional settings get applied at runtime. In order to show 4 decimal places and still use

'system defaults, define a formula field, use CCur or CDbl to convert to a number,

'append .Dec4 to its name and set number format to System Default

With m_ReportDocument.ReportDefinition.ReportObjects

For i = 0 To .Count - 1

If TypeOf (.Item(i)) Is FieldObject Then

field = DirectCast(.Item(i), FieldObject)

If field.DataSource.Name.Contains(".Dec4") Then

' in order to change decimal places & rounding format, turn use system defaults off

' it seems counter-intuitive but if done here instead of when designing report, the

' system defaults do get applied except for the ones changed here

field.FieldFormat.CommonFormat.EnableUseSystemDefaults = False

field.FieldFormat.NumericFormat.DecimalPlaces = 4

field.FieldFormat.NumericFormat.RoundingFormat = CrystalDecisions.Shared.RoundingFormat.RoundToTenThousandth

End If

End If

Next

End With

End Sub

This accomplishes everything I wanted -- when exchange rates are used, the Amount field shows 4 decimal digits, but the rest of the field follows the Regional Settings for Currency.

PS for Moderators: You might want to move this back to the forum I originally posted this in: .NET Development - Crystal Reports, as you can see that the solution involves VB code, not just Crystal report design.

[former_member11696]

Hi Katherine,

This is a report Design question so I am moving your post to the Report Design team.

Thank you

Don