Tech Talk 3 By BrainStorm, Inc.
Reporting for Duty
Creating Static and Dynamic Reports in OpenOffice.org Base
May 2008
Features
- Tech Talk 1 Want Facebook for Business? You now have it with Novell Teaming. It lets you do a lot more than just set up an online environment where your team can share files. It's a host of other things to help teams collaborate across the globe.
- Tech Talk 2 Feeling a little behind on the Linux front? Need to get your feet wet in Linux and on the command line? Don't worry. Novell just released two new ways that will help you start-and enjoy-your journey into Linux.
- Tech Talk 3 Getting to your data is easier than you think in OpenOffice.org Base. Just set up some static or dynamic reports to pull all the data you need to make sound business decisions.
Departments
- Proof Point With an expanding data center and frequent system outages, Baldor Electric migrated to SUSE Linux Enterprise Server for System z to establish a more streamlined, highly available platform for its mission-critical SAP applications.
- Laura Chappell Laura's giddy with the new release of Pilot from Cace Technologies. It's what she's wanted for years to augment her use of Wireshark. It could make your life a lot easier too! Watch this video to find out.
Creating a Dynamic Report
Dynamic reports are valuable because they are automatically updated every time the report is run. If I want to create a report that lists all the inventory in my company, sorted by department, I can make one report and simply run it every quarter to get an updated list of what equipment is in each department. In our example, this report would require using information from two tables the Basic Employees Table and the Inventory Details Table. Because reports can be based on only one table or query, the first step here would be to create a query that incorporates that data. The previous article in this series described exactly how to generate such a query. I named the query Inventory by Department. To create a dynamic report using this query, follow these steps:
- Click the Reports icon in the left pane.
- Click Use Wizard to Create Report.
- Choose the Inventory by Department query (which combines data from the tables mentioned above) from the Tables or Queries drop-down list. Alternately, you can right click the Inventory by Department query in the Queries section of Base and select Create Report. When you do this, the query will automatically be selected in the list.
- Move all of the fields from the Available Fields list to the Fields in report list. Click Next.
- Make changes to labels as desired. Click Next.
- Move the Department field from the Fields list to the Groupings list. This will set up the report so each piece of inventory is listed under the department in which it is being used. Click Next.
- Choose a layout template's or simply use the default for your report. Click Next.
- Give the report a title. I named this report Inventory by Department, just like the query. Specify that this is a Dynamic report and choose to either modify of create the report. I recommend modifying the report right away. This will give you the chance to adjust the size of each column, correct the date, enter the title using the font and size you desire, format columns with numbers if they need to be changed to currency, and so on. (See Modifying a Report for more details.)
- Click Finish.
- Make modifications as desired. When you are ready to distribute the report, open it (or save the modifications), click the Export to PDF button, name and save the file, then distribute it.
- Close the report and save the database.
- When you want to distribute an updated version of the report, open the original report file in your database, click the Export to PDF button, name the file "specifying that this is an update" save it, and distribute it as desired. (Figures 4) and (Figures 5) show the same report in PDF format. The report in Figure 4 was run first. Later, the IT director realized that one item of inventory hadn't been included. He had his intern enter the data about this "undiscovered" laptop in the Employee's Inventory Tracker form. After the intern was finished and had saved the database, the IT director ran the same report again, and it automatically included the new data, as shown in the PDF in Figure 5.
Summing It Up
As you can see, generating a report is easy in Base. It's also great that once you've gathered and entered all your data into tables and forms, all you'll ever really need to do again is to create a few queries and run a few reports.
Over time, you'll find that the data you worked so hard to collect and organize is even more valuable than you thought "and more powerful" because you now have the tools to put the data to work and demonstrate just what the numbers in your company are doing. So take Base for a spin and see how it can help you.
Modifying a Report
As suggested in step 8 in Creating a Dynamic Report, it's best to modify the report right away. Some of the most common modifications you'll want to make are explained below:
Changing the Date
The date that appears on a report is a placeholder date. To fix this so the date is current and updated when a dynamic report is run again, place your cursor to the right of the date and use the backspace key to delete it. Then click Insert > Fields > Date. The current date will appear.
If you are creating a dynamic report you will also need to do the following:
- Double click the date field.
- In the Select field, choose Date. For static reports you would use Date (fixed). (See Figure 6.)
- Choose the format you like best, then click OK.
Increasing or Decreasing Column Widths
All columns in a report have the same default width. If one column needs to be adjusted you can increase or decrease the width of the column by simply placing your cursor at the edge of the column, clicking it and dragging it to the desired width.
Formatting Numbers in a Column
By default, all numbers "even those that should be formatted as currency" are displayed only as numbers. To change the formating of a column, such as the one for Purchase Price in our report:
- Place your cursor in the column you need to change and right click.
- Select Number Format.
- Select Currency from the Category field, choose the format you like best in the Format field, set the options for decimals and leading zeros, if desired.
- Click OK.
- 01
- 02
Figure 1
In the Report Wizard you can choose a sorting order for the fields in your report. If you grouped any fields in your report, that field will automatically be the first element by which the report is sorted. Our sample report will be grouped and sorted by department and then sorted by the last and first names of each employee in each department.
Figure 2
As you use the Report Wizard, your report with dummy numbers will display behind the Wizard window. You can select from a variety of templates and see how they will look in the report.
Figure 3
Once finished with the Report Wizard, your simple, static report will look great and professional. Export it to a PDF, and its ready to be distributed.
Figure 4
This is a PDF of one section in the Inventory by Department report. This dynamic report was run, saved as a PDF, and distributed. Later, the IT director noticed that one piece of inventory was missing from the list. He updated the database and ran the report again. He then saved the updated report as a PDF and redistributed it. The automatic update can be seen in Figure 5. Every time the database is updated, dynamic reports are also updated.
Figure 5
This is a PDF of the same report shown in Figure 4, but it contains information that has been added to the database since the first report was run. The new data is circled.
Figure 6
For dynamic reports, you simply want to select Date, not Date(fixed) when formatting the date on your report. This allows Base to automatically update the report with the current date every time it is run.
