Instructions for preparing pivot table reports with examples

Pivot Table / Data Pilot is a powerful feature in spreadsheet applications that allows you to prepare reports from lists of data very easily. This will allow you to speed up preparation of reports that you have to generate as part of project management. Although the example I have created and the instructions I have listed out is specific to the timesheets from Redmine the concept can be applied for any lists of data. Once you get the hang of it you wouldn't want to prepare reports any other way. If there are questions please ask somebody around you who knows this well. We have at least one person each in each of our offices who knows this well.

1. From Redmine prepare a timesheet using the right filters.
2. Ensure that the "Group By" is set to "Project".
3. Click on Apply to prepare the timesheet.
4. Click on the CSV link on the top right to download the CSV for the prepared timesheet.
5. Save the file to your project/work/timesheets folder using the format project-code_yyyy-mm-dd_yyyy-mm-dd.csv
6. Open the file using openoffice spreadsheet.
7. In the text import dialog that opens up make sure that the "Separated by" is set to "Comma".
8. Default values should work for other options.
9. Save the spreadsheet as project-code_yyyy-mm-dd_yyyy-mm-dd.ods in the same folder as the csv.
10. Select the first row - set to bold and set a light grey background.
11. Your time sheet for the project for the given time period is ready.
12. have added a calculated column "Issue type" to show you some of the pre-processing you can do on the data before running the "Data Pilot" / "Pivot Table" report.
13. I have also changed the format option for the comment column to allow for wrapping of text to prevent creating an extremely wide column for the comment column.
14. Select the first cell, press ctrl+shift+right and then ctrl+shift+down to select the whole data.
15. From menu select Data > "Data Pilot" > Start.
16. In the dialog that opens up "Current Selection" should be selected by default press OK.
17. In the "Data Pilot" dialog that opens up again you will see the configuration for the data pilot.
18. You can select the location where you want to create this data pilot. It would be a "new sheet" by default and that should be ok.
19. "Total Columns", "Total Rows", "Add Filter", "Enable Drill to Details" should be selected by default and that should be ok.
20. Now the main configuration of the datapilot is in the figure on top of the dialog.
21. There are "Page Fields", "Column Fields", "Row Fields", and "Data Fields" regions in the grid like figure.
22. The different fields available will be seen as buttons on the right and you should be able to drag them and add them to these regions.
23. Once fields are in place you can press ok to prepare the "Data Pilot" / "Pivot Table" report and go to the report.
24. Now deciding what to place in these regions is the trick. If you are good at coordinate geometry then this should be childs play. If not, you will have to play with this by going back and forth after preparing a pivot report. Once you are in the report you can go back to the "Data Pilot" settings by right clicking on the report and clicking on the "Start" item in the context menu that pops up. The logic for the "Data Pilot" / "Pivot Table" report is very simple. Each of the "Page Fields", "Column Fields", "Row Fields" are areas where you can add any number of "axis" from the data set. The "Data Fields" is where you run an aggregation function on one of the columns against all the remaining axes that are not yet added to any of the regions. That is the theory but it is much easier to understand once you try it out a few times. I am attaching a sample spreadsheet with a "Data Pilot" / "Pivot Table" created for you to play with. The spreadsheet has a few different sample "Data Pilot" / "Pivot Table" reports created for you to see / play with.