Report Builder is intended for use by end users who are comfortable with using Excel. It is a drag and drop report writing tool which allows the user to lay out banded reports, with groupings, subtotals and totals. It also provides a powerful way to extract data into Excel.
Users can use two types of data source: prebuilt datasets, or custom-built datasets using the semantic layer. Calculated fields and report calculations can do many of the calculations Excel users are familiar with, both on character fields and on values.
A video demonstrating the use of Report Builder is available:
Report Builder Screen
When you first run Report Builder (%WSY1150WRTN), it shows an empty report screen, which consists of:
Canvas
Button/options bar
Data Frame
Filter/Group Frame
The page is disabled apart from the “New Report” and “Open Existing Report” buttons.
Canvas
The canvas shows the current report (work in progress – WIP). When you open a report or start a new one it is the WIP report that you are looking at and changing. Each change is saved immediately; you can leave the WIP report, quit the system and log back in and it will still be there. The WIP report can be discarded or overwritten with the report action buttons as described below; it is only when you Save or Save As that the report is saved permanently.
The canvas is disabled if the Report Type is Spreadsheet Only.
Button and Options Bar
Report Actions
These buttons run actions that affect the report as a whole.
 | New Report – discard the current report and create a new one |
 | Open Existing Report – discard the current report and open an existing one |
 | Properties of Current Report – change the properties of the current report |
 | Save Current Report – save the current report with the existing name |
 | Save Current Report As – save the current report with a new name. The previous save of the current report will still exist |
 | Delete Current Report – discard the current report and delete the saved version |
 | Clear Current Report – discard the current report but retain the last saved version |
Field Formatting
These buttons apply formatting to fields selected on the canvas. Select a field by clicking on it, or select multiple fields using CTRL+click (Windows) or ALT+click (Mac). Then use the buttons to format the field or fields.
 | Bold |
 | No border |
 | Full border |
 | Top border |
 | Bottom border |
 | Top and Bottom border |
Canvas View
The View combo allows you to show the field names, the field labels or the field format on the canvas.
The Size combo allows you to set the scale of the canvas.
Actions
These buttons apply actions to the report canvas.
The Sum button allows you to automatically build total forms for the fields in the body. You can select which forms are built. The forms selected will be deleted and rebuilt using the configuration of fields in the body. Any changes that you made to the total forms will be lost. |
The Refresh button rebuilds the canvas from the saved report data in Coins ERP+. |
Run
These buttons relate to running the report.
The Designer button takes you to the report designer page for the current report. This is useful for users who have report designer skills and want to see the underlying data that is being created by the report writer. |
The Preview button generates a sample report based on up to 100 records from the data source and immediately shows the result in a PDF in a frame. This preview may not be available until mandatory parameters have been set. If something goes wrong, you can view the report preview log using the View Log link at the bottom of the preview frame. Each time a preview is run, the report output log file is written (overwritten) in $BASE/var/diag/preview_repbuilder.$UID.log, where $UID is your user ID. |
The Report button shows a report selection form (in a dialogue) for you to complete and to run the report as any other normal Coins ERP+ report. The resulting PDF and Excel/CSV output will be available on the Report Status Workbench. |
Opening an Existing Report
You can open an existing report created by you (or by another user if you have the appropriate permissions).
Press the Open Existing Report button
This loads the Open Existing Report dialogue.
Select the user who created the report and the report you want to open.
To create a new report it may be easiest to open an existing report (which could be another user’s report) as above, then use the Save As button
to save it as a new report. This also allows easier report testing – one user can create the report, then another user can copy using this method and test it.
Creating a New Report
Press the New Report button
to load the new report dialogue.
Fields
Field | Description |
Report | The name of the report for you to refer to it. It should be treated like a file name of a document on a disk. Do not use special characters (such as | , ” ’ ~ \ or ^) in the name. |
Description | A fuller description of what the report does. |
Page Layout | The page sizes and layout for the report. The list of possible sizes and layouts is controlled by SY parameter RBPAGES. |
Class | The font class to use. The list controlled by SY parameter RBSTYLES. |
Report Type | The type of report. This can be one of:
|
Clicking Cancel would discard the new report definition. Any current report on the canvas would be retained.
When you press the OK button, the busy icon will be shown in the top left hand corner. This is used whenever anything is being sent to the server.
If necessary, you can change the properties of the report using the
button.
After creating the new report, a blank canvas will be shown. The buttons are now enabled.
The data source button is highlighted because you have yet to define a data source.
Data Source
Press the data source button
to display the Data Source dialogue.
You can select an existing data source or (if you have permission) create a custom data source.
Existing Data Source
An existing data source is a data source that has been set up to be available in Report Builder (see 2.3, Report Data Sources).
If you select a pre-defined data source then you simply select from the list of available report data sources.
Custom Data Source
A custom data source is a data set you create ‘on the fly’ from the semantic layer. You need a compiler licence which allows you to compile data sets from the semantic layer.
Select a product to filter the list of collections. When you select a collection, the default alias for the resulting table will be copied from the semantic layer and the available fields will be shown.
Select the fields that are required for the report.
Press OK to compile the dataset. You can then define the output.
Data Frame
The data frame shows:
The tables of the data source (Contract in the example above); there may be multiple tables when using pre-defined data sources. The tables used in the query are shown highlighted in green.
The fields of the data source, split in to dimensions (values – strings, dates, integers) and measures (decimals – things that can be aggregated).
Dimensions also includes text, count and calculations (for character strings) which you can define when you drag them onto the report canvas. There is a calculation for decimal values in Measures.
If the data source requires parameters then they are shown at the bottom. Highlighted parameters (Financial Period in the example) are mandatory and yet to be completed.
The canvas has empty body and total forms added to it.
The query associated with the data source is automatically applied. You can view it (and change it if required) by pressing the Query
button.
Adding Fields
To add fields to the report, either:
Drag a field from the dimensions or measures frame to the canvas and drop it. This allows you to position the fields in the order you want.
Double-click a field; it will be added to the end of the body form.
You can filter fields by typing part of the name in the search box.
You can change the
show combo in the header shows labels or names.
You can change the scale of the canvas using the Scale selector
:
You may notice the Save button pulsing. This indicates that you have made changes to the current WIP report but they have not yet been saved. If you press the Save (or Save As) button, the report will be saved and the pulsing will stop.
Pressing the Preview button
shows the report so far.
Groups
To sort data, and (optionally) create header and footer forms, drag fields from the data frame to the groups frame. You can drag the group fields within the groups frame to change the sort/group order. Double-click a group to show the group dialogue.
Fields
Page
If you tick this, the report will have a new page for each different value of this field.
Ascending
If you tick this, the sort order for this field will be ascending. Otherwise it will be descending.
Header/Footer
Whether a header and/or footer form for this field should be shown on the canvas. If you are only using the group to control sorting, you may prefer not to show the forms. If a header or footer form is shown on the canvas but is empty then it will not appear on the report.
You can now drag fields to the new forms.
Filters
To create a filter, drag fields to the filters frame. The order shown is the order they will appear on the input form for the user when running the report. You can re-order the filters by dragging them within the filters frame.
Double-click a filter to show the filter dialogue.
The From, To and Matches columns are for the filters and act the same as normal from, to and matches filters in Coins ERP+.
Fixed
If you tick this, the filter will be fixed (users cannot override it at run time).
For example, you might use this to omit a specific joint venture company by putting !9,* in the matches column. This would mean not company 9, but all other companies. The user then might still override which of the other companies they want to see at run time but they would never see company 9.
The Show option allows this fixed filter to be shown on the report selection screen (if desired).
Preview
The values that will be used at preview time only. They are not used at run time. This allows you to pick some useful data to be shown on the preview report.
Run Time
Default values for the report selection screen.
If you do not want to allow the user to change the filter, tick the Hide option (in this case you will need to set a fixed filter).
Pressing Preview again will show the report so far.
The sort/group and (preview) filtering have been applied.
Run Report
Press the Run Report button
to run the report.
The report selection form is shown in a frame like any other Coins ERP+ report.
Fixed parameters or filters are shown (as defined) and you can change the run time selection fields. Click Next (
) to submit the report to the background queue to be generated. The result will appear on the Report Status Workbench.