How to use REPORT
Jump to
How to use REPORT
Before you use the Report Writer, certain requirements must be met. The Report Writer relies heavily on the information stored in the ITAS Data Dictionary (IDD). This specifies how data stored in ITAS is represented and also allows data to be given friendly names to make it more understandable to the user. Relationships between data fields can also be created in IDD - this will be explained more fully below. We also use the IDD to determine how tables are joined together to create a list of pre-formatted table types. For example, the Client Transactions table type specifies those tables that are used for reporting client transactions. The Report Writer needs these table types to be set up, and it also needs the friendly names for columns - without this information, you will not be able to create any reports.
Report Writer Windows
Main Screen
This is the opening screen of the Report Writer. It contains all the standard elements of an ITAS application (toolbar, context menus, status bar and so on). It additionally displays a list of reports that already exist. You can modify one of these by double-clicking it, or typing its name into the Report Name field, or create your own by typing in a new name in this field. From there, you can then change the properties that apply to the report. Mostly these applies to the physical look of the report - how the page is aligned, what font to use and so on. You must also define how many lines of headings the report should have, (ITAS will automatically place its standard 2 heading lines above these), and the table types that the report uses (see the requirements section).
Reports Available In ITAS: This shows a list of Report Writer reports. If you are accessing the Report Writer from another menu option, this list may only contain reports pertinent to that option. For example, if you click the Report Writer button in CLI, you will only see client reports.
As well as the report name, this display shows additional information for that report. You can select an existing report by highlighting and double-clicking it.
Report Name: This is the name of the report. The report name is free format and can include spaces, with a maximum length of 30 characters. Once you have entered the report name, you should press the OK button. If the report already exists, its details will be displayed on the screen otherwise you will be prompted to create a new report.
Orientation: This controls how the report is displayed on the page: portrait or landscape. If you intend to create a wide report, you should make it landscape. If the report does not fit into the selected orientation, it will be truncated when printed.
Margins: You can enter both, in millimetres, left/right and top/bottom margins. These will be applied to the printed reports.
Category: This is the report category. There are three categories available:
· Private: This indicates that only you have access to this report.
· Public: This indicates that other users can run this report, but are not allowed to change it.
· System: These are special reports, created by Hivedome. Generally, they should not be run from the Report Writer.
The category works in conjunction with users' Report Access settings in CMP. The CMP settings allow management to control users' report. Normally, you would be set up with Private and Own Public Reports, which will operate as described above.
Font and Size: This is the name of the font to be used, and also the font size. You can use any font installed on your system.
No. of Headings: This is the number of heading lines in the report. This excludes the standard ITAS heading lines which will be included automatically. You can always change the number of headings at a later stage.
No. of columns: This is the number of columns in the report. You can always change this at a later stage.
Table Type: This defines what type of data will be reported. The list of valid table types is maintained by IDD and would normally be controlled by Hivedome.
The More button works in conjunction with the Table Type prompt by allowing you to add more table types to your report. The table types must be set up in such a way that a link is provided between all the tables in your report. Normally, a report would contain a main table. Therefore, either every additional table type needs to reference this main table, or there needs to be a chain from the main table to an additional table type. For example, if the main table type is Physicals and you want to link to Transactions, you would create a chain from Physicals, Invoice Marking, Transactions.
Company: This prompt is required if a report only works with a specific company. Normally it would be left blank to indicate that it is a general report, but if you wanted to create a variation of an existing report for a specific company, you could create a new report with the same name and the company set up here.
Memo: The memo prompt allows you to enter free-format text describing a report.
When creating a report, you must enter all the necessary data on this screen before you actually start designing the report. Context menus also give access to the following features:
· Edit Report Layout. This takes you to the Report Layout screen, as described below.
· Import/Export Reports. This is used to copy reports between ITAS sites.
· Default Filter. This is an advanced feature that should only be used if you are experienced in the SQL language. It allows the creation of a filter that is always used whenever the report is run.
· Copy Report. This allows a report to be copied. Any report can be copied, even System ones.
· Rename Report. This allows you to rename a report.
· Produce Report. This takes you to the Produce Report screen, as described below.
Report Layout Screen
Choosing ‘Edit’ then ‘Report Layout’ from the main screen will bring you to this screen. It is divided into 4 areas which represent different parts of a report: Headings, Detail, Footers and Others. To create a report, you use standard ‘drag and drop’ techniques to place elements of a report (text, fields etc.) into the correct area. You create the elements by selecting ‘Insert’ from the context menu. The elements are represented as boxes, the content of which depends on their usage. An object can be deleted by dragging it off the grid, or by specifying Delete from the menu.
Report Elements
The elements of a report template are the objects that can be placed in the report layout itself. There are four kinds of elements:
Fields: A Field relates to a data column from one of the tables used by ITAS. For example, in the Client Ledger table, ‘Client Account’ would be a field. The value displayed on the report therefore is changeable and depends on what row (record) of data is currently being displayed.
Text: A Text item is an item that stays constant throughout a report. For example, a heading would be a text item. You create the text item in the Report Writer, specify its value, and this is displayed as always the same on the report.
Rule: There are a small number of rules in the Report Writer that represent values that cannot be displayed directly from the database ‘as is’. Hivedome creates all of these rules. As at 2Q13, the rules available mainly apply only for TRADE records:-
· aconval+acosts. Allocation Contract Value and Costs in P&L currency units
· aconval+acostsownccy. Allocation Contract Value and Costs in their contract currency units
· alloc_client. Allocated counterparty (CLI code)
· alloc_client_name. Allocated counterparty name (CLI title)
· alloc_dest. Allocated Destination code
· alloc_conterms. Allocated Contract terms code
· alloc_contermsport. Allocated Modifier code
· alloc_payterms. Allocated payment code
· alloc_afi. Allocated Shipping Status code
· alloc_trader, Allocation Trader Initials
· alloc_commod, alloc_origin, alloc_grade, alloc_cmycode4, Allocation Commodity Code
· alloc_conprice, Allocation Contract price (no conversion)
· alloc_ccy, Allocation Currency Code
· alloc_mill, Allocation Mill Code
· alloc_cropyear, Allocation Commodity Crop Year
· alloc_period, Allocation Shipment Period
· alloc_dest_descr. Allocation Port Of Destination Description
· alloc_origvalncommod, Allocation Original Valuation Commodity
· alloc_origvalndiffn, Allocation Original Valuation Differential
· alloc_origvalnperiod, Allocation Original Valuation Period
· alloc_valncommod, Allocation Valuation Market Code e.g. NY11
· alloc_valndiffn, Allocation Valuation Differential +/-
· alloc_valnperiod, Allocation Valuation Period
· amktval, Allocation market value
· amktvalownccy, Allocation Market Value (Contract Currency)
· comsn. Commission value in P&L currency units
· Comsnownccy. Commission value in contract currency units
· conval. Contract Value in P&L currency units
· convalownccy. Contract value in contract currency units
· conval+costs. Contract Value and Costs in P&L currency units
· conval+costsownccy. Contract Value and Costs in contract currency units
· costs. Costs in P&L currency units
· costsownccy. Costs in Contract currency units
· fullcommodity. Commodity codes (max 5) concatenated to form a single string (trim each field and add a space between each field)
· Netweight. TRADE net weight in wtcode units, applies shipping weight terms and tare to calculate. Can then apply advanced feature to convert to target weight units e.g. lbs, kgs
· Grossweight. TRADE gross weight in wtcode units, applies shipping weight terms and tare to calculate. Can then apply advanced feature to convert to target weight units, e.g. lbs, kgs
· Contract. 1st 6 characters of contract ref
· period, Period in DDMMMYY-DDMMMYY Format. Uses phys01_startper and phys01_endper
· Allocation. 1st 6 characters of allocation ref
· uninvoiced_amount. Contract value less total amount already invoiced (does not show –ve situation i.e. invoiced more than contract in which case will show zero)
· uninvoiced_weight. Contract weight less total quantity already invoiced.
· uninvoiced_packing. Contract packing less total packing already invoiced.
· oils_lw. Load weight when Oils Multiweight subsystem
· oils_dw. Discharge weight when Oils Multiweight subsystem
· oils_ff. Fullfilment weight when Oils Multiweight subsystem
· oils_qualresult. Show a ‘Y’ if any Oils quality result has been entered
· oils_qualbase. Show a ‘Y’ if any Oils quality Base has been entered
· provinv_ccy. Provisional invoice currency {when Invoice type=P}
· provinv_value. Provisional invoice value {when Invoice type=P}
· provinv_docref. Provisional invoice reference {when Invoice type=P}
· provinv_date. Provisional invoice date {when Invoice type=P}
· provinv_dxbp, Provisional Invoice DxBp account. Locate phys06_customsdocref where phys06_type='B'
· finalinv_ccy. Final invoice currency {when Invoice type=F}
· finalinv_value. Final invoice value {when Invoice type=F}
· finalinv_docref. Final invoice reference {when Invoice type=F}
· finalinv_date. Final invoice date {when Invoice type=F}
· physpl. TRADE (FinCalc) Physical P&L in P&L currency units
· physpljv. The JointPartner percentage of the TRADE (FinCalc) Physical P&L in P&L currency units
· originpl. The Origin Country percentage of the TRADE (FinCalc) Physical P&L in P&L currency units
· destpl. The Destination Country percentage of the TRADE (FinCalc) Physical P&L in P&L currency units
· destplexcljv, The Company % of Contract P&L at Destination. If the contract is unallocated, its value is marked to market
· polperc, Pol% expressed as Uplift e.g. 1.0375
· mktval, Market Value
· mktvalownccy, Market Value (Contract Currency)
· net_amount, Amount net of VAT
· net_amount_sum, Summarised Amount net of VAT for use in a summary report
· uninvoiced_mktval, Market value of uninvoiced quantity
· uninvoiced_mktvalownccy, Market value of uninvoiced quantity (contract currency)
· net_basis, Cotton Net Basis value
· originplexcljv, The Company % of Contract P&L at Origin. If the contract is unallocated, its value is marked to market
· physplcomp, The Company Contract P&L. If the contract is unallocated, its value is marked to market
· cifmktval, CIF market value (ASR)
· cifasr, explicit CIF cost (ASR)
· uninvoiced_amountownccy, Uninvoiced Value (Contract Currency)
· load_departure_days, Number of days difference between load date and departure date
· loadeta_loadarrival_days, Number of days difference between load port ETA and loadport arrival date
· vdate_mdate_days, Number of days difference between value date and match date
· docsrecdate_duedate_days, Number of days difference between documents received date and due date
· vat_amount, VAT Amount
· vat_amount_sum, Summarised VAT Amount for use in a summary report
· vmargin, F&O Variation margin in the TCCM currency units
· qual_adjustment, Value adjustment for Sugar based on quality result
itasdate, Current ITAS date
asset_bookvalue, Book Value of an Asset as displayed in the Asset Register
Technical note. These rules are maintained in Rpeng.dll.
Special Fields: Special Fields are total, running balance and Weighted Average. A running balance is a total that would appear in the Detail section of the report and be updated on every line. The insert Special Field/Total command is the standard method for activating report totals. The total (special field) command will then activate the production of a summary report using all of the other columns as the grouping SQL process i.e. for every distinct grouping of column data there will be a summary line.
A weighted average is a total that is weighted by another field (“weighting field”). To calculate a weighted average, the ReportWriter multiplies out each detail row of this field against the weighting field, and then divides the total by the total of the weighting field. The following example shows this:
| Contract | Quantity | Price |
| 1 | 100 | 1200 |
| 2 | 120 | 1250 |
| 3 | 150 | 1275 |
| Total | 370 | 1246.62 |
The weighted average calculation is ((100 x 1200) + (120 x 1250) + (150 x 1275)) / 370 = 1246.62
Report Sections
Sometimes you may want to separate a report into different sections to show sub-totals, especially if you want to create a sorted report (e.g. currencies appear sorted within account numbers). To create a section in a report you must use Insert Section. This will add the section to the report. You can then add fields and text to this section in the normal manner. In addition, a report has the following fixed sections:
Report Header
Items in this area will be printed at the top of each page of the report. Usually you would place only text items here since this area would be static. It is possible to place fields in this area, but results are unpredictable. The number of lines shown in the Heading area depends on the number of heading lines you specified in the main screen.
Detail
This is where the main data for the report will be shown, and so the main type of content for Detail will be fields. Only one line of detail is displayed, because all detail lines are identical. Therefore, a Text item placed in this area will be the same on every line. The number of Detail lines printed depends on the amount of data returned for the report. Detail lines can be broken up by user defined sections.
Report Footer
The Report Footer is used to display information that appears at the end of a report. In most cases, this will be totals.
Other
This is used for fields that are not displayed on the report but are still necessary, such as for sorting or for use as conditionals. For example, if you wanted to produce a report with tonnages converted to a common weight code (for example MT), you would not want to display the weight code in the detail section. However, the report needs to read the weight code from the database to know how to convert it and so weight code is required on the report. By placing weight code in the Other section, it is available for the report, but will not be printed. Note that it does not matter where in the Other section items are placed.
Properties Window
The Properties Window has two versions. One for texts and another for fields. In both cases it is entered by right clicking on the text or field in the Layout window and selecting Properties.
Properties Window for texts: Texts have several simple properties that need to be set. The text name, its position in the area, its alignment with other elements in the template, the type of alignment with these elements and printing conditions are set in this window. Printing conditions are a special feature of the Report Writer where you can make the printing of a particular element dependant on the value a field in the report. Such conditions are set in the Print This Field frame. Select the Always option if you desire a text to be printed in all reports regardless of the rest of the contents, select the If option if you want the printing of the header to depend on the value of another report field. If you select the If option then you must pick from the presented list the field that must be checked for dependency. Then you must pick the actual conditioning statement, this is done by filling two fields: In the first field you select a comparison operator (e.g. =, <, >, >=, etc) in the second field you select the value to compare with. A whole statement could be like this: text name: X, Print This Field: IF, Amount, >=, 1000CR. There are special operators that can be used in the dependency statement that are:
Between: To set a range as a dependency condition (e.g. Amount between 100 and 200).
One of: To present a list of values as the condition (e.g. Amount one of 100; 200; 1000; 2000. Will print a text if the amount of the conditional field is any of the listed). The listed valued must be separated by semicolons.
Like: To use a wildcard. This feature has to be used with the % symbol. (e.g. Account number like 00% would print the text if the account number starts with 00).
Note: The Properties window has a Apply button that will show the effect of changing some properties (e.g. text size or location) without actually closing the window. In this way you can view the change and further amend if necessary.
Properties Window for Fields: This contains the following elements:
· The first prompt selects the actual field displayed. Its IDD ‘friendly name’ as well as the underlying database field name are shown.
· The Alias is the name the field will have in the report. By default it takes the friendly name of the field, but you may need to change this, for example if you have more than one occurrence of a particular field on a report – all aliases must be unique. If your report has totals you will often have fields duplicated and so each should have a different alias. For example an amount field in the Detail section might have the alias “Amount” and the total might have the alias “Total Amount”
· Type of Field. If the field is alphabetic, you cannot change this from “Default”, but if the field is numeric you can specify the numeric type. As all dates in ITAS are numeric, you would have to select the “Date” type to view one properly.
· Number of characters. This is the length of the field.
· Sequence. This is used for sorting a report. If a field has a sequence of zero, it will not be used for sorting. The field with the sequence ‘1’ will be sorted first, followed by sequence ‘2’ and so on. The data shown in the detail area will be displayed in the specified sequence.
· Decimal Places. This is only applicable to numeric fields! It can either be a fixed number, or you can make the number of decimal places dependent on a currency or weight code (see below for further details) by selecting “Use Depdency”.
· Hide Duplicates. This will suppress the printing of values that are identical on subsequent lines.
· Reset on Change of. This works in conjunction with “Hide Duplicates”, and will cause a duplicate to be displayed if the value of the field selected here has changed.
· Weighting Field: This only applies to the Weighted Average special field type.
· DOCDES rule, can select *descr, *full, *text which will be applied on the appropriate data field e.g. PHYSCODES/Ports.
Clicking the Advanced button brings up the next group of prompts. These are not so commonly used. Those in the ‘Print This Field’ frame determine any conditions that apply to the printing of this field. Normally, this will say ‘Always’ but if you change this, you can then change whenever the value is printed. For example, if you only want to print a value when it is negative, change ‘Always’ to ‘If’, select the field from the next box, select ‘<’ from the next and then type in 0.
The next group of prompts are the ‘Dependency’ prompts. It is used for converting values and works in conjunction with the IDD where you can set columns up as being dependent on other columns. For example, a monetary amount would normally be dependent on a currency, so that you could then use the currency to perform conversions. You can select the type of dependency, the field it is dependent on, and how you want the value converted. For example, for a monetary dependency, you will get the option to convert to any of the currencies set up on your system, or to the ‘Target’ currency – the main currency of the report, if there is one.
Finally, there is an Extra SQL prompt. This should only be used by people with knowledge of the SQL language. It allows the normal column value to be overridden with whatever is entered as the Extra SQL. This extra SQL is useful for translating codes into narration i.e. use CASE statement e.g. =CASE when cmy54_type = 'A' then 'Approval' when cmy54_type='C' then 'Confirm' END.
Validation is performed when you click OK, and the field will change its appearance to reflect the modifications made. For example, an alphanumeric value will be shown as a string of ‘H’, the length depending on the display length that you selected. A number will be formatted as ‘0’s, again in the manner selected.
Field elements in the template appear in two ways: Filled with H symbols if the data assigned to the field is alphanumeric, filled with 0 symbols if the data assigned is numeric.
Note: The Properties window has an Apply button that will show the effect of changing some properties (e.g. field size or location) without actually closing the window. In this way you can view the change and further amend if necessary.
Navigation in the REPORT Form
Scrolling: Scrolling through fields is done with the TAB key. Alternatively you can navigate through the form by clicking the mouse.
Edit mode: You must enter a report name in the Report Name field to view the properties (main and layout) of the report on the form. To amend any main details, you must enter the Edit mode to enable the fields for amending. To amend or enter new details in the layout window you must also enter the Edit mode. To enter the Edit mode, use mouse right click, Edit/Main or Edit/Report Layout or click on the Edit icon in the toolbar. On entering the Edit mode, the Report Name field is disabled and all other fields are enabled.
Saving, discarding changes: In the Edit mode (main or layout) you can, at any time, save changes using mouse right click, Save Changes or clicking on the Save icon on the toolbar. You can also discard changes using mouse right click Discard Changes or clicking on the Restore Current Screen icon on the toolbar. On saving or discarding changes you will exit the Edit mode.
Clearing contents: If you wish to clear the contents of the form you can do so by exiting the Edit mode (saving or discarding changes) and using mouse right click, Clear or by clicking on the Restore Current Screen icon on the Toolbar.
Cut, copy, pasting: In the Edit mode and having a field active (highlighted) you can cut, copy or paste the contents of the field by using mouse, right click, Cut, Copy, Paste.
Closing/exiting the form: At any moment within the Edit mode you can close the form by using standard Windows closing procedures (mouse click on top left and right corners of the window or Alt F4), a message box will ask you if you want to save changes, click Yes to save changes, No to exit without saving changes or Cancel to remain within the program. If you are not in the Edit mode you can also close the form by using mouse right click, Close. In this case changes will have already been saved/discarded and therefore no message box will appear.
Printing a form: At any moment whilst not within the Edit mode you can print a form by using mouse right click, Print.
Printing a report: At any moment whilst not within the Edit mode you can print a form by using mouse right click, Produce Report.
Deleting a report template: Once the report name is entered, without entering the Edit mode, you can delete the report template by using mouse right click, Delete.
Editing details: To edit general presentation details of the report use mouse right click, Edit/ . To edit individual field and text positions and properties use mouse right click, Edit/ Report Layout. Upon entering the Layout window of REPORT you must again use mouse right click, Edit to work on the report template.
Entering REPORT windows: The main window is entered upon opening REPORT. The layout window is entered by using mouse right click, Edit/ Report Layout. If you enter the Edit mode in the Main window, you must save or discard changes to be able to enter the Layout window.
Inserting report elements: To insert a field or a text you must use mouse right click, Insert/ Field or Insert/ Text respectively. New elements need then to have their properties defined in the Properties Window. Double click on the new element to enter this window.