Customization

Add FromDocNo and FromDocDate Variable in Report Design

Go in Report Design | Click on then place at the grid – refer below Pic
1. Right Click on Variable2 | Select Calculation |
  
  After Done press OK
If the date is not appearing in report preview
1. Click on Calc Tab
2. Menu bar | click View | Select Events
3. Look for Variable
4. Click on OnPrint
5. Click on Document_Detail
6. Click on FromDocNo
7. Drag from FromDocNo to the query box.

Create SUM Variable for TAX Column

Click on the variable icon
Click on where you want to create a new variable, right click on the variable created > go calculations , Insert the query in the next section accordingly
Click on the variable created, and make sure choose it to DOUBLE.

Query:

Formula for Sub Total

Variable1.DisplayFormat := Option.GetFieldValue('AccountingValueDisplayFormat');

Value := GetAggregateValue(agSUM, Document_Detail, 'DocKey', 'Amount');

Formula for Total Exclude GST (RM)

Variable2.DisplayFormat := Option.GetFieldValue('AccountingValueDisplayFormat');

Value := GetAggregateValue(agSUM, Document_Detail, 'DocKey', 'LocalAmount');

Formula for Total of Tax Amt (RM)

Variable5.DisplayFormat := Option.GetFieldValue('AccountingValueDisplayFormat');

Value := GetAggregateValue(agSUM, Document_Detail, 'DocKey', 'LocalTaxAmt');

Formula for Total Include GST (RM)

Variable6.DisplayFormat := Option.GetFieldValue('AccountingValueDisplayFormat');

Value := GetAggregateValue(agSUM, Document_Detail, 'DocKey', 'LocalAmountWithTax');

Disable Query for Deleted Field

If you had deleted any field in report designer with Query, then u will get the below message, click OK
Click on the blue colour report name to enter in report designer page
Click on the field, the field name will be shown at the left bottom of the application as shown below. If you had deleted the field, refer to step 4.
Click on CALC, go View > Modules
Follow the steps below:
1. Click on Event Handlers
2. Go to procedure DetailBeforePrint
3. An error will be shown at the bottom, double click on it, and the system will redirect to the row with the error
Enter '//' as in the image below :
Right click and compile , make sure no error > save > close the preview screen and try preview again.

Note

You can also refer to the guidance video at HERE regarding the above issue.

Display Exemption Number & Expiry Date in Invoice Template

Part A : Custom | Maintain Customer

Part B : Report Format add customer exemption Number

Go to Sales | Sales Invoice | Select any of your invoice | Preview | choose your invoice format template | click on the bottom blue color report name to go into report designer page
Insert Exemption Number label
1. click on the A label icon
2. click on the area that you want to locate the field
3. rename the label
Insert Tax Exemption Number DBText
1. click on the DBText field
2. click on the are that you want to located the field
3. select Document Company & TaxExemptNo
Insert Expiry Date label
1. click on the A label icon
2. click on the area that you want to locate the field
3. rename the label
Insert Tax Expiry Date DBText
1. click on the DBText field
2. click on the are that you want to located the field
3. select Document Company & TaxExpDate

Part C: Save as a new report name & preview the new format

Once you complete, go file | save as | enter a new report name | Save
Preview your new report
The exemption number & expiry date added

Drag XML Report into SQL

note

If this XML Report is Customer Aging, go Customer Aging to drag the XML Report

If this XML report is Supplier Aging Report, go to Supplier Aging to drag the XML Report

Open the XML Report & also Customer Aging, drag the XML Report into the Report Name field
Double Click on the new report template in blue color.
Click on the arrow down key to choose the right template
- Eg : If the report Name is Cust-Local-L0-06 Month
  - L0 = no special grouping, the template you have to choose eg Cust 06 Month Aging
  - L1 = Grouping by 1 level, the template you have to choose eg Cust 06 Month Aging – Level 1
  - L2 =Grouping by 2 level, the template you have to choose eg Cust 06 Month Aging – Level 2
- Eg : If the report Name is Cust-Local-L0-06 Month
  - 04 = 04 Month
  - 06 = 06 Month
  - 12 = 12 Month

Fix Report Tax Rate show A instead of 6%

Preview the Report format, Click on blue color link and enter into Report Design.
Check GST Summary using which Pipeline | This Report using plSQL_7.
Click on Calc Tab.
Click on Menu View | Select Module

Click on Events | Click on Main:plSQL_0 | Look for SQL_7(Refer Step 2 to see which pipeline) | Insert TaxRate

Query:

SQL_7 := 'SELECT DocKey, Tax, TaxRate, Sum(LocalAmount) LocalAmount, Sum(Qty) Qty, Sum(LocalTaxAmt) localTaxAmt, Description '+
    'FROM Document_Detail ' +
    'Where Tax &lt;> ''''' +
    'GROUP BY Dockey, Tax, TaxRate';

Click on Event Handler | Select Procedure ReportBeforePrint | Look for Pipeline SQL_7 | Insert TaxRate

Query:
```
LocalSQL_SELECT(plSQL_7, SQL_7, 'Dockey;Tax;TaxRate');
```
Click on SubReport1:plSQL_7 Tab
Click on VarTax | Right Click | Select Calculation

Amend the Query | Press OK

Before Amend:
After Amend:

Query:

Value := plsql_7.getfieldvalue('Tax') + ' @ ' + plsql_7.getfieldvalue('TaxRate');

File | Save
Close the report builder
Preview again the same report format.

Official Receipt Format Set Knock Off Grid to Show Full Width

Go to subreport1 : Document_KnockOff
Go to Report, click on Title
Move all field in Column Header to Title
Go to File > Page Setup...
Go to Layout, change the column to 1
Now, it expands to full page, you can adjust all your column as your wish.
Once you done adjust, go file > save as, key a new report name.
Close the preview, and preview your new report name.

Rounding of Figure in Report

Sample Invoice with Rounding RM0.02 :

Using Tax Invoice format 06-Tax-Invoice

note
Click on blue color link - 06-Tax-Invoice
Click on Calc Tab
Right Click select Module
Click on Declarations
Declare SQL_Rounding under Variable

Click on Events | On Create | Procedure GlobalOnCreate insert syntax as below :

SQL_Rounding := 'SELECT DocKey, Sum(Amount) Amount, Description '+
'FROM Document_Detail ' +
'WHERE ItemCode=''RTN5Cents'' '+
'GROUP BY DocKey';

Refer Picture below :

Optional

Add additional syntax in plSQL_4 – Refer below, only if Total Amount beforeGST you want to exclude rounding amount.

SQL_4 := 'SELECT DocKey, Sum(Amount) Amount, Description '+
'FROM Document_Detail ' +
'WHERE ItemCode <> ''DISC'' '+
'or Itemcode is null ' +
'and ItemCode <> ''RTN5Cents'' ' +
'GROUP BY DocKey';

Click on Event Handlers | Click on procedure Report BeforePrint, insert syntax as below :

LocalSQL_SELECT(plSQL_Rounding, SQL_Rounding, 'DocKey');
SetDataPipelineFieldLink(Main, plSQL_Rounding, 'DocKey', 'DocKey');

File | Save | Exit from report design and go in to Report Design again.
Insert DBText | and place at the place you want.
1. Click on DBText
2. Place at the Group Footer area
3. Select plSQL_Rounding
4. Manually type in Amount
Here is the output :

After adjust the position :

Item Detail hide Item Code RTN5Cents to be appearing, refer step as below :
Click on Document_Detail Tab as below | Click on Calc | Event Handlers | Procedure RdiscOnPrint
- Syntax:
```
RDisc.Visible := (Document_Detail.getfieldvalue('Itemcode') <> 'DISC')
and (Document_Detail.getfieldvalue('Itemcode') <> 'RTN5Cents');
```
- Output :
  
  How to do if the report never create the region before?
Right click tick all the Toolbars
Click on Region component
Place at the design grid | Click at the region | Select no Fill
Select No Line
Place all the DBtext/DBRichText inside the Region.
Click on Region to check the object name :
Click on Calc tab | Right Click select Events

Look for RDisc | Select OnOnPrint | Place the syntax inside bottom box

Syntax :

RDisc.Visible := (Document_Detail.getfieldvalue('Itemcode') <> 'DISC')
and (Document_Detail.getfieldvalue('Itemcode') <> 'RTN5Cents');

note

RDisc is your object name, if you object name is Region1, then have to change as below :

Region1.Visible := (Document_Detail.getfieldvalue('Itemcode') <> 'DISC')
and (Document_Detail.getfieldvalue('Itemcode') <> 'RTN5Cents');

Update GST from 6% to 0% (show zero instead of blank)

SQL Account system build in report format will automatic detect your Tax Rate based on your invoice issued.

if invoice issued on May it will show GST @ 6%, while invoice issued on June will display GST @ 0%.
D/O issued on May, transferred to Invoice and invoice date is in June, then system will automatic detect it and set the GST rate correctly.
However, if your format is customized previously, you can refer this guideline on how to change the script from GST @ 6% to GST 0% .

Click on your report name to enter into report designer page.

Follow the steps below:

Go to Calc, click on View > select Module
Go Event Handlers
Go Procedure lbTaxAmtOnGetText(var Text:String);
Replace to this script:

var s : string;
begin
    if (plSQL_0.GetFieldValue('TaxDate') < '01 Jun >2018') or
        VarToCurr(plSQL_1.GetFieldValue('TaxAmt')) <> 0) then

        s := 'GST Amt @ 6% ' else
        s := 'GST Amt @ 0% ';
    if plSQL_2.GetFieldValue('Code') &lt;> '----' >then
        Text := s + '(RM) :' else
        Text := s + ':';
end;

To Display Tax as 0.00 Instead of Blank:

Click on your report name to enter into report designer page.
Go to Calc, click on View and select Events

Find DBTxtTaxand select OnGetText, then paste the script as below:

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

Once you completed, save the report and exit, then you may preview again your format outcome.

Add FromDocNo and FromDocDate Variable in Report Design​

Create SUM Variable for TAX Column​

Disable Query for Deleted Field​

Display Exemption Number & Expiry Date in Invoice Template​

Part A : Custom | Maintain Customer​

Part B : Report Format add customer exemption Number​

Part C: Save as a new report name & preview the new format​

Drag XML Report into SQL​

Fix Report Tax Rate show A instead of 6%​

Official Receipt Format Set Knock Off Grid to Show Full Width​

Rounding of Figure in Report​

Update GST from 6% to 0% (show zero instead of blank)​