Tuesday, 19 January 2010

Changing the currency format in a ReportBuilder report

These days I've been working on a project with Reportbuilder from Digital Metaphors where we need to display different currency symbols in an invoice report. Here was my surprise when I found out that the DisplayFormat of the DBText is linked to the local currency symbol that your computer is using!. If we open the control panel -> Regional and language options we'll see the default currency symbol of the system:

Then, I started struggling with this, because I didn't know why it was happening!. If we check out the display format options:

We can see that we can use a wide range of display options, but if we have configured in our system that we work with euros and then we change the display format in Dollars, for my surprise when we display the value, it appears in euros again.

Look at the next example showing the problem:

Even if I tweak the dollar display format, it appears an euro symbol on the DBText, and I don't wont this behavior. Although I was trying to configure the application there was nothing to do with it. Then I started looking for the problem in the source code and over the internet, and I found the next piece of code:

This piece of code is inside the ppDisplayFormat unit and look at the first if. This if is looking for a Dollar symbol, and if it founds then $ symbol, it uses the FloatToStrF function and this function uses the CurrencyString variable.

The description of the CurrencyString is this:
CurrencyString - Defines the currency symbol used in floating-point to decimal conversions. The initial value is fetched from LOCALE_SCURRENCY.
To find out where this code is called, we can follow the FloatToStrF function, and this leads us to the SetString function that contains the FloatToText (which uses the CurrencyString variable).

To solve this situation in a elegant way, I found a very good solution from the rbWiki in Digital Methaphors.

The display format is defined in the GetDisplayFormat routine of the TppDisplayFormat class. If we create a descending class from TppdisplayFormat we'll have the opportunity to replace these displayed values. If we assign this new class to the global variable gcDisplayFormat, this new class will be used instead of the other.

Then, we can use the following unit that automatically will change the DisplayFormat without using the CurrencyString default, and it will let us to customize the display format as the most convenient for us.


unit myDisplayFormat;

interface

{$I ppIfDef.pas }

uses
Variants, MaskUtils,
Classes,
ppTypes, ppDisplayFormat;

type

TmyDisplayFormat = class(TppDisplayFormat)
public
class function Format(const aDisplayFormat: string; aDataType: TppDataType; aValue: Variant): string; override;
class procedure GetDisplayFormats(aDataType: TppDataType; aFormatList: TStrings); override;
end;

TmyDisplayFormatClass = class of TmyDisplayFormat;

implementation

uses
SysUtils, Mask,
ppUtils;

{------------------------------------------------------------------------------}
{ TmyDisplayFormat.GetDisplayFormats }

class procedure TmyDisplayFormat.GetDisplayFormats(aDataType: TppDataType; aFormatList: TStrings);
begin

if aFormatList = nil then
Exit;

aFormatList.Clear;
case aDataType of
dtString:
begin
aFormatList.Add('Phone' + #1 + '!\(999\)\ 000\-0000;0; ');
aFormatList.Add('Extension' + #1 + '!99999;0; ');
aFormatList.Add('Social Security' + #1 + '000\-00\-0000;0; ');
aFormatList.Add('Short Zip Code' + #1 + '00000;0; ');
aFormatList.Add('Long Zip Code' + #1 + '00000\-9999;0; ');
aFormatList.Add('Date' + #1 + '!99/99/00;0; ');
aFormatList.Add('Long Time' + #1 + '!90:00:00\ >LL;0; ');
aFormatList.Add('Short Time' + #1 + '!90:00;0; ');
end;
dtInteger, dtSingle, dtDouble, dtExtended, dtCurrency, dtLongint:
begin
aFormatList.Add('-1.234' + #1 + '#.0;-#.0');
aFormatList.Add('-1.234,40' + #1 + '#.0,00;-#.0,00');
aFormatList.Add('(1.234,40)' + #1 + '#.0,00;(#.0,00)');
aFormatList.Add('($1.234,40)' + #1 + '$#.0,00;($#.0,00)');
aFormatList.Add('-$1.234,40' + #1 + '$#.0,00;-$#.0,00');
aFormatList.Add('-$1.234' + #1 + '$#.0;-$#.0');
aFormatList.Add('($1.234)' + #1 + '$#.0;($#.0)');
aFormatList.Add('($1,234.40)' + #1 + '$#,0.00;($#,0.00)');
aFormatList.Add('-$1,234.40' + #1 + '$#,0.00;-$#,0.00');
aFormatList.Add('-$1,234' + #1 + '$#,0;-$#,0');
aFormatList.Add('($1,234)' + #1 + '$#,0;($#,0)');
aFormatList.Add('-1234,4 %' + #1 + '0 %');
aFormatList.Add('-1234,40 %' + #1 + '0,00 %');
end;
dtDate:
begin
aFormatList.Add('4.3.01' + #1 + 'd.m.yy');
aFormatList.Add('04.03.01' + #1 + 'dd.mm.yy');
aFormatList.Add('04.03.2001' + #1 + 'dd.mm.yyyy');
aFormatList.Add('4-Mar-01 ' + #1 + 'd-mmm-yy');
aFormatList.Add('04-Mar-01 ' + #1 + 'dd-mmm-yy');
aFormatList.Add('4 March, 2001' + #1 + 'd mmmm, yyyy');
aFormatList.Add('4.3' + #1 + 'd.m');
aFormatList.Add('Mar-01' + #1 + 'mmm-yy');
aFormatList.Add('March-01' + #1 + 'mmmm-yy');
end;
dtTime:
begin
aFormatList.Add('1:30 PM' + #1 + 'h:nn AM/PM');
aFormatList.Add('13:30' + #1 + 'h:nn');
aFormatList.Add('1:30:55 PM' + #1 + 'h:nn:ss AM/PM');
aFormatList.Add('13:30:55' + #1 + 'h:nn:ss');
end;
dtBoolean:
begin
aFormatList.Add('Yes' + #1 + 'Yes;No');
aFormatList.Add('Y' + #1 + 'Y;N');
aFormatList.Add('True' + #1 + 'True;False');
aFormatList.Add('T' + #1 + 'T;F');
aFormatList.Add('OK' + #1 + 'OK;');
aFormatList.Add('Done' + #1 + 'Done;');
aFormatList.Add('' + #1 + ';Not OK');
aFormatList.Add('' + #1 + ';Not Done');
end;
dtDateTime:
begin
aFormatList.Add('4.3.01 1:30:55 PM' + #1 + 'd.m.yy h:nn:ss AM/PM');
aFormatList.Add('4.3.01 13:30:55' + #1 + 'd.m.yy h:nn:ss');
aFormatList.Add('4.3.01' + #1 + 'd.m.yy');
aFormatList.Add('04.03.01' + #1 + 'dd.mm.yy');
aFormatList.Add('04.03.2001' + #1 + 'dd.mm.yyyy');
aFormatList.Add('4-Mar-01 ' + #1 + 'd-mmm-yy');
aFormatList.Add('04-Mar-01 ' + #1 + 'dd-mmm-yy');
aFormatList.Add('4 March, 2001' + #1 + 'd mmmm, yyyy');
aFormatList.Add('4.3' + #1 + 'd.m');
aFormatList.Add('Mar-01' + #1 + 'mmm-yy');
aFormatList.Add('March-01' + #1 + 'mmmm-yy');
end;

end;
end;

{------------------------------------------------------------------------------}
{ TmyDisplayFormat.Format }

class function TmyDisplayFormat.Format(const aDisplayFormat: string; aDataType: TppDataType; aValue: Variant): string;
var
lfFormat: TFloatFormat;
liDigits: Integer;
liPrecision: Integer;
lsString: string;
liPos: Integer;
lsTrueString: string;
lsFalseString: string;
begin
Result := '';
if VarIsNull(aValue) then
Exit;
case aDataType of
dtInteger, dtSingle, dtDouble, dtExtended, dtCurrency, dtLongint:
begin
if (aDisplayFormat <> '') then
lsString := FormatFloat(aDisplayFormat, aValue)
else
begin
if (aDataType = dtCurrency) then
begin
lfFormat := ffCurrency;
liDigits := CurrencyDecimals;
liPrecision := 15;
end
else
begin
lfFormat := ffGeneral;
liDigits := 0;
liPrecision := 15;
end;
lsString := FloatToStrF(aValue, lfFormat, liPrecision, liDigits);
end;
Result := lsString;
end;
dtDate:
if Length(aDisplayFormat) > 0 then
Result := FormatDateTime(aDisplayFormat, aValue)
else
Result := FormatDateTime(ShortDateFormat, aValue);
dtTime:
if Length(aDisplayFormat) > 0 then
Result := FormatDateTime(aDisplayFormat, aValue)
else
Result := FormatDateTime(LongTimeFormat, aValue);
dtDateTime:
if Length(aDisplayFormat) > 0 then
Result := FormatDateTime(aDisplayFormat, aValue)
else
Result := FormatDateTime('c', aValue);
dtString, dtChar, dtMemo:
if Length(aDisplayFormat) > 0 then
Result := FormatMaskText(aDisplayFormat, aValue)
else
Result := aValue;
dtBoolean:
begin
{$WARN UNSAFE_CAST OFF}
if (Length(aDisplayFormat) > 0) and (TVarData(aValue).VType = varBoolean) then
{$WARN UNSAFE_CAST ON}
begin
liPos := Pos(';', aDisplayFormat);
if (liPos > 0) then
begin
if (liPos > 1) then
begin
lsTrueString := Copy(aDisplayFormat, 1, liPos - 1);
lsFalseString := Copy(aDisplayFormat, liPos + 1, Length(aDisplayFormat));
end
else
begin
lsTrueString := '';
lsFalseString := Copy(aDisplayFormat, liPos + 1, Length(aDisplayFormat));
end;
end
else
begin
lsTrueString := aDisplayFormat;
lsFalseString := '';
end;

if (aValue) then
Result := lsTrueString
else
Result := lsFalseString
end
else
Result := aValue;

end;
end;
end;

initialization
gcDisplayFormat := TmyDisplayFormat;
finalization
gcDisplayFormat := TmyDisplayFormat;
end.



Now we can check the results by starting the ReportBuilder and changing the DisplayFormat in the DBText component.
  • Useful links:
Changing the currency format.
Currency Formatting.
Errors while compiling.

0 comments:

Post a Comment