|
|
|
Report and View Developer
The Report and View Developer allows you to create and/or amend Reports, Inquiry Screens, Analysis Views, Charts, and Pivot Views within Ostendo and include these in the Ostendo Menu structure.
The steps taken when generating a new Report, Inquiry Screen, View, Chart, or Pivot are as follows:
|
• |
Define the Style of the output and where it is to appear in the Menu Structure |
|
• |
Define the Data to be extracted to produce the output |
|
• |
Define the Parameters to be used when running the report |
|
• |
If it is a Report then create the Report output format |
In generating a new Report, Inquiry Screen or View, or copying an existing one, you should note:
|
• |
System Reports and Views Ostendo contains a base folder of reports that are held under Ostendo sub-folder ‘Reports’. These reports and views are maintained and updated by Development-X and cannot be amended by the end user. |
|
• |
Customer Reports, Inquiry Screens and Views An Ostendo user can create their own Report, Inquiry Screen or View by either (a) Creating a new Report (Inquiry Screen or View) ‘from scratch’, or (b) Copying a ‘System’ level Report, Inquiry Screen or View and amending it as required. In both cases the new report, screen or view will be created and held in an Ostendo sub-folder whose name will be the current signed on Company. |
|
• |
Reporting Priority Whenever the Report, Inquiry Screen or View is called in normal ‘day-to-day’ activity Ostendo will first look into the ‘Company’ folder to get the document. If it does not exist in that folder then the base Ostendo Report, Inquiry Screen or View will be used. |
NB: Update 237 initially introduced an Upgraded Report Writer. As this new Report Writer has features within it that did not exist in the prior versions it is strongly recommended that reports created / modified in either 237 or 238 are not moved to databases running prior to 237. If you need to create or modify a report for an Ostendo database running a version prior to 237, ensure you do this against an earlier version of Ostendo prior to 237.
List Tab
The displayed fields are taken from the Report or View record.
Data Display Options
Field Position - On the List screen you can move the field position by dragging the column heading left or right to the position where you want it to appear.
Filtering and Sorting - If you ‘check’ the checkbox then the displayed data is available for filtering and sorting:
|
• |
To sort the data in a selected column simply click on the column heading. Clicking against will sort in descending order. |
|
• |
Click on the black ‘down arrow’ to the right of your selected column heading and you can either select a specific entry to display all records containing that entry, or you can select (Custom ) to enter detailed selection criteria. |
Displayed Fields
Category - The Category to which the Report belongs as entered in the ‘Master Settings’ tab.
Name: Display only field showing the name of the Report, Analysis, Chart, or Pivot View
Type: Display only field showing the Type of Output. The displayed variants are:
Report
Analysis
Chart
Pivot
Inquiry
Edit View
Level - The entry Level at which the Report was created. The options are:
System: Reports generated by Development-X.
Company: Reports generated by the end user. ‘System’ level reports can be copied and saved as ‘Company’ Level reports. Company Level Reports take priority over a System Level report if it has the same Report Name
Menu Order: Display only field showing the sequence number that dictates where this Report will appear in a drop-down list
Master SQL: Display only field showing main data extraction criteria using an SQL Query (Structured Query Language) Statement as entered into the ‘Master Settings’ tab
Buttons
Close: This will close the Reports and Views Developer screen.
Add: This will bring up a panel for defining the new Report, Inquiry Screen, Analysis, Chart, or Pivot View. You can create a new display ‘from scratch’ or copy an existing format. In either case it will create an output who’s ‘Level’ will be preset at ‘Company’. On this panel the following fields are available.
Create from Scratch: Select this Radio Button if you are going to generate the Report, Analysis, Chart, Edit, or Pivot View from scratch. The following two fields will be presented which must be completed
Type: Select the Radio Button that represents the type of view being created. The options are:
Report
Analysis
Chart
Pivot View
Inquiry
Edit View
Name: Enter the Name of the view. Duplicate Names are not allowed
Copy from Another: Select this Radio Button if you are going to generate the Report, Inquiry Screen, Analysis, Chart, Edit, or Pivot View by copying an existing view and using that as a start point. The following options are now presented
Output to be copied: From the drop-down list select the current Report, Inquiry Screen, Analysis, Chart, Edit, or Pivot View that will form the basis of the new report or view. When selecting this option you can define what will happen to the copied view as follows:
Copy to Company: Select this Radio Button to denote that the above ‘Output to be Copied’ document will be created at 'Company' level. This will produce a 'Company-Level' document of the same name in the ‘Company’ Reports folder
Add New To Company: Select this Radio Button to denote that the above ‘Output to be Copied’ document will be used as the basis for creating a new report or view. The name of the new should be entered into the next field
Name: Mandatory entry if the ‘Add New To Company’ Radio Button has been selected.
Report File Name: Mandatory entry if the ‘Add New To Company’ Radio Button has been selected. Enter the name of the .fr3 Report if you are copying an existing Report. Once again Duplicate Report File Names are not allowed
Save: Not applicable to the List View
Cancel: Not applicable to the List View
Delete: This removes the current Report, View, Chart, or Pivot from this table
Master Settings Tab
This panel is split into three sections:
|
• |
Definition: This allows you to define the specific attributes to the Report, Inquiry Screen, View, Chart, or Pivot document. |
|
• |
Master Query: This contains the master query that relates to the extracted data |
|
• |
Conditions: Allows you to define any parameters to be used |
Section 1 - Definition
The fields in this section will vary depending upon the Type of report being generated
Reports
Include in Main Menu: ‘Check’ this checkbox if the Report is to appear in the ‘Reports’ section in the Main Menu. If this is checked then an entry is required in field ‘Category’ to denote under which Main Menu Toolbar Category the Report is to appear.
Name: A mandatory entry where you should specify the name of the report. This is the name that will appear in the Menu drop-down lists. Duplicate entries of the same name are not allowed.
Menu Order: Enter a sequence number that dictates where this Report will appear in the drop-down list in both the Main Menu and the Specific Screen. No checks are made against this entry and duplicates will not be rejected.
Category: This is a mandatory selection in which you should select the Main Menu Category under which this report will appear.
Type: From the drop-down list select the style of Report being produced. Ostendo has two distinct templates to help get you started. They are:
|
• |
Report: A Report is generally a multi-page output containing an optional Report Heading sheet containing the Report’s selection criteria |
|
• |
Form: A Form is generally a single output that does not produce a Header sheet and is usually linked to a single master record (Example, Invoice, Purchase Order, Job Sheet, etc) |
Specific Screen: An optional entry field where you can select a specific screen from the drop-down list. This informs Ostendo that, when in this specific screen, the ‘Reports’ button will contain this Report in the drop-down list
Report File Name: The Report will be added to Ostendo’s list of reports using the name supplied here. The name should be unique and comprise of Alphas and/or Numerics with no spaces. Ostendo will allocate it an extension of .frf
Script Name: Scripting can be used in the Report Layout Editor to control printing options such as printing fields under certain data conditions, etc. The script is created as a 'Before Report' style script via File>Scripting Configuration>Scripting Editor and is then selected from the drop-down list in this field
Archive: 'Check' this checkbox if you wish Ostendo to automatically generate an archived copy of the generated report. The archived document that then be recalled and reprinted via File>Archive Viewer
Archive Prefix: You can define the format of the archived document name. This can be a combination of plain text and fields taken from the Master Query (see below). For example if this was a Sales Invoice you may wish to name the archived document 'Invoice - INV1234' where the word Invoice is open format text and INV1234 is the actual Invoice number
Therefore the entry in this field will be Invoice - [INVOICENUMBER]. Note: the Master Query field is enclosed in 'Square Brackets'
Archive Folder Name: enter a Folder name under which this archived document will be located. If this folder does not currently exist under Ostendo>Archive Files then it will be created.
Analysis Views
Include in Main Menu: ‘Check’ this checkbox if the View is to appear in the ‘Views’ section in the Main Menu. If this is checked then an entry is required in field ‘Category’ to denote under which Main Menu Toolbar Category the View is to appear.
Name: A mandatory entry where you should specify the name of the View. This is the name that will appear in the Menu drop-down lists. Duplicate entries of the same name are not allowed.
Menu Order: Enter a sequence number that dictates where this View will appear in the drop-down list in both the Main Menu and the Specific Screen. No checks are made against this entry and duplicates will not be rejected.
Category: This is a mandatory selection in which you should select the Main Menu Category under which this View will appear.
Master Key Field: If you wish to display the View into groups (Example: All BOM Components within a Parent Item) then you should identify the Key field by which the grouping is to be made
Detail Key Field: If you wish to display the View into Sub-Groups within the above Master Key Group then you should identify the Detail Key field by which the sub-grouping is to be made
Title: Enter a short name for the Analysis View. This will appear as a title to the View whenever it is run. No checks are made on the entry
Mail Merge: You have the option to print a document from within Ostendo where the source data can be merged with a Word document to produce printed forms containing data from both sources. For more information on how to achieve this refer to Help>Tutorials and select Mail Merge.
|
• |
Mail Merge Word Document: Enter a document name (Example: SuppLetter) that will become the Merge Template Document. |
|
• |
Merge Data File: Enter a file name (Example: SuppSource) that will become the Merge Source Data. |
Charts
Include in Main Menu: ‘Check’ this checkbox if the Chart is to appear in the ‘Views’ section in the Main Menu. If this is checked then an entry is required in field ‘Category’ to denote under which Main Menu Toolbar Category the Chart is to appear.
Name: A mandatory entry where you should specify the name of the Chart. This is the name that will appear in the Menu drop-down lists. Duplicate entries of the same name are not allowed.
Menu Order: Enter a sequence number that dictates where this Chart will appear in the drop-down list in both the Main Menu and the Specific Screen. No checks are made against this entry and duplicates will not be rejected.
Category: This is a mandatory selection in which you should select the Main Menu Category under which this Chart will appear.
Title: Enter a short name for the Chart. This will appear as a title to the Chart whenever it is run. No checks are made on the entry
Pivots
Include in Main Menu: ‘Check’ this checkbox if the Pivot View is to appear in the ‘Views’ section in the Main Menu. If this is checked then an entry is required in field ‘Category’ to denote under which Main Menu Toolbar Category the Pivot View is to appear.
Name: A mandatory entry where you should specify the name of the Pivot View. This is the name that will appear in the Menu drop-down lists. Duplicate entries of the same name are not allowed.
Menu Order: Enter a sequence number that dictates where this Pivot View will appear in the drop-down list in both the Main Menu and the Specific Screen. No checks are made against this entry and duplicates will not be rejected.
Category: This is a mandatory selection in which you should select the Main Menu Category under which this Pivot View will appear.
Title: Enter a short name for the Pivot View. This will appear as a title to the Pivot View whenever it is run. No checks are made on the entry
Inquiry
Include in Main Menu: ‘Check’ this checkbox if the Inquiry Screen is to appear in the ‘Inquiry’ section in the Main Menu. If this is checked then an entry is required in field ‘Category’ to denote under which Main Menu Toolbar Category the Inquiry is to appear.
Name: A mandatory entry where you should specify the name of the Inquiry. This is the name that will appear in the Menu drop-down lists. Duplicate entries of the same name are not allowed.
Menu Order: Enter a sequence number that dictates where this Inquiry will appear in the drop-down list in both the Main Menu and the Specific Screen. No checks are made against this entry and duplicates will not be rejected.
Category: This is a mandatory selection in which you should select the Main Menu Category under which this Inquiry will appear.
Title: Enter a short name for the Inquiry Screen. This will appear as a title to the Inquiry whenever it is run. No checks are made on the entry
Edit View
Name: A mandatory entry where you should specify the name of the Edit View. This is the name that will appear in Custom>Edit View section of Ostendo. Duplicate entries of the same name are not allowed.
Script Name: An optional field to specify the Script that will be used to control that data entry/edit function to the Edit View. Select the Script from the drop-down list of 'Edit View' Style scripts.
Style: Two Viewing Styles are available:
List Detail: displays records in a List panel in which you can select a line and go to a Detail panel to maintain the record
Detail Grid: Where all the records and their fields are displayed in a single grid. You may maintain multiple record within this single grid.
Title: Enter a short name for the Edit View. This will appear as a caption to the View. No checks are made on the entry
Section 2 - Master Query
This is a mandatory entry and allows you to define the main data extraction criteria using an SQL Query (Structured Query Language) Statement. SQL Statements can range from simple to complex.
To generate your Query you can either key you Query directly in to this field or click on the ‘SQL Builder’ Button. This will bring a separate panel in which you have help in creating the query. On the displayed screen there are three panels
Tables - The names of all the Tables (database files) that are used by Ostendo
Fields - Having selected a specific Table this will show each field in that Table
Query - This is where you create and maintain the query as described below
You can enter a query in one of two ways
|
▪ |
Type in directly |
|
▪ |
Create using ‘prompts’ |
To create using ‘prompts’ you can copy:-
|
▪ |
Tables to the Query by selecting the table and either double clicking the left mouse or clicking on the ‘Add Table to Query’ button |
|
▪ |
Fields to the Query by first selecting the correct Table then the field and either double clicking the left mouse or clicking on the ‘Add Field to Query’ button |
|
▪ |
Keywords to the Query by clicking the ‘Options’ button and selecting ‘Add Keyword’ and then selecting the appropriate keyword. Available Keywords are Select, *, Sum, Count, From, Where |
|
▪ |
Operators to the Query by clicking the ‘Options’ button and selecting ‘Add Operator’ and then selecting the appropriate Operator. Available Operators are >, <, =. <>, +, -, / |
You should note that an Inquiry Screen SQL requires the the main Table's SYSUNIQUEID field to be included
Section 3 - Conditions
This panel allows you to define selection criteria for use against that SQL. These will be displayed as parameter entry fields when running the Report, View, Chart, or Pivot.
There are six basic types of Conditions:
|
• |
Selection(s) from an Ostendo Table (Example: Job Order Number) |
|
• |
Manual entry against which to compare data (Example: Date, entered text, etc) |
|
• |
Condition(s) selected from pre-defined variables (Domains) |
|
• |
Boolean Condition select (‘Yes’ or ‘No’) |
|
• |
Multiple Select Options (Example: Select A,C and F from a list of A,B,C,D,E, and F) |
|
• |
Select from any Table in Ostendo including User-Defined Tables |
These conditions can be used as selection criteria to be made against both the Master Query and nominated Detail Queries. Examples are given below to show how you can apply conditions to the Master Query. For applying Conditions to Detail Queries see Section 4.
Condition 1 - Field Value Lookup
This Condition allows you to interrogate an Ostendo Table and select a specific value. The format of this type of parameter is DisplayName;Condition linked to a Condition Index where:
DisplayName is the text that you wish to appear on the parameter entry screen
Condition has three elements
Field Name: The specific field within the Table identified within the Master SQL and ‘Condition Index’
Operator: The specific Operator that you wish to apply to this parameter. The Available Operators are >, <, =, <>, >=, <=
Identifier: Enter a unique identifier that you are assigning to this parameter. For example: if the parameter is the start range of Job Numbers then this could be given an Identifier of ‘JobFrom’
Condition Index refers to the Table that contains the above Field Value. Click on the drop-down against field ‘Condition Indexes’ and select an Index in the 1000 range.
Example for use against the Master Query: If you are creating a Job Orders Report then the Master SQL will refer to the Table JOBHEADER. This parameter represents the start range of selected Job Numbers and will show - in a drop-down list - all Job Orders currently in Ostendo. In this instance the parameter could be:
Conditions: Job Order From;ORDERNUMBER>= :JOBFROM
Condition Index: 1075 (Job Orders)
Condition 2 - Free Format Entry
This format allows the user to enter a specific value against a parameter. The format of this type of parameter is DisplayName;Condition linked to a Condition Index where:
DisplayName is the text that you wish to appear on the parameter entry screen
Condition has three elements
Field Name: The specific field within the Table identified in the Master SQL
Operator: The specific Operator that you wish to apply to this parameter. The Available Operators are >, <, =, <>, >=, <=
Identifier: Enter a unique identifier that you are assigning to this parameter. For example: if the parameter is the start date of a range of Job Orders then this could be given an Identifier of ‘DateFrom’
Condition Index refers to the style of Free Format entry. The values are all within the 2000 range. For example:
2001 - Open format area to store Text entries
2002 - Date format
etc
Example: If you are creating a Parameter that represents the start date of selected Job Orders then the full line could be:
Conditions: Job Date From;ORDERDATE>=:DATEFROM
Condition Index: 2002 (Date)
Note: If you only require a single Condition with Operator linked to the manual entry then you can simply create a Parameter Line as follows
Conditions: Job Date;ORDERDATE>=:SCREENPARAM
Condition Index: 2002 (Date)
Condition 3 - Multiple Select Option
Ostendo contains many tables that are User maintained. For example: Job Types, Item Categories, etc. This parameter format allows you to nominate one of these tables from which you can make one or more selections from the drop-down list
The format of this type of parameter is DisplayName;Parameters linked to a Condition Index where:
DisplayName is the text that you wish to appear on the parameter entry screen
Parameters has five elements
First Parameter: The output fields in the Master SQL should contain a field whose content is user defined in a lookup Table. (Example: Field JOBTYPE in table JOBHEADER).
Second Parameter: Refers to the Look-Up Table Name equating the field name in the previous Table. (Example: Table JOBTYPES equates to the field JOBTYPE in Table JOBHEADER)
Third Parameter: This refers to the field name in the Look-up table whose data will form the drop-down list. (Example Field ORDERTYPE in Table JOBTYPES)
Fourth Parameter: The drop-down list will be sorted in the sequence defined by this field. Normally this will be the same as the Third Parameter but can be any other field in the ‘Look-Up’ table
Condition Index For Look-Up related parameters this is always 3000
Example: If you are creating a Parameter in which you want to select (say) the Order Type from the Job Type Table when selecting record from a Job Header then the full line could be:
Conditions: Job Type;JOBTYPE;JOBTYPES;ORDERTYPE;ORDERTYPE
Condition Index: 3000
Condition 4 - Pre-Defined Variables (Domain)
Within Ostendo there are many pre-defined variables (such as Job Status) and are referred to as ‘Domains’. This parameter format allows you to point to the ‘Domain’ tables and select the specific Table from which a selection or selections can be made. Note: During Parameter entry all entries in the nominated Domain Name Table are displayed in a drop-down list. You have the option to make one or more selections from that list
The format of this type of parameter is DisplayName;Condition linked to a Condition Index where:
DisplayName is the text that you wish to appear on the parameter entry screen
Condition has two elements
Identifier: Enter the field relating to the Domain Name For example: if the Domain is ‘ORDER_STATUS’ then this would be ‘ORDERSTATUS’
Domain Name: The specific Domain Name. There are around 550 Domain Names in Ostendo
Condition Index For Domain related parameters this is always 4000
Example: If you are creating a Parameter in which you want to select all Jobs of a Status then the full line could be:
Conditions: Job Status;ORDERSTATUS;ORDER_STATUS
Condition Index: 4000
Condition 5 – Defined Table Option
This Condition statement allows you to refer to any Table in Ostendo from which you can make a selection from a list of displayed records from that Table. This would commonly be used when selecting from User-Defined Tables
The format of this type of parameter is
DisplayName;ParamDef;TableName;Fields;Captions;ReturnedField;OrderBy where:
DisplayName is the text that you wish to appear on the parameter entry screen
ParamDef has three parts:
KeyField is the field name in the Query from which you making the comparison
Condition is the condition that you are applying to the parameter
TheStoredValue is a unique reference that you give this selected parameter
TableName is the name in the Table containing your selection
Fields are the Field Names from the selected Table that you wish to be displayed
Captions are the names that equate to the Fields and will be displayed in the extracted column headings
ReturnedField is the field in the selected Table from which data will be returned and populate the above ‘DisplayName’ Variable
OrderBy is the sort order by which the selected records will be displayed
Condition Index For this style of Parameter entry this is always 5000
Example: If you are creating a Parameter in which you want to select a Drawing Number from a list of Drawings created in your own User-Defined Table then the full line would be:
Conditions:Drawing_Number;DrawingNumber=:DrawingNumber;OSTDEF_DRAWINGREGISTER;DrawingNumber;Drawing;DrawingNumber;DrawingNumber
Condition Index: 5000
Condition 6 – User-Defined Selection Option
This Condition statement allows you to create user-defined values that will appear in a drop-down list when run. This is useful when used in conjunction with a User-Defined Table where the variables are specified in the Edit View script. It could also be used to restrict selections made against base Ostendo Tables
The format of this type of parameter is DisplayName;Condition;Selections where:
DisplayName is the text that you wish to appear on the parameter entry screen
Condition has three elements
Field Name: The specific field within the Table identified within the Main Query
Operator: The specific Operator that you wish to apply to this parameter. The Available Operators are >, <, =, <>, >=, <=
Identifier: Enter a unique identifier that you are assigning to this parameter.
Selections Each possible selection is entered separated by a comma. Note: If the selection contains a space in its identity (Example - In Progress) then it must be surrounded by double quote ("In Progress")
Condition Index This is always 6000
Example: If you have a User-defined Edit View that contains the following options (Planned, In Progress, Complete) against field Status then this condition would allow you to select the specific Status for output. Therefore the condition would be::
Conditions:Select Status:Status=:Status;Planned,"In Process",Complete
Condition Index: 6000
Condition 7 - User-Defined
User-Defined entries allow you to specify a Parameter and its format. Upon entry of the parameter it is stored in the 'Variables' of the Report and can be used to process activities within the report
The format of this type of parameter is DisplayName;{Format} linked to a Condition Index where:
DisplayName is the text that you wish to appear on the parameter entry screen
{Format} This is the format of this entry {contained in ‘squiggly’ brackets}. Data entry is validated against this format although the value itself is stored as a String in the database. The validation options are:
|
• |
Boolean |
|
• |
Integer |
|
• |
String |
|
• |
Date |
|
• |
Time |
|
• |
Double |
Condition Index This is always 9000
Example: If you are creating a Parameter in which you provide the option to print the Run Parameters then the full line could be:
Conditions: Exclude Parameters;{Boolean}
Condition Index: 9000
This is linked to an Exclude Parameters Variable in the Report Layout which you can then use this to determine if the parameters are to be printed or not.
Section 4 - Detail Query Conditions
This uses the 'Conditions' panel in the Master Query Screen and its similar to the Master Query Conditions except that you need to specify the Detail Data Query to which the Condition applies
This references the Detail Data Query Number along with the field within that query. For example if we wish to show a range of Job Orders held against a Range of Customers then the Master Query would refer to the Customer data and Detail Data Query would refer to the linked Job Orders.
To select the Range of Customers the following Conditions would apply.
From Customer;CUSTOMER>=:CUSTFROM (Type) 1015
To Customer;CUSTOMER<=:CUSTTO (Type) 1015
To select the Range of Job Orders the following Conditions would apply.
Job Order From;ORDERNUMBER>= :DDC_1_JOBFROM (Type) 1075
Job Order To;ORDERNUMBER<= :DDC_1_JOBTO (Type) 1075
Where DDC_1 represents the Detail Query Number to which the condition will apply
Additionally you may have additional Detail Queries that also link to the selected Job Order Number. For Example Job Order Lines (DD_2), Job Order Documents (DD_3), etc. In this instance you would simply extend the above condition to include the Detail Query Number - separated by a #
Job Order From;ORDERNUMBER>= :DDC_1#2#3_JOBFROM (Type) 1075
Job Order To;ORDERNUMBER<= :DDC_1#2#3_JOBTO (Type) 1075
Buttons
The following buttons are used to save the current Report, View, Chart, Pivot, or Edit View in addition to processing the respective layouts
Close: This will close the Reports and Views Developer screen.
Add: This will bring up a panel for defining the new Report, Analysis, Chart, or Pivot View. You can create a new report ‘from scratch’ or copy an existing format. In either case it will create an output whose ‘Level’ will be preset at ‘Company’. On this panel the following fields are available.
Create from Scratch: Select this Radio Button if you are going to generate the Report, Inquiry Screen, Analysis, Chart, or Pivot View from scratch. The following two fields will be presented which must be completed
Type: Select the Radio Button that represents the type of view being created. The options are:
Report
Analysis
Chart
Pivot View
Inquiry
Edit View
Name: Enter the Name of the view. Duplicate Names are not allowed
Copy from Another: Select this Radio Button if you are going to generate the Report, Inquiry Screen, Analysis, Chart, or Pivot View by copying an existing view and using that as a start point. The following options are now presented
Output to be copied: From the drop-down list select the current Report, Inquiry, Analysis, Chart, or Pivot View that will form the basis of the new view. When selecting this option you can define what will happen to the copied view as follows:
Replace the Existing: Select this Radio Button to denote that the ‘Output to be Copied’ document will be replaced after changes have been made
Be an Additional: Select this Radio Button to denote that the ‘Output to be Copied’ document will be used as the basis for creating a new view. The name of the new view should be entered into the next field
Name: Mandatory enter if the ‘Be an Additional’ Radio Button ahs been selected. Duplicate Names are not allowed
Save: This will save the current data without exiting the screen
Cancel: Any changes made to the current record or the last time the ‘Save’ Button was pressed will be lost.
Delete: Not applicable to this View
Edit: This is only available in the 'Master Settings' tab and only applies to reports. If you click on this button then you will be taken through the parameters currently held against this Report so that you can create and/or maintain the physical document. If you are creating the Report then a panel will be presented where you can either use a ‘Wizard’ to create the basic report or select an existing Report to use as a start point.
Note 1: A guide to the Report Design Wizard is shown at the end of this section
Note 2: A simple Tutorial is available under Ostendo ‘Help’ that describes some of the basic functions such as:
|
• |
Adding Data Fields |
|
• |
Adding Text Fields |
|
• |
Adding your Company Logo |
|
• |
etc |
Preview: This will take you through the parameters currently held against this Report or View and then produce the output as if you were an end user.
Export: If you click on this button then the current Report, View, Pivot, or Chart’s details will be exported and generated as a .dat file. You should note that, in the case of a Report, this does NOT export the .fr3 file. This must be copied separately if you are transferring the Report to another Ostendo.
Import: If you click on this button then you will be asked to point to the .dat file generated using the above ‘Export’ process. Upon selecting the .dat file the Report, View, Pivot, or Chart’s details will immediately be imported into the current (sign-on) Company. You should note that, in the case of a Report, you should also copy the specific .fr3 file into the ‘Company’ reports folder.
Detail Query Tab
This tab allows you to create up to:
|
• |
20 additional sub queries for reports |
|
• |
1 additional sub query for Analysis Views |
|
• |
1 additional sub query for Edit Views |
|
• |
12 additional sub queries for Inquiries |
Each Sub Query contains the following fields
Query #: This allows you to define a sub-query linked to the Main, or a higher level, SQL (see the next field).
Link to Query #: This enables you to define the ‘Level’ that the Query occupies and therefore to which parent Query it is joined. For example:
Any Query at Level 0 will be Left Joined to the Master SQL
Any Query with Level 1 will be Left Joined to the immediate previous Level 0
Any Query with Level 2 will be Left Joined to the immediate previous Level 1
Etc
SQL Builder: Supporting screen to help you design your query. On the displayed screen there are three panels
Tables - The names of all the Tables (database files) that are used by Ostendo
Fields - Having selected a specific Table this will show each field in that Table
Query - This is where you create and maintain the query as described below
You can enter a query in one of two ways
|
▪ |
Type in directly |
|
▪ |
Create using ‘prompts’ |
To create using ‘prompts’ you can copy:-
|
▪ |
Tables to the Query by selecting the table and either double clicking the left mouse or clicking on the ‘Add Table to Query’ button |
|
▪ |
Fields to the Query by first selecting the correct Table then the field and either double clicking the left mouse or clicking on the ‘Add Field to Query’ button |
|
▪ |
Keywords to the Query by clicking the ‘Options’ button and selecting ‘Add Keyword’ and then selecting the appropriate keyword. Available Keywords are Select, *, Sum, Count, From, Where |
|
▪ |
Operators to the Query by clicking the ‘Options’ button and selecting ‘Add Operator’ and then selecting the appropriate Operator. Available Operators are >, <, =. <>, +, -, / |
The following buttons are used to save the current Report, View, Chart, Pivot, or Edit View in addition to processing to the respective layouts
Buttons
The following buttons are used to save the current Report, View, Chart, or Pivot in addition to processing to the respective layouts
Close: This will close the Reports and Views Developer screen.
Add: This will bring up a panel for defining the new Report, Analysis, Chart, or Pivot View. You can create a new report ‘from scratch’ or copy an existing format. In either case it will create an output whose ‘Level’ will be preset at ‘Company’. On this panel the following fields are available.
Create from Scratch: Select this Radio Button if you are going to generate the Report, Inquiry Screen, Analysis, Chart, or Pivot View from scratch. The following two fields will be presented which must be completed
Type: Select the Radio Button that represents the type of view being created. The options are:
Report
Analysis
Chart
Pivot View
Inquiry
Edit View
Name: Enter the Name of the view. Duplicate Names are not allowed
Copy from Another: Select this Radio Button if you are going to generate the Report, Inquiry Screen, Analysis, Chart, or Pivot View by copying an existing view and using that as a start point. The following options are now presented
Output to be copied: From the drop-down list select the current Report, Inquiry Screen, Analysis, Chart, or Pivot View that will form the basis of the new view. When selecting this option you can define what will happen to the copied view as follows:
Replace the Existing: Select this Radio Button to denote that the ‘Output to be Copied’ document will be replaced after changes have been made
Be an Additional: Select this Radio Button to denote that the ‘Output to be Copied’ document will be used as the basis for creating a new view. The name of the new view should be entered into the next field
Name: Mandatory enter if the ‘Be an Additional’ Radio Button has been selected. Duplicate Names are not allowed
Save: This will save the current data without exiting the screen
Cancel: Any changes made to the current record or the last time the ‘Save’ Button was pressed will be lost.
Delete: Not applicable to this View
Report Creation Wizard
Whenever a Report is being created and the ‘Edit’ button is pressed a panel is presented to help you define a starting point for the new Report. Three options are available:
Option 1. You can use an existing Report’s Format as the basis for the new Report by clicking on the ‘Templates’ tab and selecting the specific Report.
Option 2. You can construct a Report ‘from scratch’ by using a ‘Wizard’. This contains the following steps
Step 1 - Data: This panel allows you to identify the dataset(s) from which the fields will be extracted. The basic datasets shown are:
SYS_ This contains all the fields relating to your Company
SC_ This shows the parameters that have been identified against this Report
MD_ This shows the fields extracted via your Master Query (above)
DD_# This shows the Detail-queries that were created against the Report. The # after the DD_ identifies the Detail-query number in the Detail Query List
Select the dataset then click on the ‘Next’ button
Step 2 - Fields: This panel shows the available fields within the dataset selected above. Select the field(s) that you want to appear on the Report and move them to the right side of the screen. Click on the ‘Next’ Button when done.
Step 3 - Groups: This is an optional step that allows you to Group the extracted records. You should note that the Query used to extract this data should also include an ‘ORDER BY’ statement to reflect the grouping being defined here. The left side of the screen shows the extracted fields defined in step 2. Select the ‘Group By’ field(s) and move to the right-hand area. Click on the ‘Next’ Button when done.
Step 4 - Layout: Select the following:
Orientation:- Landscape or Portrait
Layout:- Tabular or Columnar (The display to the right of the panel will reflect the selection)
Fields Fit to Width:- If ‘checked’ then the selected fields will be restricted to the width of the page and field concatenation could occur. If not ‘checked’ then the field will continue into a second (and third line) if necessary to show all the data.
Click on the ‘Next’ Button when done.
Step 5 - Style: Select the Report Style that you wish to use. The options are Bold, Standard, Soft Gray, or Corporate. The picture to the right of the panel shows an example of the report style. Click on the ‘Finish’ Button when done.
Option 3. You can construct a Report ‘from scratch’ by starting with a blank ‘palette’ by selecting on the ‘Standard Report’ icon and clicking on the ‘OK’ button. A blank report will be presented. You now need to make the Datasets and their available for inclusion in the report. To do this you should select Reports on the top toolbar and choose ‘Data’ from the dropdown list. ‘Check’ those datasets from which you wish to select fields and click the ‘OK’ button.