Simon Clive: Created page with "Template Designer ERP has a complete Report Designer within the software. This allows any User with SQL database knowledge, the ability to design and create their own template..."

2011-06-29T00:37:19Z

Created page with "Template Designer ERP has a complete Report Designer within the software. This allows any User with SQL database knowledge, the ability to design and create their own template..."

New page

Template Designer

ERP has a complete Report Designer within the software. This allows any User with SQL database knowledge, the ability to design
and create their own templates to report on data stored within ERP. The User has the freedom to customise templates and reports to
suit their own Organisations requirements. Refer to the detailed user manual for the report builder module called LearnReportBuilder.pdf<File=LearnReportBuilder.pdf>

Note: If you have customised your Templates, it is important to remember to Export your Custom Templates BEFORE running any
ERP Update. Exporting Customised Templates to an archive folder as you complete them is a good practice. Some custom
templates may need modifying after a software update.

How To View Templates

Select the General tab.
Select the Templates button
OR select Templates via the top menu bar under, Reports>>Templates.

The Customise Templates screen will load with the Cursor in the Search Text field.
Start typing the name of the Template you wish to open/view, e.g. Sales Order. Alternately you can also scroll up/down to find
the report that you're looking for. The arrow on the left of the List will indicate which Template is selected, as more than one
template will be highlighted when Sal is typed into the Search field.
Select View Report to open the Print Preview, view of the selected Template.

The Preview will load as 1 sheet per page, (full page view). Left zoom button view.
Select the middle zoom button to zoom in for a half page close-up view.
Select the right zoom button to display the page as actual size (100%).
Enter a % value manually into the zoom percentage field to display the preview as required, e.g. 80%.
If the report is spread over multiple pages you can toggle between these using the toggle arrow buttons.
Select the Printer icon to print the report from within this Preview page.
Select Close to return to the Customise Templates screen.

The Customise Templates screen displays 3 columns:

1. Template Name. This indicates the actual name of your Template. It is a good idea to name all related Templates
with the same first name, e.g. Sales Order, Sales Order Matrix etc. This keeps them listed together for convenience.
This field can be sorted alphabetically in either ascending or descending order by clicking on the field header. The
order is indicated by the finger pointer.

2. Type field. This indicates what area of ERP the template is designed to be used for, e.g. Sales Order Type to create
a new 'Sales Order' template. The Type list is predefined; meaning a report Type can only be one that is already specified in ERP.
This column can also be sorted alphabetically in either ascending or descending order by clicking on the field header. The order is
indicated by the finger pointer.

3. Default. If this field is ticked it indicates that this template is the default template used by ERP when printing reports of that Type.
The default can be changed to any other Template of the same Type. There can only be one default template set for each 'type'.
This column can also be sorted by ascending or descending order by clicking on the field header, conveniently listing all your 'default'
templates together. The order is indicated by the finger pointer.

How To Edit Templates

Select the Customise Templates window as shown above.
If you know the name of the report you can type it in the Search text and it will appear as highlighted, otherwise you can also
scroll up/down to find the report that you require. In this example we will use the Sales Order report.
Double-click on the Report and this will open the report in the ERP Report Designer.
By default the Template is opened in Design mode but you can always view a Preview by clicking on the Preview tab.
Back in Design mode you have a wide range of tools along the top of the screen to customise the template as required by the
organisation. These tools are covered in more detail below in How to Create New Template.

How To Create a New Template

In ERP you can re-create any existing template and customise it as required by your organisation. Customisation is limited to the
available SQL String per template, and the SQL knowledge of the person designing the Template. LearnReportBuilder.pdf<File=LearnReportBuilder.pdf>
You can also create totally new report templates from a blank page, but this requires advanced knowledge of SQL Strings and ERP
software, therefore it is probable that you will have to seek the skills of a ERP Programmer to achieve your desired results in a timely
fashion.

The process of creating a new template by using the base of an existing one, involves copying all the detail from the original (including
the layout and data elements) into the new template, then amending it.

To create a new Template go to the Customise Templates window and select New. This will create a new line in the Template
list and will allow you to enter a name and type for the new template
In the Template Name field enter the name for the new template and in the Type field select a Template Type from the dropdown
list. In this example we will use the Invoice type and name it Invoice New
Double-click on the newly created template to load the Report Designer. Note that this template is currently blank.
To exit from this screen use File=>Close from the menu bar, or you can use the x button at the top right.

NB: Closing out of the Template Designer will NOT save any changes that have been made to that template. You will
Always have to select the 'Save/Close' button on the Customise Templates window before changes to templates
will be saved.

We now need to copy the SQL String from the original Invoice and paste it into the Invoice New.
Select Invoice from the Template list.
Highlight ALL of the SQL String and copy it.
Select Invoice New from the template List.
Highlight ALL of the SQL String then paste the SQL string you have just copied from the original Invoice.

Note: SQL (acronym for Structured Query Language) is a standard method of conveying information to and from a database.
In ERP the SQL String allows information to be retrieved from the ERP Server and used in Reports.

The SQL Strings used in ERP are unique for every report; therefore it is vital when we create new reports that the
SQL String is copied from the correct Report Type.

Double-click on Invoice to open the Report Designer.
Select all off the layout of the template by using Ctrl+A and then copy it into your clipboard by using Ctrl+C
Take a note of how many breakpoints the layout of the Template has. In the Invoice below we have 3 breakpoints; Header,
Detail and Footer. These are indicated by the grey bar going across the page.
The reason for this is that the new report will also need to have the same in order for the layout to be pasted correctly.
Take a note of how much of the page is used in between the breakpoints.

Close out of 'Invoice' and Open 'Invoice New'.
By default any new invoice template will open with 3 breakpoints; Header, Detail and Footer and will not have much space
between them

Note: Remember you can always use 'Save/Close' to save changes to templates and then re-open the report to carry on working.
This will ensure you capture correct settings of your new template design before you proceed to make further changes that
you may not initially get right.

We now need to resize the distance between breakpoints so that the layout that is to be pasted will fit correctly.
To resize the distance between breakpoints you can simply click and drag the grey bar up or down for each breakpoint
Following on from our example of the Invoice the Header covers just over 5 inches, Detail covers around half an inch
and Footer covers just under 4 inches. We will use around an inch more for each one as we can re-adjust distance after
pasting the layout.

Once the breakpoint distances have been set, click into the body of the template and then paste the Invoice template
using Ctrl+V (by default time/date and page numbering are inserted on blank reports).

You can now resize the breakpoints to fit the template content, by dragging them back up to the base of the text.

NB: By default you will not be able to drag the breakpoint beyond the text.

If we now preview the Invoice it will show up exactly as the original Invoice.

You may have noticed that there are a number of tools in the ERP Template Designer that can be used to edit any report.
The first and most important one is the 'Select Object'.

Using the 'Select Object' you can select, highlight, move and resize almost any item on the template. When the Report
Designer is opened the 'Select Object' is selected by default.

Label Object. This allows the adding of a text Label onto the report. You can then change the properties by right-clicking
on it.

If you right-click while on an existing object anywhere on the ERP Report Explorer, you will be able to view and change the
setting for that item, e.g. below are the options for the Label Object.

Memo Object. This allows the adding of a longer piece of text onto the report, for example a terms statement.

Rich Text. Using this you are able to add text in Rich Text Format.

System Variable. This function will allow you to select from a list a variable that the system has already stored, for example
the date, page numbering etc.
First select the System Variable object, place it on the report.
Then choose the variable you require from the dropdown list on the toolbar.

Image. Most organisations need to use images such as their logos onto their templates. The image function allows you to
import external images and customise them on the template.
Select the Image object and place it into the template.

You would then need to right-click on the image box, select 'Picture', browse to the picture and select it to import it
onto the template.

Using the right-click options you would then be able to edit and customise the reports as required.

Shape. This function will allow you to select from a list a shape that the system has already stored, for example square,
circle etc.
First select the Shape object, place it on the report and then choose the shape you require from the dropdown list on the
toolbar.

Barcode. You can add barcodes to specific reports by selecting the Barcode Object and placing it on the report. You can
then change the human readable text/numbers of the barcode from the textbox.

All of the objects detailed above are Standard Components that can be used on the report; they are independent and are not
associated to any specific data in the ERP Database.

The next set of Components in the ERP Report Designer is Database Components. These are also objects that can be used
on any report but the essential difference is that they are linked to data stored in the ERP Database and retrieve information
directly from it.

In the ERP Report Designer we have 6 Database Components in total;

DBText 2;
DBMemo;
DBRichText;
DBCalc;
DBImag;
DBBarCode;
Apart from being able to link to fields in the ERP database these are very similar to their Standard Component equivalents
(apart from DBClac).

Select a Database Component field and place it on the template
Select the required data from the drop-down list to link the field to a specific field in the database which will then show
on the Report.

In the example below a Database Text Object (DBText) is selected. When the object is placed on the report a list box
appears in the Toolbar which shows all of the available fields for the Text Object.

Select the AmountDue as per example below selected.

NB: The field now displays Sum(AmountDue)

The database fields list box does not contain all of the fields of the database per template. The fields that are available to
select from the list box are determined by the SQL String which is used in the report/template when it was created
(detailed above).

Fields can be added and removed from the list box using the SQL String, but this requires advanced knowledge of SQL and
ERP Software and is recommended to be carried out by ERP approved Personnel.

Regions; Subreports; and Cross Tabs can be added to a report by using the Advance Components.

To make best use of these features please refer to the Report Builder Help LearnReportBuilder.pdf<File=LearnReportBuilder.pdf> or an ERP approved Consultant.

Format and Edit Toolbar. All of the buttons here function the same way as they would on any Microsoft text editing software
i.e. Microsoft Word
Nudge Toolbar. Using these buttons all of the selected components would be moved up or down or left or right by 1 pixel.
e.g. The highlighted sections, Company Name down to Phone/Fax etc. have been nudged down. In this instance, over top of
other fields.
Size Toolbar. Based on the button you select it would determine the min/max width or the min/max height of all the selected
Components and will then set the width/height of all the components to that value.

§ Shrink Width to Smallest
§ Grow Width to Largest
§ Shrink Height to Smallest
§ Grow Height to Largest

Align or Space Toolbar. The buttons on this toolbar will assist in positioning components relative to one another and
relative to the band in which they appear.

§ Align Left
§ Align Middle
§ Align Right
§ Align Top
§ Align Center
§ Align Bottom
§ Space Horizontally
§ Space Vertically
§ Center Horizontally in Band
§ Center Vertically in Band

Align Left: Aligns a group of components with the leftmost position of the component that was selected first.

Align Middle: Centers a component based on the horizontal centre of the component that was first selected.

Align Right: Aligns a group of components with the rightmost position of the component that was selected first.

Align Top: Aligns a group of components with the topmost position of the component that was selected first.

Align Center: Centers a component based on the Vertical centre of the component that was first selected.

Align Bottom: Aligns a group of components with the bottommost position of the component that was selected first.

Space Horizontally: Spaces a set of components based on the leftmost position of the first component selected and the
rightmost position of the last component selected.

Space Vertically: Spaces a set of components based on the topmost position of the first component selected and the
bottommost position of the last component selected.

Centre Horizontally in Band: Centers a component horizontally within a band.

Centre Vertically in Band: Centers a component Vertically within a band.

Note: Please refer to a ERP Consultant or the Report Builder Help if you require any further clarification on Templates.

20 Templates - Revision history

Simon Clive: Created page with "Template Designer ERP has a complete Report Designer within the software. This allows any User with SQL database knowledge, the ability to design and create their own template..."