Report Currency Formatting

[clinicoffice_user]

Hi,
 
I have CO v 5 with Synergy Physio and Rehab Ltd in Surrey.
 
Could you tell me what the SQL code is to display a column of data in a report in Currency format – i.e. £x,xxx.xx?  This would be for a custom column I have created from two other numeric data fields such as salecost or purchasecost.  I have tried cast as numeric but that does not give the correct result.
 
Regards,
 
Godfrey
Director
The Running School Woking

[Support]

That's a good question!

As far as the physical field type in the database, NUMERIC is the correct one which we use for currency values in ClinicOffice. However, there are many other "decimal" values in the database which are also defined as NUMERIC fields - so the question is, how does ClinicOffice know whether a NUMERIC field should be a currency or not?

The answer lies in the physical field name. Here's the internal code in ClinicOffice which determines whether a field is considered to be a currency value :-

Code: Select all

bool is_currency =
(col.FieldName.Contains("cost") || col.FieldName.Contains("price") ||
col.FieldName.Contains("total") || col.FieldName.Contains("amount") ||
col.FieldName.Contains("amt") || col.FieldName.Contains("purchase") ||
col.FieldName.Contains("tax") || col.FieldName.Contains("net") ||
col.FieldName.Contains("gross") || col.FieldName.Contains("profit") ||
col.FieldName.Contains("balance") || col.FieldName.Contains("outstanding"));

As you can see from the above, if the field name contains one of those keywords, then it's considered to be a currency value and hence formatted accordingly.

We hope this helps to answer your question. Please let us know if we can help any further.