If you have created your own reports, from the following templates: 

Empty.rep - This report includes a standardised Page header (COMPANYDATA dataset) and a standardised Page footer. The Detail section of the Report Structure is blank. 

New.rep - This report includes a blank Page header, Detail section and Page footer. The entire report is blank. Need to add the COMPANYDATA dataset in the Page header and add the details in the Page footer

Whichever template your own new report is based on, the report will not print any data from the active Sets of Books. 

You need to add the details (content of the report), which must include a "Main Dataset" containing SQL to reference the tables and the fields to be available for the report. 

Thereafter, you may select the data fields from the table(s) and drag it onto the report. 

REPORT DETAIL

To create a new report from scratch, the debtor listing report will be used to illustrate the basics based on the "Empty.rep". This example will also illustrate to add parameters to the report.

To create a new report based on and existing report, the debtor listing report, is used as basis to create a creditor listing report.  

Add a New dataset

You need to add the details (content of the report), which must include a "Main Dataset" containing SQL to reference the tables and the fields to be available for the report.

To add a new Main Dataset:

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

reportman-main-dataset-add

2.On the "Database connections and datasets" screen, click on the Add+ icon to add a New dataset. 

3.Enter i.e. “ACCOUNT” and click Ok.

reportman-main-dataset-add-sql

On Datasets tab, you may use the Database browser to view the tables. (The ACCOUNT and DEBTOR tables). 

4.On the new Dataset “ACCOUNT” type the following Sql: 

“Select * from account”  

This will add all fields (*) in the ACCOUNT table. 

Then add the join from the DEBTOR table. 

“join debtor on account.waccountid = debtor.waccountid”

The Sql should as follows: 

“Select * from account join debtor on account.waccountid = debtor.waccountid”

5.Click on the Show data button to preview the data tables (Dataset).

reportman-main-dataset-add-show-data

Each time you click on the Next and Previous buttons the accounts will be listed from the account id (WACCOUNTID) field.

6.Close the "Data" screen. 

7.Click OK on the "Database connections and datasets" screen. 

The next step is to link the ACCOUNT dataset as the Main Dataset to the report. 


Link the ACCOUNT Dataset to the SubReport-0 

Once you have added the dataset, e.g. ACCOUNT as in this example, you need to link the Dataset to the Main Dataset. You may need to set the dataset to print only if data is available. This will prevent printing blank reports, which may only print the Page header and Page footer.

To link the Dataset to the SubReport:

1.On the report Structure tab, select the SubReport-0. 

2.Link the SubReport-0(No Dataset) to the Main Dataset (i.e. "ACCOUNT").

reportman-main-dataset-link

3."P.Only data avail." (Print only data available) set to “True”.

 If an invalid selection is made on the parameters; or if there is no data in the "ACCOUNT" dataset to print, the following error message will be produced: 

“No data available to print!”

If the "P.Only data avail." (Print only data available) set to “False”, the report will print only the Page headers and Page footers if no data is available on the "Main Dataset". 

The next step is to add the data in the ACCOUNT table to the Detail section of the report.


Add data to the Detail section of the report 

Once the Dataset is set and the tables are defined, you may proceed to select the data from the tables you need to print on the report.

To add data from the Main Dataset to the report detail:

1.On the Data tab, expand the ACCOUNT table. Add the data you wish to print. 

You could drag and drop the fields from the ACCOUNT table onto the Detail section of the report.

reportman-main-dataset-add-fields

2.Add all the data fields (expressions), as required onto the report.   

3.Click on the Print preview icon. 

reportman-main-dataset-add-fields-print

4.If data is entered on the relevant fields of the forms (screens), as per this example, the Account code, Account name (description), Telephone 1, Telephone 2,  Postal address and Delivery address will be displayed in the Detail section of this report. 

The sequence, in which the debtor (customer / client) accounts is listed, does not really makes sense on the printed report. 

The reason for this, is that the accounts is by default sorted according to the record number (id) on the ACCOUNT table. 

The next step is to change the default sort order (sequence) to account code or description in which the data in the ACCOUNT table should be listed in the Detail section of the report. 

Set the sequence (order) of the data in the report

By default, the report, will list the data according to the sequence (order) of the account id (WACCOUNTID) as in this example. 

To change this, for example, to debtor code or debtor description, you may set the SQL to change the sequence (order) of the report. 

The sequence, in which the debtor (customer / client) accounts is listed, does not really makes sense on the printed report. 

At this stage, in the (report design stage), you can set a specific sequence in which the data is displayed, for example account code or description. 

You may add the sequence parameter which will add an option to the report options parameters. The sequence in which the data on the report is listed can be selected when the report is printed or reprinted.

To set the default Account sequence for the report:

1.On the Datasets tab, select the ACCOUNT dataset.

2.Click the Show data button. 

reportman-main-dataset-add-order-sql

3.Note the Data's number (e.g. WACCOUNTID = 1; SACCOUNTCODE = 2; SMAINACCOUNTCODE =3; SSUBACCOUNTCODE = 4; SDESCRIPTION = 5; etc.). 

4.To list the accounts, according to the account name or description, you need to enter the following SQL"

“Order by 5” 

This will print the report according to the debtor (customer / client) account description.

To print the report according to the debtor code, you may set the Sql to change the sequence (order) of the report. 

Change the number 5 to 2 in the Sql

“Order by 2” 

This will print the report according to the debtor code.

5.Close the "Data" screen.

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

7.Click on the Print preview icon. An example of the printed report according to the account description (Order by 5), is as follows:

reportman-main-dataset-add-order-sql-print

8.Close the "Print preview" screen. 

The next step is to add descriptive headings for the data in the Detail section of the report.

Add headings to the Data on the Details section of the report 

From a user's point of view, the data on the report would not make sense. 

As per this example, the Account code, Account name (description), Telephone 1, Telephone 2,  Postal address and Delivery address will be displayed in the Detail section of this report.

There are 2 methods of adding labels to a report:

Static text - Add plain text labels.

Expressions - Add translatable expression labels to reference the language id. This will use the language files to translate the labels the languages are changed (Switch language on the Start ribbon).  

To add static text (labels): 

1.Select the "Detail"  section of the report structure. 

2.Select the Text icon to insert static text and drag it to the required space on the report. It will add the “TrpLabel0”, etc. for each static text field. 

reportman-main-dataset-add-labels

3.On the Label tab, replace the "Text" with your own, e.g. text. 

4.Repeat the process for all labels. Once all the static text labels are added, you may need to change the font. 

To select all the labels you need to format, select a label (e.g. Code); and while keeping the Shift key in; use the mouse to select the rest of the labels for which you wish to change the font properties. 

5.Click on the Text tab.

reportman-main-dataset-format-labels

6.Select your options to change or set the properties, and click OK on the "Font" screen.

7.Click the Preview icon. 

reportman-main-dataset-add-labels-test

8.In the example of this Preview, 2 issues need some attention:

oThere is no space between the "Description / Telephone 1 / Telephone 2" label and the "Postal address" label. The Postal address label and expressions may be moved to the right; or line breaks could be added in the Text label.

oThe static text headings is repeated for each account. To solve this by printing these Heading labels on each page once only, you may need to add another Page header and set a Print condition.

 

PAGE HEADERS

To print the headings only once on the page, you may:

Stretch the Page header and cut the headings from the Detail section of the report and Paste it on the Page header, or; 

You may add (insert) another Page header and cut the headings from the Detail section of the report and Paste it on the Page header. 

For longer reports (multi-page reports), where the Company data only needs to print on the first page and the headings on each page, a new Page header is recommended for the headings.

Add (insert) Page header 


To insert another Page header:

1.Select Page header and on the Structure tab, click on the Add icon and select Page header from the list. 

reportman-main-dataset-add-page-header

2.This will insert a new Page header to the top of the existing Page header (before the Page header containing the information from the COMPANYDATA Data set.

3.On the Structure tab, select the new (blank) Page header and click on the Down arrow to move the section down. 

reportman-main-dataset-move-page-header

4.Once the blank inserted Page header is moved, you may add the Headings from the Detail section to the new page header.

reportman-dataset-move-detail-to-page-header


Add Heading Detail to Page header

If you need to add headings to the Page header section, you may add them manually.  

Refer to Add headings to the Data on the Details section of the report in this document.

If you have already added detail in another report section (e.g. Detail section), you may cut and paste the detail (e.g. heading labels) to the new Page header. 

To move the Heading Expressions to the Page header:

1.Select the Detail section of the report structure.

2.Select the label e.g. "Account code"; and while keeping the Shift key in; use the mouse to select the rest of the labels. 

reportman-dataset-move-detail-to-page-header

3.Select Edit → Cut (Alt+X) or press the (Alt+X) keys.

4.Select the new Page header section of the report.

5.Select Edit → Paste (Alt+V) or press the (Alt+V) keys.

reportman-dataset-moved-detail-to-page-header


Add a name for the report

It is recommended to add a name or description to the report.  

To add a name for the report:

1.Move the pasted fields a bit down to add a name of your report in the Page header section. 

2.Select the Text icon to insert static text and drag it to the required space on the Page header section of the report. 

3.On the Label tab, enter "Debtor list" in the "Text" field.  

reportman-dataset-add-report-name-page-header

4.Resize the Page header. 

5.You may wish to add (insert) a Simple drawing (horizontal line).

6.Tidy the Detail section

7.Move the fields as required.

8.Resize the Detail section.

9.Click on the Print preview button to check the layout.   

reportman-dataset-add-report-name-test

10.The layout of the report seems to be fine on a one page report. To test the "Print preview" on more than one page, the Company detail on the First header will be printed on each consecutive page. 

If you do not have enough data to test the multi-page report headers, you may stretch the second Page header or the Detail sections of the report. If it works remember to resize the report sections back.

Page header - Print company details only on the first page

If the report is printed on more pages than 1 page, the Page headers, will by default be printed on the next pages of the report. 

You may set it to print only on the fist page. 

To set a print condition for the Page header:

1.Select the Page header (with Company details). 

2.On the Data tab, select the Variables Dataset. 

3.Check for the M.PAGE setting. 

reportman-dataset-page-header-print-condition

4.On the All tab, select the Print condition and click on the icon. 

5.On the "Dialog" screen, select the "Category" - "Functions" and select "M.PAGE".

6.Click on the Add selection button (or double-click) and type “= 1” after the "M.PAGE" operation. The SQL on the "Dialog" should read as follows:

 “M.PAGE=1

7.If you click on the Show details button, the result should be “False”.

8.Click Ok on the "Information" screen. 

9.Click on the Print preview button. 

10.The first Page header (Company data) will print on Page 1 only. The second page header will print on page 1 as well as any consecutive pages.

If you do not have enough data to test the multi-page report headers, you may stretch the second Page header or the Detail sections of the report. If it works remember to resize the report sections back.

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