Tech Talk 3 By BrainStorm, Inc.
Reporting for Duty
Creating Static and Dynamic Reports in OpenOffice.org Base
- 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.
- 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.
If you've been following this series, you're quickly becoming a database pro. You've learned the basics of just about everything Base offers. The last step is to throw all that data into a usable report that can be printed and used. This article will show you how to do just that.
Before we get started, let's review our scenario: You're the IT director for a company with several employees, and you have a lot of hardware to monitor. You're building an inventory database to help manage and track the printers, laptops, desktops, scanners, fax machines and other important equipment floating around the office. You've created at least one great form in which an intern or secretary can enter employee names and the products each employee is using, as well as all the pertinent data about those products. You've run a few queries—some simple, others more complex—and now you're ready to distribute the data in a report.
Static and Dynamic Reports
First decide what kind of report you want: static or dynamic. A static report uses—and will always use—the data you have right now; the data used will not change over time. For example, static reports are good for printing out a list of employees with their phone extensions and the departments in which they work.
A dynamic report accesses the original data source (a query or table) and updates the data each time you run the report. This kind of report is helpful in generating a list of inventory purchases, for example. You could run the report quarterly so new purchases are added to the list before distributing the updates. You could also use a dynamic report to print out a quarterly cost analysis: each quarter you would use the Dollars Spent by Employees query to run a report. Save each newly generated report, and at the end of the year you could see just how much was spent each quarter in each department. You could even create a new query that spits out the total dollar amount spent in all departments and run a quarterly report based on that.
Creating a Simple Report with the Wizard
For our purposes today, we'll start with a simple static report. First, note that a report can be based on only one table or query. If your report needs to include data from two or more different tables, first create a query to combine the fields, then use that query as the basis of your report. This means you might find yourself creating new queries or combining old queries to get the right information for your report. Here's how you'd create your first simple report:
- Open the database.
- Click the Reports icon in the left column.
- Choose Use Wizard to Create Report. A generic report will open in the background, with the Report Wizard window displaying on top.
- Our report is going to list the names, departments and phone extensions of each employee. We'll select the Basic Employee Details table from the Tables or Queries drop-down field.
- Select each field in the Available fields list that you would like to include in the report and use the arrow to move them to the Fields in Report list, then click Next.
- Here, you can rename the fields so the names are more specific or to add a space between the words in the label. Or you can simply leave the fields as they are. I usually make a few changes, though, so the generated report better serves the report readers. After changing the labels, click Next. Note that only the labels are changed, the field names in the original table or query remain the same.
- In this step, you can have Base group certain elements of your report. For example, it might be helpful to have the employee list grouped by departments. This way, I can quickly see all the employees in each department at a glance. In the Fields list, select Department and click the arrow button to move the field to the Groupings list. Click Next.
- Here, define how the report is sorted. Note the first Sort by field is automatically filled in with the Department field because that is how the report will be grouped. I want to sort the data within each grouping by Last Name, then First Name, so I select those options from the next two sorting fields. (See Figure 1.) Click Next.
- One nice feature is that you can move the Report Wizard window around on the screen to see how the report beneath it is taking shape. Don't worry if the numbers displayed on the report are all messed up at this point; they are simply a visual representation of how your report will appear. It will display your data accurately after you run the report. In this step, you can choose a template for your form. I usually move the Wizard over a little so I can see how the report looks in the background as I decide which template to use. For the most part, simple is best. For our sample, I chose to use borders in my data layout, to keep the default settings for headers and footers, and to change the report's orientation from landscape to portrait. (See Figure 2.) Click Next when finished.
- Name the report and indicate whether it is Static or Dynamic. If you choose Static, which this report is, you won’t have any more options. If you choose Dynamic, you can decide to immediately create the report or to modify it once finishing. Click Finish.
- Your report will be displayed on the screen. (See Figure 3.) Save it.
- Now, simply click the Export Directly as PDF button in the menu bar, save the PDF, and distribute as desired.
- Close the report, then save the entire database before closing it.