18 SQL Writer
From TrueERP wiki
SQL Writer
Do you know how to use the SQL writer to create custom reports and templates?
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. 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 or use LearnReportBuilder.pdf<File=LearnReportBuilder.pdf>
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. LearnReportBuilder.pdf<File=LearnReportBuilder.pdf> 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 LearnReportBuilder.pdf<File=LearnReportBuilder.pdf> if you require any further clarification on Templates.
