Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/DataPilot example 2"

From Apache OpenOffice Wiki
Jump to: navigation, search
(New page: {{DISPLAYTITLE:DataPilot example 2: Timekeeping}} {{Documentation/CG3Ch6TOC |ShowPrevNext=block |PrevPage=Documentation/OOo3_User_Guides/Calc Guide/DataPilot example 1 |NextPage=Documentat...)
 
(→‎Differences and advantages)
 
(5 intermediate revisions by 2 users not shown)
Line 5: Line 5:
 
|NextPage=Documentation/OOo3_User_Guides/Calc Guide/DataPilot example 3
 
|NextPage=Documentation/OOo3_User_Guides/Calc Guide/DataPilot example 3
 
}}__notoc__
 
}}__notoc__
−
This example is often used within the consultancy field and is also used in several variations in the user support area. The task is to provide a means for one or more users to keep track of working hours.
+
This example is often used by consultants and in several variations in user support. The task is to provide a means for one or more users to keep track of working hours.
  
 
A typical way of doing this is to create a spreadsheet per month and a sum sheet with all the results of one year. For each employee, there is one file.
 
A typical way of doing this is to create a spreadsheet per month and a sum sheet with all the results of one year. For each employee, there is one file.
Line 15: Line 15:
 
=== Practical problems and questions ===
 
=== Practical problems and questions ===
 
# It is very difficult and time-consuming to create the timekeeping table: 12 sheets that have to be copied from a raw template and adjusted for each month, and a sheet with all the yearly sums with references to all the other sheets. Users often search for a macro to make the creation easier.
 
# It is very difficult and time-consuming to create the timekeeping table: 12 sheets that have to be copied from a raw template and adjusted for each month, and a sheet with all the yearly sums with references to all the other sheets. Users often search for a macro to make the creation easier.
−
# The shown file contains only the data of one employee. How can you get all the data for all the employees, so that you can have a summary of all the work hours from all employees of a department or the whole company?
+
# The file shown contains only the data for one employee. How can you get all the data for all the employees, so that you can have a summary of all the work hours from all employees of a department or the whole company?
−
# How can you compare employees and/or departments?  
+
# How can you compare employees of a department or the whole company?  
−
# The shown file contains data for one year. How can you compare it with the data of the previous years?
+
# The file shown contains data for one year. How can you compare it with the data of the previous years?
  
 
=== Solution ===
 
=== Solution ===
Line 31: Line 31:
 
# Choose '''Data > DataPilot > Start''' and click '''OK'''.
 
# Choose '''Data > DataPilot > Start''' and click '''OK'''.
 
# On the DataPilot dialog:
 
# On the DataPilot dialog:
−
#* Drag the field '''date''' into the layout area ''Row fields''.
+
#* Drag '''date''' into the ''Row fields'' area.
−
#* Drag the field '''hours''' into the layout area ''Data fields''. Notice that it becomes '''Sum – hours'''.
+
#* Drag '''hours''' into the ''Data fields'' area. Notice that it becomes '''Sum – hours'''.
−
#* Drag the field '''name''' into the layout area ''Column fields''.
+
#* Drag '''name''' into the ''Column fields'' area.
 
# Click '''More''' to show more options in the lower part of the dialog.
 
# Click '''More''' to show more options in the lower part of the dialog.
−
# Choose '''– new sheet –''' for ''Results to.''
+
# In the ''Results to'' field, select '''– new sheet –''' from the drp-down list.
 
# Click '''OK'''.
 
# Click '''OK'''.
  
Line 44: Line 44:
 
[[Image:CG3Ch6F14.png|thumb|none|500px|''The evaluation, done within seconds with DataPilot'']]
 
[[Image:CG3Ch6F14.png|thumb|none|500px|''The evaluation, done within seconds with DataPilot'']]
  
−
The result is much more powerful than the solution with the classic formula-based calculation. For example, you can summarize the daily results to a monthly result very easily.
+
The result is much more powerful than is possible with the classic formula-based calculation. For example, you can summarize the daily results to a monthly result very easily.
  
 
<ol>
 
<ol>
Line 50: Line 50:
 
<li>Choose '''Data > Group and Outline > Group''' and click '''OK'''. The result is now grouped into months.</li>
 
<li>Choose '''Data > Group and Outline > Group''' and click '''OK'''. The result is now grouped into months.</li>
  
−
[[Image:CG3Ch6F15.png|thumb|none|500px|''Monthly sums'']]If you need a result with a percentage, start the DataPilot from this page.
+
[[Image:CG3Ch6F15.png|thumb|none|500px|''Monthly sums'']]
 +
</ol>
  
 +
If you need a result with a percentage, start the DataPilot from this page.
 +
 +
<ol>
 
<li>Select the cell '''A3''' (or any other cell that contains a result of the DataPilot).</li>
 
<li>Select the cell '''A3''' (or any other cell that contains a result of the DataPilot).</li>
 
<li>Choose '''Data > DataPilot > Start'''.</li>
 
<li>Choose '''Data > DataPilot > Start'''.</li>
 
<li>Double-click on the field '''Sum – hours''' to open the Data Field dialog.</li>
 
<li>Double-click on the field '''Sum – hours''' to open the Data Field dialog.</li>
 
<li>Click on '''More''', to see more options.</li>
 
<li>Click on '''More''', to see more options.</li>
−
<li>Switch the type of the displayed value to '''% of column'''.</li>
+
<li>Switch '''Displayed value > Type''' to '''% of column'''.</li>
  
 
[[Image:CG3Ch6F16.png|thumb|none|500px|''Properties of the data field'']]
 
[[Image:CG3Ch6F16.png|thumb|none|500px|''Properties of the data field'']]
Line 63: Line 67:
 
</ol>
 
</ol>
  
−
In the result the percentages are shown as a decimal number. If you prefer to have a percent format, select the cells and click on the '''%''' icon in the menu bar.
+
The result shows percentages as a decimal number. If you prefer to have a percent format, select the cells and click on the '''%''' icon in the menu bar.
  
 
[[Image:CG3Ch6F17.png|thumb|none|500px|''Result with percentages'']]
 
[[Image:CG3Ch6F17.png|thumb|none|500px|''Result with percentages'']]
Line 73: Line 77:
 
# Double-click on the field '''Sum – hours''' to open the Data Field dialog.
 
# Double-click on the field '''Sum – hours''' to open the Data Field dialog.
 
# Click '''More''', to see more options.
 
# Click '''More''', to see more options.
−
#* Switch the ''Type'' of the displayed value to '''Difference from'''.
+
#* Switch the ''type'' of the Displayed value to '''Difference from'''.
 
#* Switch the ''Base field'' to name.
 
#* Switch the ''Base field'' to name.
 
#* Switch the ''Base item'' to Brigitte.
 
#* Switch the ''Base item'' to Brigitte.
Line 80: Line 84:
 
[[Image:CG3Ch6F18.png|thumb|none|500px|''Absolute comparison with Brigitte'']]
 
[[Image:CG3Ch6F18.png|thumb|none|500px|''Absolute comparison with Brigitte'']]
  
−
As a last example we switch to an accumulated view; that is, continuing sums of all values:
+
As a final example we switch to an accumulated view; that is, continuing sums of all values:
  
 
# Choose '''Data > DataPilot > Start'''.
 
# Choose '''Data > DataPilot > Start'''.
Line 92: Line 96:
  
 
=== Differences and advantages ===
 
=== Differences and advantages ===
−
These experiments show an important aspect of the DataPilot concept.
+
These examples show an important aspect of the DataPilot. Normally you have to collect your data according to the way you want the result to be represented. This means you have to use a specific structure and you’re stuck with it.
−
 
+
−
With the classic method you have to collect your data according to the way you want the result to be represented. This means you have to use a specific structure and you’re stuck with it.
+
  
−
The DataPilot works more like a real database. The source data are collected in a regular form, a somewhat primitive but universal structure: a simple spreadsheet that contains all data. Only when you want to look at it, you select which part of the data you want to use.
+
The DataPilot works more like a real database. The source data are collected in a simple spreadsheet that contains all data. Only when you want to look at it, you select which part of the data you want to use.
  
  
−
{{CCBY}}
+
{{CCBYSA}}
 
[[Category: Calc Guide (Documentation)]]
 
[[Category: Calc Guide (Documentation)]]

Latest revision as of 15:28, 27 December 2010



This example is often used by consultants and in several variations in user support. The task is to provide a means for one or more users to keep track of working hours.

A typical way of doing this is to create a spreadsheet per month and a sum sheet with all the results of one year. For each employee, there is one file.

One month of timekeeping for one employee
Yearly sums for one employee

Practical problems and questions

  1. It is very difficult and time-consuming to create the timekeeping table: 12 sheets that have to be copied from a raw template and adjusted for each month, and a sheet with all the yearly sums with references to all the other sheets. Users often search for a macro to make the creation easier.
  2. The file shown contains only the data for one employee. How can you get all the data for all the employees, so that you can have a summary of all the work hours from all employees of a department or the whole company?
  3. How can you compare employees of a department or the whole company?
  4. The file shown contains data for one year. How can you compare it with the data of the previous years?

Solution

To use the DataPilot for this task, collect all data into one single table. This can be done either manually or by importing a file from an (electronic) timekeeping machine.

In very simple cases each employee takes care of their own working hours. If you need calculations that cover several employees, departments, or the whole company, just copy everything into one huge table.

Data basis in Calc

Using the DataPilot just needs 12 mouse clicks and gives you within a few seconds a nice overview:

  1. Select the cell A1 (or any other single cell within the list).
  2. Choose Data > DataPilot > Start and click OK.
  3. On the DataPilot dialog:
    • Drag date into the Row fields area.
    • Drag hours into the Data fields area. Notice that it becomes Sum – hours.
    • Drag name into the Column fields area.
  4. Click More to show more options in the lower part of the dialog.
  5. In the Results to field, select – new sheet – from the drp-down list.
  6. Click OK.
DataPilot dialog

The result appears on a new sheet.

The evaluation, done within seconds with DataPilot

The result is much more powerful than is possible with the classic formula-based calculation. For example, you can summarize the daily results to a monthly result very easily.

  1. To group together the rows, select the cell A5 (or any other cell that contains a date).
  2. Choose Data > Group and Outline > Group and click OK. The result is now grouped into months.
    3. Monthly sums

If you need a result with a percentage, start the DataPilot from this page.

  1. Select the cell A3 (or any other cell that contains a result of the DataPilot).
  2. Choose Data > DataPilot > Start.
  3. Double-click on the field Sum – hours to open the Data Field dialog.
  4. Click on More, to see more options.
  5. Switch Displayed value > Type to % of column.
    6. Properties of the data field
  6. Click OK twice.

The result shows percentages as a decimal number. If you prefer to have a percent format, select the cells and click on the % icon in the menu bar.

Result with percentages

To get a comparison between employees, start the DataPilot again:

  1. Select the cell A3 (or any other cell that contains a result of the DataPilot).
  2. Choose Data > DataPilot > Start.
  3. Double-click on the field Sum – hours to open the Data Field dialog.
  4. Click More, to see more options.
    • Switch the type of the Displayed value to Difference from.
    • Switch the Base field to name.
    • Switch the Base item to Brigitte.
  5. Click OK twice.
Absolute comparison with Brigitte

As a final example we switch to an accumulated view; that is, continuing sums of all values:

  1. Choose Data > DataPilot > Start.
  2. Double-click on the field Sums – hours to open the Data Field dialog.
  3. Click More, to see more options.
    • Switch the type of the displayed value to Running total in.
    • Switch the Base field to Date.
  4. Click OK twice.
The DataPilot now shows accumulated values

Differences and advantages

These examples show an important aspect of the DataPilot. Normally you have to collect your data according to the way you want the result to be represented. This means you have to use a specific structure and you’re stuck with it.

The DataPilot works more like a real database. The source data are collected in a simple spreadsheet that contains all data. Only when you want to look at it, you select which part of the data you want to use.


Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA).
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/OOo3_User_Guides/Calc_Guide/DataPilot_example_2&oldid=190825"
Personal tools