Data Spreadsheet

This feature shows a basic spreadsheet into which you can (a) create your own spreadsheet from data held within Ostendo and (b) display existing .xls files.   Upon first entering this screen it will show a standard Spreadsheet layout.  This guide will take you through the process of accessing your Ostendo Database and extracting information for viewing in the spreadsheet.

 

To extract information you first need to create a Query.  A Query is a sequence of instructions that tells the computer what to extract from the database.    

 

Select Data>Query Builder to commence creating your query.   On the displayed screen there are four 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

Selection Criteria - Enables you to define parameters when running this query

 

Tables: This lists the current tables in Ostendo.   

 

Fields: This lists the current fields within the selected table.   

 

Query: Enter your Query statement.   You can enter a query in one of three ways

▪

Type in directly

▪

Select from the list by clicking on the ‘Query List’ Button

▪

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 >, <, =. <>, +, -, /

 

Selection Criteria: A parameter gives you the option to insert selection criteria whenever the Query is run. For example: if you want to look at a range of Customers then the parameters would be:

From Customer:

To Customer:

 

To create a parameter you should first:

•

Highlight the Table in the ‘Table’ panel

•

Create the Query in the ‘Query’ panel

 

An then click the ‘Add’ button at the bottom of the screen.  This will add a new line in the ‘Selection Criteria’ Panel.  Enter the following information

 

Display Name: This is the name that will be displayed during ‘Run’ for entry of the parameter

 

Field Name: From the drop-down list select the field against which the parameter entry will be made.

 

Operator: From the drop-down list select the operand that will apply to the parameter.  The available operands are:

=        (Equal To)

>        (Greater Than)

<        (Less Than)

>=        (Greater than or equal)

<=        (Less than or equal)

<>        (Not equal to)

 

Look Up Table: If the selection criteria is a Customer, Supplier, Item, or Descriptor then select this from the drop-down list.  The relevant master data will be available for selecting a record when running the Query.

 

Buttons

 

Run: This runs the query and outputs the information to the spreadsheet

 

Cancel: Closes this form and goes back to the spreadsheet view

 

Options: Click on this button to add a Keyword or Operator and also to create a runtime Parameter

 

List: Click on this button to list current saved queries for selection onto this screen

 

Save Query: If you wish to retain the Query for future use then you can save the query.   A screen where you have the option to create a new saved query or overwrite an existing query

 

Delete: This will bring up your saved queries from which you can select and delete

 

 

Simple Introduction to Queries

 

With the Data Spreadsheet you can extract information and output to an Excel Spreadsheet.  Here are simple instructions to creating your own extract file.

 

The first thing to note is the structure of the database holding your data.  To view the structure select Data>Query Builder. You upper-left panel shows the tables in Ostendo.  Clicking on one of these will show you the individual fields within a table in the panel below. Now we can construct a ‘Query’.

 

The first command is ‘select’.  With this command you are telling the computer which fields to select.  However the computer doesn’t know from which table you are selecting therefore the command should be extended to tell it from which table to extract the field.  As an example let us extract all fields from the Inventory Master (Inventory) table.  The instruction would be

 

Select * from Inventory

 

If you type this in the right hand panel and hit ‘Run’ button the full data from the Inventory table will be output to a spreadsheet.  (Hint – when you get to the ‘Inventory’ table in the left-hand panel you can double click on it and it will automatically copy to the right)

 

You may wish to extract only selected fields from the Inventory table.  This is simply done by specifying the required field in the ‘select’ statement.  For example:

 

Select ItemCode, WarehouseCode, LocationCode from Inventory

 

Note:  Each selected field is separated by a comma.

 

(Hint – Once again you can double click on the field on the left side of the screen and it will automatically copy to the right)