Lookup Parameters - FROM ACCOUNT … TO ACCOUNT 

It s recommended that you create a copy of the existing report before adding parameters. If something goes wrong you can revert back to your original report.

In the "Report man" screen, select your report and click on the Copy report button.  

If you already added a parameter or wish to move or resize parameters, to align them with other reports, you may wish to resize the the layout of the parameter selection fields.

It is a good idea to note the position (i.e. Left and Top), and the size properties (i.e. Length, Width, Height and size) of other report parameters.

Add lookup parameters 

On the copy of your report, click on the Print preview button to print some data on screen. 

Click on the Parameters button.

The Lookup parameters will launch the lookup for the selected account type. On the lookup you may select a specific account or a range of accounts to be included in your report. 

To do this, you need to create (add) two (2) parameters. 

1.From account 

2.To account  

Once the Lookup parameter is added, you need to add some basic SQL to the report to add the functionality to the report.

In this example, the Debtors lookup will be added.

Add From account parameter

The From account lookup 

To add the lookup parameters:

1.On the "Report man" screen, select your report and click on the Parameters button.

reportman-parameters-add

2.On the parameters for your report, click on the Add button.

reportman-parameters-add-lookup-from

3.Select the options as follows:

a)Size and position - Enter the values for the Left, Width, Top and Height properties to position your parameter.

b)Translation - Click on the ... icon and search for the language label. Select your label (in this example, "From account" is selected) and click Ok.

4.Click on the Save and rebuild button. This will position the new parameter and add the translation on the "Parameter" screen. 

reportman-parameters-add-lookup-from-sql

5.Enter the following options: 

a)Type of control - Select "OD Lookup Debtors".

b)Size and position - Check and edit the values for the Left, Width, Top and Height properties to position your parameter, if necessary.

c)Column origin - To link the account code, you need to assign a valid column (database field) SACCOUNTCODE. 

Note the "SQL" field is not available at this stage. 

6.Click on the Save and rebuild button.  

7.Click on the Save button to save your parameter and to close exit the parameters screen. 

If you do not click on the Save button, your parameter or any changes to the parameter will be discarded (lost).

8.Reopen the "Parameters" screen. 

9.Click on the "From account" parameter. The SQL area will be added to the parameters. 

reportman-parameters-add-lookup-from-control

10.Enter the following SQL: 

“ALL and WAccountTypeid = 1”

All needs to be in the UPPERCASE (i.e. “ALL and WAccountTypeid = 1”).

If "All" is in the lower case (i.e. “All and WAccountTypeid = 1”), the lookup will produce the following error:

"Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 1
All"

11.Click on the Save and rebuild button.

12.You may proceed to add the "To account" parameter. 

If you need to exit the "Parameters" screen at this stage, click on the Save button. If you do not click on  the Save button, your parameter or any changes to the parameter will be discarded (lost).


Add To account parameter


To add the lookup parameters:

1.On the parameters screen for your report, click on the Add button.

reportman-parameters-add-lookup-to

2.Select and or enter the options for the "To account" parameter. 

reportman-parameters-add-lookup-to-control

3.Select the options as follows:

a)Type of control - Select "OD Lookup Debtors".

b)Size and position -  Enter the values for the Left, Width, Top and Height and Size properties to position your parameter.

c)Translation - Click on the ... icon and search for the language label. Select your label (in this example, "To account" is selected) and click Ok.

d)Column origin - To link the account code, you need to assign a valid column (database field) SACCOUNTCODETO. 

Note the "SQL" field is not available at this stage. 

4.Click on the Save and rebuild button. This will position the new parameter and add the translation on the "Parameter" screen. 

reportman-parameters-add-lookup-to-rebuild

5.Click on the Save button to save your parameter and to close exit the "Parameters" screen. 

If you do not click on the Save button, your parameter or any changes to the parameter will be discarded (lost).

6.Reopen the "Parameters" screen. 

7.Click on the "To account" parameter. The SQL area will be added to the parameters. 

reportman-parameters-add-lookup-to-sql

8.Enter the following SQL: 

“ALL and WAccountTypeid = 1”

All needs to be in the UPPERCASE (i.e. “ALL and WAccountTypeid = 1”).

If "All" is in the lower case (i.e. “All and WAccountTypeid = 1”), the lookup will produce the following error:

"Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 1
All"

9.Click on the Save and rebuild button.

10.Click the Save button. This will save and close the report parameter design screen.

If you do not click on the Save button, your parameter or any changes to the parameter will be discarded (lost).


The Parameters are available on the "Parameters" screen but will not work at this stage. 

You need to add the parameters and some SQL to the report.

You will still be able to preview the report at this stage without using the lookup parameters.


Add lookup parameters to reports


Add lookup (From account and To account) parameters to report 

You basically need tot add the following parameters to the report parameters:

From account parameters

Parameter 

Data type

Sets this parameter to

Data

SACCOUNTCODE

String


Account id

SACCOUNTCODETX

String Substitute

@SACCOUNTCODETX

Account code

To account parameters

Parameter 

Data type

Sets this parameter to

Data

SACCOUNTCODETO

String


Account id

SACCOUNTCODETOTX

String Substitute

@SACCOUNTCODETOTX

Account code


Add From account (SACCOUNTCODE) String parameter


To add the From account string parameter to the report:

1.On the "Report man" screen, select your report and click on the Edit button. (You may also double-click on your report).

2.On the "Database and connections datasets" screen (accessed from the first icon or from the Report → Data access configuration menu), click the Parameters button. 

reportman-add-report-lookup-from

3.On the "Parameter definition" screen, select the "ACCOUNT" dataset and click on the Add icon.

4.On the "New param" screen, enter "SACCOUNTCODE".

The name if this parameter must be entered exactly as entered in the "Column origin" field for the "From account" parameter (i.e. "SACCOUNTCODE") on the parameter design screen. 

5.Click Ok. This will add the parameter. 

6.Select the "SACCOUNTCODE" parameter.

reportman-add-report-lookup-from-string

8.On the "SACCOUNTCODE" parameter, select and configure the options as follows:

a)Data type - Select "String".  

b)Properties - "Visible" and "Never visible" as well as "Allow Nulls" fields may be ticked. The other options may be left blank. 

c)Description - Enter a description ("From account" as per this example).

9.You may proceed to add the String substitute parameter ("SACCOUNTCODETX") for the "From account" lookup parameter.  

Should you wish to add the next parameter at a later stage, you need to click OK on both the "Parameter definition" as well as the "Database connections and datasets" screens. If you do not click on  the OK button of the "Database connections and datasets" screen, your parameter or any changes to the parameter will be discarded (lost).


Add From account (SACCOUNTCODETX) String Substitute parameter


To add the From account string parameter to the report:

1.On the "Report man" screen, select your report and click on the Edit button. (You may also double-click on your report).

2.On the "Database and connections datasets" screen (accessed from the first icon or from the Report → Data access configuration menu), click the Parameters button. 

reportman-add-report-lookup-from-string-subst

3.On the "Parameter definition" screen, select the "ACCOUNT" dataset and click on the Add icon.

4.On the "New param" screen, enter "SACCOUNTCODETX".

The name if this parameter must be entered exactly as entered in the "Column origin" field for the "From account" parameter, followed by TX (i.e. "SACCOUNTCODETX") on the "parameter design" screen. 

5.Click Ok. This will add the parameter. 

6.Select the "SACCOUNTCODETX" parameter.

reportman-add-report-lookup-from-dataset

8.On the "SACCOUNTCODETX" parameter, select and configure the options as follows:

a)Data type - Select "String Substi" (String substitute).  

b)Properties - "Visible" and "Never visible" as well as "Allow Nulls" fields may be ticked. The other options may be left blank. 

c)Description - Enter a description ("From account" as per this example).

d)Sets this parameter to - Enter “@SACCOUNTCODETX”.

e)Assign to datasets – Select the ACCOUNT dataset and click on the > icon.

9.You may proceed to add the "To account" lookup parameters.  

Should you wish to add the next parameter at a later stage, you need to click OK on both the "Parameter definition" as well as the "Database connections and datasets" screens. If you do not click on  the OK button of the "Database connections and datasets" screen, your parameter or any changes to the parameter will be discarded (lost).


Add To Account (SACCOUNTCODETO) String parameter

To add the To account string parameter to the report:

1.On the "Report man" screen, select your report and click on the Edit button. (You may also double-click on your report).

2.On the "Database and connections datasets" screen (accessed from the first icon or from the Report → Data access configuration menu), click the Parameters button. 

reportman-add-report-lookup-to-string

3.On the "Parameter definition" screen, select the "ACCOUNT" dataset and click on the Add icon.

4.On the "New param" screen, enter "SACCOUNTCODETO".

The name if this parameter must be entered exactly as entered in the "Column origin" field for the "To account" parameter (i.e. "SACCOUNTCODETO") on the "parameter design" screen. 

5.Click Ok. This will add the parameter. 

6.Select the "SACCOUNTCODETO" parameter.

reportman-add-report-lookup-to-string-value

8.On the "SACCOUNTCODETO" parameter, select and configure the options as follows:

a)Data type - Select "String".  

b)Properties - "Visible" and "Never visible" as well as "Allow Nulls" fields may be ticked. The other options may be left blank. 

c)Description - Enter a description ("To account" as per this example).

9.You may proceed to add the String substitute parameter ("SACCOUNTCODETOTX") for the "From account" lookup parameter.  

Should you wish to add the next parameter at a later stage, you need to click OK on both the "Parameter definition" as well as the "Database connections and datasets" screens. If you do not click on  the OK button of the "Database connections and datasets" screen, your parameter or any changes to the parameter will be discarded (lost).


Add To account (SACCOUNTCODETX) String Substitute parameter


To add the To account string parameter to the report:

1.On the "Report man" screen, select your report and click on the Edit button. (You may also double-click on your report).

2.On the "Database and connections datasets" screen (accessed from the first icon or from the Report → Data access configuration menu), click the Parameters button. 

reportman-add-report-lookup-to-string-subst

3.On the "Parameter definition" screen, select the "ACCOUNT" dataset and click on the Add icon.

4.On the "New param" screen, enter "SACCOUNTCODETOTX".

The name if this parameter must be entered exactly as entered in the "Column origin" field for the "To account" parameter, followed by TX (i.e. "SACCOUNTCODETOTX") on the "parameter design" screen. 

5.Click Ok. This will add the parameter. 

6.Select the "SACCOUNTCODETOTX" parameter.

reportman-add-report-lookup-to-dataset

8.On the "SACCOUNTCODETOTX" parameter, select and configure the options as follows:

a)Data type - Select "String Substi" (String substitute).  

b)Properties - "Visible" and "Never visible" as well as "Allow Nulls" fields may be ticked. The other options may be left blank. 

c)Description - Enter a description ("To account" as per this example).

d)Sets this parameter to - Enter “@SACCOUNTCODETOTX”.

e)Assign to datasets – Select the "ACCOUNT" dataset and click on the > icon.

9.You may proceed to add the "To account" lookup parameters.  

Should you wish to add the next parameter at a later stage, you need to click OK on both the "Parameter definition" as well as the "Database connections and datasets" screens. If you do not click on  the OK button of the "Database connections and datasets" screen, your parameter or any changes to the parameter will be discarded (lost).


Add From ... To account lookup parameters SQL to the report

After adding the parameters to the report dataset, you need to add the SQL to the dataset (e.g. "ACCOUNT").

1.On the "Database and connections datasets" screen (accessed from the first icon or from the Report → Data access configuration menu), click the Parameters button. 

2.On the "Database connections and datasets" screen, select the "ACCOUNT" dataset and add the following SQL:

and SAccountcode <= trim(substring('@SACCOUNTCODETOTX ' from 2 for {if mysql}if{else}IIF{endif}(position (' ' in '@SACCOUNTCODETOTX ')=0,0,position (' ' in '@SACCOUNTCODETOTX ')-1)) )
and SAccountcode >= trim(substring('@SACCOUNTCODETX ' from 2 for  {if mysql}if{else}IIF{endif}(position (' ' in '@SACCOUNTCODETX ')=0,0,position (' ' in '@SACCOUNTCODETX ')-1)))” 

reportman-add-report-lookup-sql-dataset

3.On the "Database connections and datasets" screen, select the "ACCOUNT" dataset and add the following SQL:

“and SAccountcode <= trim(substring('@SACCOUNTCODETOTX ' from 2 for {if mysql}if{else}IIF{endif}(position (' ' in '@SACCOUNTCODETOTX ')=0,0,position (' ' in '@SACCOUNTCODETOTX ')-1)) )
and SAccountcode >= trim(substring('@SACCOUNTCODETX ' from 2 for  {if mysql}if{else}IIF{endif}(position (' ' in '@SACCOUNTCODETX ')=0,0,position (' ' in '@SACCOUNTCODETX ')-1)))” 

NOTE - To accommodate and strike the balance between the three (3) database types (i.e. Firebird, My SQL and Postgress), 

NOTE - The reason for this expanded SQL, is to prevent possible errors in MySQL Database types, for example:

Errors MySQL
CONTACTS:
#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'account. Saccountcode between '000003' and '000006' 
order by  49' at line 3

This must be added before the Order by SQL. 

“Order by @ORDER”  

NOTE - The Show data button will not display any data at this stage. You need to click on the OK button and close (exit) the report. 

If you click on the Print preview icon on the "Report manager designer" screen, the data will also not be available to print. An error message will be displayed:

"No data available to print!"

You need to click on the Print preview button and select the From account … To Account lookups from the "parameters" screen and click OK

4.Click on the OK button of the "Database connections and datasets" screen. 

If you do not click on  the OK button of the "Database connections and datasets" screen, your parameter or any changes to the parameter will be discarded (lost).

5.Close the report and Save the report. 

If you do not click on the Save button, your parameter or any changes to the parameter will be discarded (lost).


Test / Print the new report with the From account and To account parameter

The report should print all accounts From account … To account (according to your selection on the "Parameters" screen).  If you have selected the same Debtor account in the From account and the same Debtor account in the To account lookup, only the data of the selected Debtor account should be included in the report.

To test and preview the lookup parameters:

1.On the "Report man" screen, select your report, and click the Print preview button.

reportman-parameter-lookup-options-test

2.Select the accounts to include in the report the From account … To Account lookup screens.

reportman-parameter-lookup-lookup-test

3.Select the "Sequence" (i.e. Debtor code or Description) if not correct. 

4.Click on the OK button of the "Report options" screen. An example of the printed report, is as follows:

reportman-parameter-lookup-printed-test


You may wish to add the "From account ... To account" selection to print on the report (similar to the "Sequence" parameter option). 

Add From account … To Account parameters to the printout 

The From account and To account parameters allow you to print the report for all debtor accounts, a selected range of Debtor accounts or a specific debtor account as per your selection on the report parameter. It is a good idea to add the parameter selection on the report printout. 

A user will exactly read on the report page headers in which which account or accounts are included in the report.

Refer to the section - Reportman - Translatable label expressions in this document.


To add the lookup parameters to the report print:

1.On the "Report man" screen, select your report, and click the Edit button (You may also double-click on the selected report).

2.Select the second Page header in the report structure. 

3.Select the Expression icon to insert a basic expression.  The default expression is “2+2”.

reportman-parameter-lookup-add-labels

4.On the Expression tab, click on the ... icon to edit the expression. 

5.Select the "Variables" in the "Category" section.

These Variables is automatically added when adding parameters Datasets.  

From account - M.SACCOUNTCODE and M.SACCOUNTCODETX, 

To account - M.SACCOUNTCODETO and M.SACCOUNTCODETOTX 

Sequence - M.SEQ, M.SEQTX and M.SEQTX2

6.Remove the default "2+2" SQL in the expression.

7.Select the M.SACCOUNTCODETX string substitute ("From account") parameter and click on the Add selection button (or double-click on the selected operator) to add the operator to the SQL section.   

8.Select the M.SACCOUNTCODETOTX string substitute ("To account") parameter and click on the Add selection button (or double-click on the selected operator) to add the operator to the SQL section. 

9.Join these two variables by typing the + ' - '+ after the first variable (This will insert a space and a hyphen followed by a space). The expression should be as follows:

M.SACCOUNTCODETX + ' -  '+
M.SACCOUNTCODETOTX

10.Click on the Show Result button to check if it is displayed correctly. The "From account" and the "To account" to will display correctly on the "Information" screen according to your previous selection on the Report options.

11.Click Ok on the "Information" screen and OK on the "Dialog" screen to save the expression.

 

Add labels for From account … To Account parameters to the printout 

At this stage, only the Account codes will be printed on the report. To describe these codes on the printed report, you need to add labels.

Add labels to the From account and To account expressions:

1.On the "Report man" screen, select your report, and click the Edit button (You may also double-click on the selected report).

2.Select the "M.SACCOUNTCODETX" expression and click on the Expression tab to edit the expression.  

3.On the Expression tab, click on the ... icon to edit the expression. 

reportman-parameter-lookup-add-labels-print

The required label id's from the Language files were already selected (in the "Translation" field) when the Parameters were designed: 

From account - SACCOUNTCODE - (translatable language id 246 ). 

To account - SACCOUNTCODETO - (translatable language id 2011).

4.Add a label From account : (translatable language id 246 and description).  

5.Add a label To account : (translatable language id 2011 and description). The completed Sql for the From and To account is as follows:

CUSTOM('GETTEXTLANG',246,'From account',0,0,0)
+ ':  ' + 
M.SACCOUNTCODETX + ' -  '+
CUSTOM('GETTEXTLANG',2011,'To',0,0,0) 
+ ' :  ' + 
M.SACCOUNTCODETOTX

6.Click on the Show Result button. 

Take note the spaces of the colon on the "Information" screen. From account: (no space) and To : includes a space. 

You may decide to add a space after From account : or to remove the space after To :

7.Click OK on the "Information" and the "Dialog" screens.

8.Click on the Print preview icon to preview the report. The Debtor accounts selected in the report parameters will display your selection on the report printout.  

reportman-parameter-lookup-add-labels-test

You may wish to : 

Set font properties (e.g. Bold, size, etc.).

Insert a Horizontal line 

Move the Sequence parameter down. 

9.Close the "Print preview" screen. 

10.Add the necessary touches to tidy the report layout. 

11.Click on the Print preview icon to preview the report.

reportman-parameter-lookup-add-labels-tidy

12.Close the "Print preview" screen. 

13.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).