Solved

Formatting Number/Currency fields

Posted on 2008-09-30
17
2,301 Views
Last Modified: 2012-05-05
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 Number tab.  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.  The first copy is suppressed if exchange rates are used; the second copy is suppressed if exchange rates are not used. (If exchange rates are used, I want the Amount field to have 4 decimal places.)
Here's what I am trying to accomplish, and the associated questions:
1.  The first copy of the Amount field should always reflect the Regional Settings in Windows Control Panel.  I think I've done this by setting System Default Number Format.  However, when I go back into the Format Editor, System Default Number Format is no longer selected -- Custom Style is.  Why is it getting changed?  (Despite the change, the first copy of this field at runtime in the viewed report seems to correctly reflect the Regional Settings selection.)

2.  I want the second copy of the Amount field to reflect Regional Settings too, but with 4 decimal places, a rounding of .0001, and negatives shown according to Regional Settings.  From a post I found in this forum, and from my testing, I gather that I forfeit the field's ability to reflect Regional Settings because I selected Custom Style and made specific decimal, rounding and negative selections.  Is this correct?

3.  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.  Can you confirm?

4.  Can you think of any way that I can make the second copy of the Amount field reflect all Regional Settings except show four decimal places?
0
Comment
Question by:kathoshea
  • 8
  • 3
  • 2
  • +1
17 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 22625006
I glanced over your issue...and quite honestly...IF you have access to the actual DATABASE I would recommend building this logic in SQL. That is to say,...within the SQL Script that you are using for this report.....

Just an observation....you may get this kind of quirkiness when trying to do the kind of field formatting within Crystal Itself. I'm not saying it can't be done,...I'm just saying that for a MORE SOLID way of doing it, I would handle the field formatting in SQL not in Crystal.

Hope it helps.
M
0
 

Author Comment

by:kathoshea
ID: 22636575
Thanks, I've been coming to the same conclusion.
I *do* have access to the database, and wrote the SQL query myself for gathering the report data.  
However, I'm still a novice as a Transact-SQL coder.  I don't know what I would put in the stored procedure to format a field in the output table according to Regional Settings.
If you can give me any hints about how I get the output to use Regional Settings, I'd be grateful.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22636905
Well,...you can use CAST or CONVERT function in SQL.

Quit honestly,..the best way to have any questions answered regarding SQL is to go over to the SQL Server Experts...and ask them...they are VERY fast ......!

I would think you'd need to use a CASE statement then use the CAST or CONVERT funtions for the actual Fields.

0
 

Author Comment

by:kathoshea
ID: 22637022
CAST or CONVERT would work because the money data type follows Regional Settings?

Where do I find these SQL Server experts?   Do you mean here at Experts-Exchange?
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22637060
What do you mean by "Regional Settings"...???

What exactly do you need to accomplish regarding the "Regional Settings"...do you literally mean that based on the specific PHYSICAL LOCATION that the report is being RUN/PROCESSED you would like the display to be different based on that? Based on the settings foudn within the COMPUTER SYSTEM that is running the report..?????

YES,....Experts Exchange "SQL SERVER" experts.....
0
 

Author Comment

by:kathoshea
ID: 22637118
Yes, I want the Amount field to always reflect the user's Regional Settings in Windows Control Panel when the report is viewed by the end-user at runtime.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22638211
>Yes, I want the Amount field to always reflect the user's Regional Settings in Windows Control Panel when the report is viewed by the end-user at runtime.

the problem is that the SQL (Crystal) Server does not know about the users regional settings...
so, I fear, that will go into "you cannot do that" (from the server) ...

listening
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:kathoshea
ID: 22651681
angellll:  Are you saying that I'm better off sticking with getting the formatting to work in the report field itself?  Because in the end-user implementation, the SQL database is most likely not located on the end-user's PC?  And therefore cannot possibly know abou the end-user-PC's regional settings?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22652367
yes, exactly.
0
 

Author Comment

by:kathoshea
ID: 22652593
So what I really need then, is more Crystal Reports Software experts in this exchange to take a look at my initial questions, right?  If so, how do I go about this?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22652863
I would recommend you close this question and ask it again in the Crystal zone.

mlmcc
0
 

Author Comment

by:kathoshea
ID: 22652897
One more thought, though:  even if the SQL database is not located on the end-user's PC, it is on the same network, and therefore could "look" at the Regional Settings of the PC or Server it's installed on, couldn't it?  If yes, then the customer can set Regional Settings on the PC where the database is located and get the reports to look how they want?  If yes, then I do need some help from SQL experts about what SQL code to use inside the stored procedure to format the currency fields so that they reflect Regional Settings (if this is possible).
0
 

Author Comment

by:kathoshea
ID: 22652938
mlmcc:  My questions were originally posted in the Crystal Reports Software zone so I assume they are still there, aren't they?  (Not sure how all this zone-stuff works.)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22654534
Yes, they are.  However since the qurstion is a bit old most experts will not know about it.  I have been monitoring since you were trying to get a SQL solution and waited for that path to bring an answer or frustration.

I could ask a new question that linked to this one.

mlmcc
0
 

Accepted Solution

by:
kathoshea earned 0 total points
ID: 22760036
My co-worker came up with the solution below to my question #4.
As for my question #1, who knows ... some weird Crystal/VB/Vista interaction.
The answers to questions #2 and #3 are yes and yes.

Here's how to make the Amount field reflect all Regional Settings except show four decimal places:

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.

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now