The Account type prefix is already implemented on most reports. This will indicate whether it is a debtor, or creditor, or other account types. 

The Account type is automatically displayed in the "From account" and "To" in the report parameters; but it is not displayed in the "Detail" section of the report structure for the "ACCOUNT.SACCOUNTCODE" expression. 

Account type id

Account type prefix

Description

0

G

General ledger (Balance sheet and Income / Expense accounts)

1

D

Debtor accounts

2

C

Creditor accounts

3

B

Bank accounts

4

T

Tax accounts

In the account code field the code expression “ACCOUNT.SACCOUNTCODE” excludes the Account type id.

reportman-expression-account-type


To add the Account type prefix to an Expression:

1.On the "Report manager designer" screen, select the Expression and click on the icon.  

reportman-expression-account-type-add

2.On the "Dialog" screen, the Database fields for the ACCOUNT contains the ACCOUNT.WACCOUNTTYPEID field. Add the following SQL before the Expression – "ACCOUNT.SACCOUNTCODE":

IIF(ACCOUNT.WACCOUNTTYPEID=0,'G','')+
IIF(ACCOUNT.WACCOUNTTYPEID=1,'D','')+
IIF(ACCOUNT.WACCOUNTTYPEID=2,'C','')+
IIF(ACCOUNT.WACCOUNTTYPEID=3,'B','')+
IIF(ACCOUNT.WACCOUNTTYPEID=4,'T','')+

3.Click on the Show Result button.  The Account prefix or account type id is added to the Account code on the "Information" screen. 

4.Click Ok to exit (close) the "Information" screen and OK to save the updated SQL for the Expression. 

NOTE – ACCOUNT is the Dataset and refers to this report. If you use this SQL on other reports, you may need to check that the correct Dataset is referenced. If the dataset is not correctly referenced, SQL errors will be produced. 

5.Click on the Print preview icon to check the report.

reportman-expression-account-type-preview

The account type prefix (WACCOUNTTYPEID) is correctly defined and printed in the "Detail" section of the report. It is consistent with the data in From account … To expression on the Page header section of the report. 

6.Close the "Print preview" screen.   

7.Close the "Report manager designer" screen. Remember to Save the report. 

Whenever you close the "Report manager designer" screen, after editing your report, remember save the report. If you click on No, on the "Save report?" confirmation message, your changes will be discarded (lost).