View source for EN/documentation/ooo3 user guides/getting started/creating reports

<section begin=title /><noinclude>{{DISPLAYTITLE:</noinclude>Creating reports<noinclude>}}</noinclude><section end=title />

{{Documentation/GS3BaseTOC
|ShowPrevNext=block
|PrevPage=EN/documentation/ooo3_user_guides/getting_started/creating_queries_and_reports
|NextPage=EN/documentation/ooo3_user_guides/getting_started/getting_started_with_math
}}__NOTOC__
Reports provide information found in the database in a useful way. In this they are similar to queries. Reports are generated from the database's tables or queries. They can contain all of the fields of the table or query or just a selected group of fields. Reports can be static or dynamic. Static reports contain the data in the selected fields at the time the report was created. Dynamic reports can be updated to show the latest data.

{{Documentation/Caution|Dynamic reports update only the data that is changed or added to a table or query. It does not show any modifications made to a table or query. For example, open the fuel economy query you just created. For the "End-Reading".”Odometer – Fuel.”Odometer” column, change the number 1 to the number 3 after creating the report below. The report will be identical before and after you make the change.}}

For example, a report on vacation expenses divided into categories should probably be a static report because it is based upon specific data that does not change. However, a report on the fuel data should probably be a dynamic report, because this report depends upon data that does change.

{{Documentation/Caution|All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables, you must first combine these fields in a single query. Then you can create a report on this query.}}

An example of this caution is creating a report on vacation expenses. Fuel costs are one part of that report as are meal costs. These values are contained in fields of two different tables: Vacations and Fuel. So this report requires creating a query.

[[#top|Top of page]]

==Creating a static report==

We will create a report on vacation expenses. Certain questions need to be asked before creating the report.

* What information do we want in the report?
* How do we want the information arraigned? 
* What fields are required to provide this information?
* Will a query have to be created because these fields are in different tables?
* Are there any calculations required in the data before being added to the report?

The expenses for our vacation are motel, tolls, miscellaneous, breakfast, lunch, supper, snacks, and fuel. One possible report would simply list the totals of each of these expense groups. Another possible report would list the expense totals for each day of the vacation. A third possible report would list the totals for each expense group for each type of payment. (This would let us know where the money came from to pay the expenses.) At the present time, using the data from the queries in a spreadsheet is the best way to handle reports like this. In the near future, the report feature will include these abilities.

For our purposes, we will create two reports. The first one will list the expenses each day other than fuel. The second report will list the fuel costs each day. 

The fields we will need for the first report from the Vacations table are: Date, Motel, Toll, Breakfast, Lunch, Supper, SnackCost, and Miscellaneous. This report will not require an additional query.

The second report involves the Fuel table. Since fuel was purchased and entered into this table at times other than during the vacation, a query needs to be created that contains only the fuel purchased during the vacation.

[[#top|Top of page]]

===Vacations table report===

<ol>
<li>Create a new report.</li>
<ol style="list-style-type:lower-alpha">
<li>Click the ''Reports'' icon in the Database list in the Automobile - OpenOffice.org window.</li>
<li>In the Tasks list, click '''Use Wizard to Create Report'''. The Report Wizard window opens.</li>
</ol>
<li>Select the fields.</li>
<ol style="list-style-type:lower-alpha">
<li>Select Table: Vacations in the Tables or Queries dropdown list.</li>
<li>Use the '''>''' to move these fields from the ''Available fields ''list to the ''Fields in report'' list: Date, Motel, Tolls, Miscellaneous, Breakfast, Lunch, Supper, and SnackCost. Click '''Next'''.</li>
[[Image:AddFieldsToRpt.png|thumb|none|500px|''Adding fields to a report.'']]
</ol>
<li>Label the fields: answering the question How do you want to label the fields.<br>
Shorten Miscellaneous to Misc. Click '''Next'''.</li>

<li>Since we are grouping by the date, use the '''>''' button to move the ''Date'' field to the Grouping list. Click '''Next'''.</li>
:[[Image:Grouping.png|thumb|none|500px|Grouping list]]

<li>Sort options.<br>
We do not want to do any additional sorting. Click '''Next'''.</li>

<li>Choose layout.<br>
We will be using the default settings for the layout. Click '''Next'''.</li>

{{Documentation/Note|If you feel adventurous, try selecting some of the other layout choices. After selecting a choice, drag and drop the Report Wizard window so that you can see what you have selected. (Move the cursor over the Heading of this window, and then drag and drop.)}}

<li>Create report.</li>
* Label the report: Vacation Expenses.</li>
* Select Static report.</li>
<li>Click '''Finished'''.</li>
</ol>

[[#top|Top of page]]

===Vacation fuel report===
<ol>
<li>Create a query containing only fuel bought on the days of the vacation.</li>
<ol style="list-style-type:lower-alpha">
<li>Open a query in Design View.</li>
<li>Follow the steps for adding tables in [[../Creating_queries_and_reports#Step 2: Add tables.|Step 2: Add tables]] of [[../Creating_queries_and_reports#Using the Design View to create a query|Using the Design View to create a query]]. Add the Fuel table.</li>
<li>Double-click these fields in the Fuel table listing: Date and FuelCost to enter them in the table at the bottom of the query.</li>
<li>In the Criterion cell of the Date field, type the following:</li>
:<tt>BETWEEN #5/25/2007# AND #5/26/2007#</tt>

[[Image:Qcriterion.png|thumb|none|500px|''Setting the criterion for a query.'']]

<li>Save, name, and close the query. (Suggestion: ''Vacation Fuel Purchases.'')</li>
</ol>

{{Documentation/Tip|When using dates in a query, enter them in numerical form MM/DDYYYY or DD/MM/YYYY depending upon your language's default setting for dates (my default setting is MM/DD/YYYY).<br/>All dates must have a # before and after it. Hence, May 25, 2007 is written #05/25/2007# or #25/5/2007depending upon your language's default setting.}}

<li>Open a new report.</li>
<ol style="list-style-type:lower-alpha">
<li>Right-click the ''Vacation Fuel Purchases'' query.</li>
<li>Select ''Report Wizard'' from the context menu.</li>
</ol>

{{Documentation/Note|When a new report is opened in this way, the query used to open it is automatically selected in the Tables or Queries dropdown list.}}

<li>Create the report.<br>
Use '''>>''' to move both fields from the ''Available Fields'' to the ''Fields in Report'' list. Click '''Next'''.</li>

<li>Label fields.<br>
Add a space to FuelCost to make it Fuel Cost (two words). Click '''Next'''.</li>

<li>Group fields.</li>
<ol style="list-style-type:lower-alpha">
<li>Click Date to highlight it.</li>
<li>Use '''>''' to move the Date field to the Groupings list. Click '''Next'''.</li>
</ol>
<li>Choose layout.<br>
We will be making no changes in the layout. Click '''Next'''.</li>

<li>Create report (final settings).</li>
<ol style="list-style-type:lower-alpha">
<li>Use the suggested name, which is the same as the query.</li>
<li>Select ''Static report. ''Click '''Finish'''.</li>
</ol>
</ol>

[[#top|Top of page]]

==Creating a dynamic report==

We will create a report with some statistics on our fuel consumption. To do this, we have to modify two queries: End-Reading and Fuel Economy. We will be adding the FuelCost field to the End-Reading query. Then we will add the FuelCost field from the End-Reading query to the Fuel Economy query.

{{Documentation/Tip|When opening a query to edit it, it might appear as below.  If you move your cursor over the black line (circled in red), it becomes a double headed arrow. Drag and drop it to a lower position.}}

[[Image:QueryEditing.png|thumb|none|500px|''Appearance of query when opened for editing.'']]

<ol>
<li>Add the ''FuelCost'' field to the End-Reading query:</li>
<ol style="list-style-type:lower-alpha">
<li>In the Fuel table list, double-click to add ''FuelCost'' to the bottom table.</li>
<li> Save and close the query.</li>
[[Image:AddFieldToQ2.png|thumb|none|500px|''Adding an additional field to the query.'']]
</ol>
<li>Right-click the End-Reading query and select Edit from the context menu.</li>
<li>Add the ''FuelCost'' field from the End-Reading query to the Fuel Economy query.</li>
<ol style="list-style-type:lower-alpha">
<li>Right-click the ''Fuel Economy'' query and select '''Edit''' from the context menu.</li>
<li>Double-click the ''FuelCost'' field in the End-Reading query list to the table at the bottom.</li>
</ol>
<li>Add a calculation field to the right of the FuelCost field.</li>
<ol style="list-style-type:lower-alpha">
<li>Type the following in the Field cell:<br>
<tt>"End-Reading".FuelCost/("End-Reading".Odometer - Fuel.Odometer)</tt></li>
<li>Type the following in the Alias cell:<br>
<tt>cost per mile</tt></li>
</ol>

{{Documentation/Note|If you use the metric system, cost per km is the appropriate alias.}}

<li>Save and close the query.</li>
<li>Open a new report.<br>
Right-click the Fuel Economy query and select '''Report Wizard'''.</li>
<li>Select fields.<br>
Move all the fields from the ''Available fields'' to the ''Fields in report'' list. Use the '''>>''' to do so. Click '''Next'''.</li>
<li>Label fields.<br>
Change FuelCost to Fuel Cost by placing a space between the words. Click '''Next'''.</li>
<li>Group fields.<br>
Use '''>''' to move the Date field to the Groupings list. Click '''Next'''.</li>
<li>Sort options: the wizard skipped this one.</li>
<li>Choose layout.<br>
Accept the default. Click '''Next'''.</li>
<li> Create the report.</li>
<ol style="list-style-type:lower-alpha">
<li>Change the report name to Fuel Statistics.</li>
<li>The default setting is Dynamic report, so no change is necessary.</li>
<li>Select ''Modify report layout. ''Click '''Finish'''.</li>
</ol>
</ol>

[[#top|Top of page]]

==Modifying a report==

At the end of the last section, we left the Fuel Statistics report open in the edit mode. We will be working on that report. These same steps can be used with any report that you open for editing.

[[Image:BaseRptEdit.png|thumb|none|500px|''A report in edit mode.'']]

The Author is the name you listed in '''Tools > Options > OpenOffice.org > User Data'''. The date is not correct. The columns need to be moved to the left to give a better appearance. None of the numbers are correct, but their only purpose is to show the number of decimal places.

[[#top|Top of page]]

===Step 1: Change the date.===
<ol>
<li>Click to the right of the date (4/26/20) so that the cursor is next to the field. Use the ''Backspace'' key to erase the date.</li>
<li>'''Insert > Fields > Date'''. This places today's date where the original date was.</li>
<li>Change the date formating:</li>
<ol style="list-style-type:lower-alpha">
<li>Double-click the date field you just inserted. The Edit Fields: Document window opens (figure below).</li>
[[Image:BaseEditFields.png|thumb|none|500px|''Modifying a date field.'']]

<li>Since this is a dynamic report, change the Select field from Date (fixed) to Date.</li>
<li>Change the Format to what you desire. (I use the Friday, December 31, 1999 choice.) Click '''OK'''.</li>
</ol>
</ol>

[[#top|Top of page]]

===Step 2: Change the column widths.===
The column widths can be changed by moving the cursor over the right border of each column so that it becomes a double-headed arrow. Then drag and drop it where you want it. This has to be done for each column in each table in the report. This can also be done with the last column on the right even though there is no black border. It should now look something like the figure below.

[[Image:RealignCols.png|thumb|none|500px|''Realigned columns in a report.'']]

[[#top|Top of page]]

===Step 3: Change the number formating in the cells.===
The fuel quantity should have three decimal places. The Begin, End, and Distance should have one decimal place. Fuel Cost should be currency and have two decimal places, and Cost per mile should have three decimal places.

<ol>
<li>Right-click the cell below Quantity to open the context menu. (The cell is circled in red in the figure above.)</li>
<li>Select '''Number format'''.</li>
<li>In the ''Options'' section,</li> 
<ol style="list-style-type:lower-alpha">
<li>Change the number of Decimal places to 3.</li>
<li>Click the green checkmark. Click '''OK'''.</li>
[[Image:NumberFormat.png|thumb|none|500px|''Option section of the Number Formating window.'']]
</ol>
<li>Change the Cost per mile field.</li>
<ol style="list-style-type:lower-alpha">
<li>Right-click in the cell below ''Cost per mile''.</li>
<li>Select '''Number Format'''.</li>
<li>In the Category list, select ''Currency''. Click '''OK'''.</li>
</ol>
<li>Change the Fuel Cost field.</li>
<ol style="list-style-type:lower-alpha">
<li>Right-click in the cell below ''Cost per mile''.</li>
<li>Select '''Number Format'''.</li>
<li>In the Category list, select ''Currency''.</li>
<li>In the Option section:</li>
<ul>
<li>Set the number of decimal places to 3.</li>
<li>Click the green checkmark.</li>
</ul>
<li>Click '''OK'''.</li>
</ol>
</ol>

[[#top|Top of page]]

===Step 4: Save and close the report.===

Double-click the report. It should now look like the figure below.

[[Image:BaseFinalRpt3.png|thumb|none|600px|''Final report.'']]

==More ways to create reports==
An extension is available to assist in report creation. Sun Report Builder creates stylish, complex database reports. You can define group and page headers, group and page footers, and calculation fields. It is available from [http://extensions.services.openoffice.org/].

To install this extension, follow these steps:

# Select '''Tools > Extension Manager''' from the menu bar. In the Extension Manager dialog, click '''Get more extensions here...'''.
# The OOo extensions page opens in your browser window. Find and select the extension you want to install and follow the prompts to install it. During installation, you will be asked to accept a license agreement.
# When the installation is complete, the extension is listed in the Extension Manager dialog.

For more about extensions, see Chapter 14 (Customizing OpenOffice.org).

{{CCBY}}
[[Category:Getting Started (Documentation)]]