Database tutorial on free app openoffice.org. Creating a Database using Open Office Base - Duration: 9:48. IISFCTUNL2013 26,679 views. This page is an index of the Base (database) tutorials for OpenOffice. Enhance Apache OpenOffice. Get OpenOffice Search templates. Search templates; Highest rated. Accessing ODBC Databases from Apache Open. Office, Libre. Office and Open. Office. org. The Apache Open. Office productivity suite was previously known as Oracle Open Office. Open. Office. org. Libre. Office was initially derived from the Open. Office. org source. You can access databases. Microsoft SQL Server, Microsoft Access, Oracle, Salesforce. DB2, Derby, Inter. Base and. Sybase ASE from Base and Calc even when the database is on a different. As both Apache Open. Office. and Libre.
ODBC access from Apache OpenOffice, LibreOffice and OpenOffice.org to databases such as Microsoft SQL Server, Oracle, Salesforce.com, MongoDB, DB2, InterBase and. Office are built with threads, make sure that you specify the. OOB client. driver (. If. unix. ODBC is not installed already, install the version that comes. Easysoft ODBC driver distribution. Open. Office. org is. ODBC yourself, use. For. earlier OOB. Otherwise, you will have to edit the unix. ODBC odbc. ini. file to add data sources. For information about adding an. OOB data. source, see DSN. For information about adding a data source for. Easysoft ODBC driver, see the. Easysoft ODBC driver. The ODBC data source selection is not available. You can enter the name of a SYSTEM. USER data source (defined for the user who is running Base). To find out. the name of the data source look at the relevant . If this user needs to. Password required check box. From here. you can access your data. Type the database password. OK. If your database requires a database. User name box. If this user needs to. Password required check box. Type the. database password if prompted, and then click OK. In this case, the data. As a result. odbcinst is in /usr/bin/odbcinst and libodbc. All. the OOB. installation needs is odbcinst and libodbcinst. OOB driver. into unix. ODBC. Once. OOB is. ODBC you can delete. ODBC. This will use your existing. OOB (no net. difference to 1). When asked if you want to install. OOB as an. ODBC driver under unix. ODBC, answer Yes. This example shows how to connect Calc to ODBC databases such as Mssql, Oracle, Inter. Base and Sybase. Calc is the Open. Office spreadsheet program. In Calc, choose View > Data Sources or press the F4 key. You can manipulate the rows and columns in a Data. Pilot table to view or summarise the data in different ways for the purposes of analysis. Data. Pilots also allow you to apply spreadsheet functions to the data. This example shows how to use an external ODBC data source as the source for a Data. Pilot table. Import the ODBC data that you want to analyse with the Data. Pilot table. In Calc, select the data you want to base the Data. Pilot table on. The dialog box shows you a diagram of the Data. Pilot that you are creating. The table column headings are displayed as dialog box buttons. To create the Data. Pilot, you drag these buttons to the dialog box’s layout areas: Column, Row and Data. Drag the required fields to one of the three areas. If you drag a column button to the Data area, it becomes the data in the Data. Pilot. This example Data. Pilot shown in the screen shot uses the Northwind Orders table as its source data. In the example, Ship. Country is the column, Employee. ID the row, and Order. Date the column. Double- click the column button in the Data Fields area. The example Data. Pilot uses the Count function to count the number of orders each Northwind sales person took in each country. Click OK to exit the Data. Pilot configuration dialog boxes. The example Data. Pilot makes it easy to see how many orders each employee took, broken down by country. To change how the Data. Pilot displays the information, you can drag the columns to new positions. For example, the following Data. Pilot was produced by dragging the Ship. Country column below the Employee. ID column. If you no longer need the Data. Pilot, choose Data > Data. Pilot > Delete. Using a Base Query as Data. Pilot Source Data. Calc lets you use a database query created in Base as the source data for a Data. Pilot. You can use the query to selectively retrieve the data you want before importing it into Calc. This section shows how to use a Base query and a Calc Data. Pilot to create a sales report from external ODBC data that shows how well sales representatives are doing and which products are selling the most. To follow the examples in this section, create a SQL Server ODBC driver or OOB data source that connects to a SQL Server instance that serves the Northwind database. Alternatively, create an OOB data source that points to an Microsoft Access ODBC data source for the Northwind database. Creating a Base Query from ODBC Data. To find out how much each sales representative has sold, create a query that retrieves the: Name of each sales representative. Name of each product. Amount sold. Dates of the orders. Create an Open. Office database document for an ODBC data source that connects to the Northwind database. In the Database pane, click Queries. In the Tasks Pane, click Create Query in Design View. The following example shows how to compare the amount of products sold by each sales representative. In a Calc spreadsheet, choose Data > Data. Pilot > Start. For example, you can change the layout of the Sales Results Data. Pilot products are displayed across the columns, and sales representatives are listed down each row. In the Calc spreadsheet, click any cell in the Data. Pilot table. Choose Data > Data. Pilot > Start. Switch the positions of the Last. Name and Product. Name fields. Click OK. The Data. Pilot table displays the data in the new layout. Data. Pilots and Datetime Fields. At the time of testing (using Open. Office. org 2. 3. Data. Pilot table. In the Data. Pilot table, cells containing Datetime fields are marked as . See instance 5. 32. Alternatively, create an OOB data source that points to an Microsoft Access ODBC data source for the Northwind database. Using Calculated Fields in a Base Query. Calculated fields derive their values from data that exists elsewhere in the database. The example query in this section uses a calculated field to work out the total value of each order in the Northwind Orders table. The calculated field does this by multiplying the Quantity column value by the Unit. Price column value. The calculation produces a value in the query result set that is derived from but not stored in the database table. Create an Open. Office database document for an ODBC data source that connects to the Northwind database. In the Database pane, click Queries. In the Tasks pane, click Create Query in Design View. In the Tables list, double- click Employees, Orders and Order Details, and then click Close. In the upper pane of the Query Design window, double- click. Last. Name and First. Name in the Employees table. Order. Date in the Orders table. In the lower pane of the Query Design window, choose Order Details in the first available Table cell. In the first available Field cell, type this formula. Quantity * Unit. Price. The formula calculates the total value of each order. In the Alias cell below this formula, type . By default, Open. Office displays the formula value (. For example, instead of retrieving all orders received, you can create a criterion field that retrieves orders received in a particular month. This section shows how to use a criterion field with an external ODBC data source to filter Northwind orders by month. Open the Open. Office document where you saved the Sales Performance query. In the Database pane, click Queries. In the Queries Pane, click the Sales Results query. Choose Edit > Edit. You many need to change the Order. Date column format. To do this, right- click the Order. Date column heading and choose Column Format. In the format code box, type: DD/MM/YYChoose File > Save As and save the query as Monthly Sales Performance. To rank the employees, the query sorts the records by order amount and displays the records in descending order. The larger the order amount, the earlier the employee displays in the query results. Open the Monthly Sales Performance query in Design View. In the First. Name column, choose Group in the Function field. Repeat the previous step for the Last. Name column. In the Order. Date column, click to clear the Visible field. In the Total Order Value column, choose Sum in the Function field. The example form is based around the Northwind Products table. Open an Open. Office database document for an ODBC data source that connects to the Northwind database. In the Database pane, click Forms. In the Tasks pane, click Use Wizard to Create Form. Click Next. Click Next to move to the Arrange controls page. In the Arrangement of main form section, click Columnar - Label Left. Click Next to move to the Apply styles page. Choose the form layout format you want, and then click Next. In the Name of the form box, type . To enable users to choose from a predefined list of supplier IDs and category IDs when entering data, change the form controls for these fields to list boxes that retrieve the available IDs. Hold down the CTRL key, and click the Supplier. ID text box to select the control. Right- click the control, and then choose Replace With > List Box from the shortcut menu. Double- click the control to display the List Box properties dialog box. In the General tab, set the Drop down property to Yes. In the Data tab, set the Type of list contents property to . In the List content box, type. SELECT. Change the SQL for the list box’s source data to. SELECT. For example, to prevent negative numbers from being entered in the Units. In. Stock field, in the General Properties for this field, type . For example, if the unit price for a product is never above 1. To do this in the example form, hold down the CTRL key, select, and then double- click the Unit. Price box. In the Value maximum box, type . Note that although this method prevents values that are outside the range from being entered, Open. Office does not prompt for a valid value. Instead, Open. Office sets the value to the maximum permitted by the range. In the example form, if a value of 1. Unit. Price field, Open. Office sets the value to 1. You can configure the form to automatically enter field values when user’s add new records.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2016
Categories |