Report Designer

Report Builder

• Report Builder - Overview
• Report Builder - Doing Simple Calculation
• Report Builder - Get Data Directly from DB
• Report Builder - Get Data from Available Pipeline

[top]

FAQ

1) How to rename the component I use?

01. Click Icon here (eg DBText)

02. Click on the place to be print/shown

03. At the Properties for XXXXXX, look for Identity | UserName & change the Name you like (Only Alphanumeric & no spacing)

[top]

2) Why when Export to PDF format the Company Logo look "ugly"?

01. In the report designer set the setting

- ImageCompressionLevel to 1

- Untick ScaleImages

02. At the Company Logo Right Click

- Tick DirectDraw

- Untick Stretch

03. Resize your Jpeg Logo to smaller dimension

04. Resize the Company Logo (Report Builder component) to the same dimension of your Jpeg Logo

[top]

3) Why when Export to PDF format the the file size so large?

01. In the report designer set the setting look for PDFSettings

- CompressionLevel to clMax

02. In the report designer set the setting look for PDFSettings | EmbedFontOptions

- efUseSubset to True (checked)

[top]

4) Special DisplayFormat

Q1 : How if data is PI-12345 but I wanted to be printed as 12345?
A : Just Enter as Follow code:-

Procedure DBTxtDocNoOnGetText(var Text: String);
begin
  Delete(Text, 1, 3);
  Text := Text;
end;

Q2 : How if data is PI-12345 but I wanted to be printed as PI12345?
A : Just Enter as Follow code:-

Procedure DBTxtDocNoOnGetText(var Text: String);
begin
  Delete(Text, 3, 1);
  Text := Text;
end;

Q3 : How if data is 17-08-2005 but I wanted to be printed as 17 hb?
A : Just Enter as Follow code in the DisplayFormat:-

dd 'hb'

Q4 : How if data is 1234-001 but I wanted to be printed as 1234 & the value is not fixed (i.e may 12345-001, 1234-001)?
A : Just Enter as Follow code:-

Procedure DBTxtDocNoOnGetText(var Text: String);
begin
  Text := SubStrOfDelimitedStr(Text, 0, 1, '-');
end;

Q5 : How if data is 1234-001 but I wanted to be printed as 001 & the value is not fixed (i.e may 12345-001, 1234-32)?
A : Just Enter as Follow code:-

Procedure DBTxtDocNoOnGetText(var Text: String);
begin
  Text := SubStrOfDelimitedStr(Text, 1, 1, '-');
end;

Q6 : How to reverse this #,0.00;-#,0.00 to this -#,0.00;#,0.00?
A : Just Enter as Follow code:-

procedure DetailBeforePrint;
var d, s : string;
begin
  s := Option.GetFieldValue('AccountingValueDisplayFormat');
  d := ValueOfSemiColonStr(s, 1)+';'+ ValueOfSemiColonStr(s, 0);  
  DBTxtC4.DisplayFormat := d;
end;

Q7 : How to set 0 when empty?
A : Below is example

procedure DBTxtTaxOnGetText(var Text:String);
begin
  if Trim(plSQL_1.GetFieldValue('TaxAmt')) = '' then
    Text := '0.00' ;
end;

Q8. How to Combine Currency Symbol with DocAmt or DBCalc.Value using TppVariable?

procedure VarSumDROnCalc(var Value : Variant);
var V : string;
begin
  V := FormatFloat(Option.GetFieldValue('AccountingValueDisplayFormat'), DBCalcSCR.Value);
  Value := Option.GetFieldValue('LocalCurrencySymbol') + ' ' + V;
end;

Q9. How to Set Current Date Time plus/minus N?

procedure Variable1OnCalc(var Value : Variant);
var lDT : Variant;
begin
  //Example deduct 1.5 hrs
  lDT := CurrentDateTime-0.0625; //(1.5/24)
  Value := FormatDateTime('dd/MM/yyyy hh:mm AM/PM', lDT); 
end;

[top]

Fast Report

• Fast Report - Overview
• Fast Report - Doing Simple Calculation
• Fast Report - Get Data Directly from DB
• Fast Report - Get Data from Available Pipeline
• Fast Report - Shift Relative To Simulator
• Fast Report - Down Then Across Column
• Fast Report - Continue Next Page & Continue From Previous Page
• Fast Report - E-Mail Client (Batch) & E-Mail (Native)
• Fast Report - SQL Lite Functions Commonly Use
• Fast Report - Get CSV Data from Memo Field
• Fast Report - Share Text Message - WhatsApp

----------------------------------------------------------------------------------------------------------------------------------------------------

When design for Fast Report Make sure the all the component in the MasterData or DetailData band set as following Height : 0.1 Stretch : True (Tick in Right Click)

----------------------------------------------------------------------------------------------------------------------------------------------------

[top]

FAQ

Why is not same on preview?

Try Untick the Option Shift by right click the Memo

[top]

Why my Layout in the Designer is different from the example shown?

Can try run This script. Please Exit SQL Product before run the script.

[top]

How to rename the component I use?

01. Click the Red A Icon.

02. Click on the place to be print/shown

03. At the Object Inspector, look for Name & change the Name you like (Only Alphanumeric & no spacing)

[top]

How to draw line for whole report?

01. Click at empty place of outer report.

02. At the Object Inspector, look for Frame & click the + sign

03. Under the Frame, look for Typ & set to True for ftLeft, ftRight, ftTop & ftBottom.

Make sure the Paper Margin for Top, Bottom, Left & Right not set to 0

[top]

How to add spacing between 2 records?

01. Right Click at DetailData.

02. Select Add child band.

03. At the Object Inspector, look for Height & set the gap Height you wanted

[top]

My More Description is very long & it shown page 1 empty & shown in page 2

01. Right Click at DetailData.

02. Select Allow Split.

03. Click at GroupFooter:GroupFooter1.

04. Double Click OnBeforePrint

05. Remove - 60

06. Click at DetailData:DetailData1.

07. Double Click OnAfterCalcHeight

08. Enter //

[top]

Show Note at Header

01. Right Click at GroupHeader:GroupHeader1.

02. Select Add Child Band & Select Stretch

03. Adjust the Child band Height as you wanted

04. Right click at Child Band & Select Stretch

05. Move all the Title to Child:Child1

06. Click Blue A icon (RichText Object)

07. Click on the place to be print/shown & set as below setting in Right Click

Stretch = True

Shift = False

Allow Expression = False

08. At the Object Inspector, Look for & set as below

DataSet ==> Main

DataField ==> Note

[top]

Show Total Qty Excluding Item Code RTN5Cents

01. Click the Red A Icon.

02. Click on the place to be print/shown

03. Enter the below code (make sure in the report had the AdjRound pipeline & GetTotal function (refer to Sales Invoice 7 (GST 1))

[FormatFloat(<Option."StockQuantityDisplayFormat">,
    IIF(Trim(<AdjRound."LocalAmount">)<>'0', 
          GetTotal('Qty')-1,
          GetTotal('Qty'))
)]

[top]

How to print 2 page in 1 A4?

01. Click Margin Icon

02. Adjust the Height to fit in 1 page

03. Select Apply to all pages

04. Click Ok

05. Set the Zoom to 100% and/or Page Within

06. Click Print Icon

07. Set Print Mode to Scale & Print on Sheet to A4

08. Click Ok to print

[top]

How to Load Notes/More Description to TfrRichView using script?

Just add below script on the TfrRichView OnBeforePrint

procedure Rich1OnBeforePrint(Sender: TfrxComponent);
var slOutput : TStringList;
    M: TMemoryStream;    
begin
  slOutput := TStringList.Create;
  M := TMemoryStream.Create;
  try
    slOutput.Text := <plST_Item."Note">; //The Note/More Description field
    slOutput.SaveToStream(M);
    M.Position := 0;
    Rich1.RichEdit.Lines.LoadFromStream(M); //eg The TfrRichView name is Rich1 
  finally
    slOutput.Free;
    M.Free;  
  end;
end;

[top]

How to reduce the Column use when Export to Excel?

Try use Export to Excel Table (XML) option & untick the Option WYSIWYG

[top]

Special DisplayFormat

Q1 : How if data is Zero(0) and I wanted to be printed as - and some space on the back?
A  : Just Enter as Follow code:-

[FormatFloat(',0.00;-,0.00;-   ',<Main."Amount">)]

Q2 : How to set a given string as this 123456121234 to show as 123456-12-1234?
A  : Just Enter as Follow code:-

[FormatMaskText('000000-00-0000;0;',<Employee."NewIC">)]

Q3 : How to set displayformat follow the option for Grand Total?
A  : Just Enter as Follow code:-

[FormatFloat(<Option."StockQuantityDisplayFormat">, SUM(<Main."TotalQty">,MasterData1))] 

OR

[FormatFloat(<Option."AccountingValueDisplayFormat">, SUM(<Main."TotalAmt">,MasterData1))]

Q4 : How to set displayformat BlankWhenZero or Hide Zeros if using FormatFloat function?
A  : Just set the display format at Tools | Options | Misc to

#,0.00;-#,0.00; ;

OR

A  : Just Enter as Follow code :-

 [IIF(SimpleRoundTo(<Document_Detail."Qty">*<Document_Detail."UnitPrice">,-2) <> 0, 
     FormatFloat(<Option."AccountingValueDisplayFormat">, 
     SimpleRoundTo(<Document_Detail."Qty">*<Document_Detail."UnitPrice">,-2)), '')]

Q5. How to Set Current Date Time plus/minus N?

procedure DBTxtTimeOnBeforePrint(Sender: TfrxComponent);
var lDT : variant;
begin
  //Example deduct 1.5 hours
  lDT := FormatDateTime('dd/MM/yyyy hh:mm AM/PM', Now - 0.0625); //(1.5/24)
  DBTxtTime.Text := lDT;
end;

[top]

How to use Line function?

Default is system will use [Line#] this will result as

Group 1
 1
 2
 3
Group 2
 4
 5
 6

Use Line this will result as

Group 1
 1
 2
 3
Group 2
 1
 2
 3

01. Select System Text

02. Enter as [Line] in Text field

[top]

Can I do running Total for each footer?

Yes just add ,2 in the SUM formula

WARNING it can't batch Printing (i.e. will keep increasing)

[SUM(<Document_Detail."Amount">,DetailData1,2)]

[top]

How can combine each string lines from detail without the line break added?

For example,

Items	Qty	Unit Price	Amount
RM-001	20	100	2000
RM-002	40	200	8000

Expected result:

RM-001,20,100,2000|RM-002,40,200,8000|

Var s: string;
Begin
  s := Items + ',' + VarToStr(Qty) + ',' + VarToStr(Amount) +'|';
  Memo1.Text := '''Copy(Memo1.Text,1,Length(Memo1.Text)-2)''' + s;
End;

[top]

How to make sure my report export to Excel as less column usage as possible?

Below is the guideline

• Use align to Grid button

• No gap between to component
• Must Align between top & Bottom of component

• Untick Diagonal when use Line
• Avoid use Rich Text (TfrRichView)
• Untick Auto Width
• Tick Stretch to Max Height (for Detail)

[top]

Why when I export to PDF with Chinese Word become "tofu" or box box?

This is because default Font is Tahoma is not support Unicode Font

Solution : Change the Font To Microsft YaHei in Report Designer

[top]

How to Maintain the QR Code Size with large Data?

Note : It might unable to scan if size too small & data too large

[top]

Show Even row with color?

  if <Line#> mod 2 = 1 then
    TfrxBrushFill(DetailData1.Fill).BackColor := clNone else
    TfrxBrushFill(DetailData1.Fill).BackColor := clSilver;

[top]

See also

• Report Designer at HIME
• Others Customisation