Pivot
From Wikipedia
Tutorial: How to cre ate Pivot tables for your PHProjekt Statistic
The possibility to Pivot tables is one of the most powerful features in Excel (and of course OpenOffice covers this feature too).
It is most commonly used for accounting data, but with its many possibilities to sort, filter and summarize data, it is a very useful tool for analysing your project bookings as well.
By using a pivot table, you can crea te an interactive summary of your bookings, changing the output according to your needs. You can total your booked hours for example either by projects, users, dates or even todos and helpdesk tickets and rearrange this information later on quickly by dragging the buttons in a new position.
Thus Pivot Tables are not only powerful, but also very easy to handle, which makes it a very suitable tool for manipulating the PHProject statistic to ones requirements.
Of course this tutorial can’t cover all possible outputs you can crea te with Pivot tables, rather it tries to introduce the basic Idea and handling of PHProject Statistic with pivot tables.
This Tutorial will be referring to Excel Pivot Tables, but as mentioned before you can of course use OpenOffice 2.0 (or higher) instead.
Please notice as well that as our company is based in Germany the Screenshots you find in this tutorial were taken from the German Version of Microsoft Excel.
1. Importing the data into an Excel Spreadsheet
First enter the Project Module and call either “Statistic” or “MyStatistic” depending on the Statistic you want to cre ate. Once you are on our Statistic page, choose the options, projects and user you want to consider in your statistic, choose “Sort by Project” and continue by clicking OK. On the next page (here the statistic is displayed in html format) proceed by clicking export and than choose “xls” as Export format and save the file to disk. Just open the saved file with Excel and you are done importing the data.
2. Creating the Pivot Table with Excel
In order to cre ate your Pivot table, start by selecting all rows and columns you want to include in your Pivot table. In my example I will explain how to build an overview over the projects, users and dates. To achieve this I only select the columns Name, Project Name, Date, Hours and Minutes, if you are not sure which columns to select, simply choose all exported columns at this point, you can easily remove redundant columns later on. To finally crea te a Pivot table start the PivotTable wizard from the data menu as shown below.
While there are many advanced options available to use, for us it is sufficient to only click the finish button as we have already selected the required columns before(If you don’t select anything all columns will be included). Now Excel crea tes a blank PivotTable which you can edit by dragging the fields you want to include in the appropriate section of the table.
In the following steps I will describe how to cre te different Pivot tables, if those examples don’t cover your requirements, I suggest to try them anyhow so that you can get the hang off it and create your own output afterwards.
3.Create overview of bookings per day In order to summarize your bookings per day, drag the field “Date” in the Row fields section. That way each row will show a distinct date. To view the booked hours on each date drag the fields “Hours” and “minutes” in the Data Items Section, in case you want to see the time booked on the different projects in addition to the total sum booked on a day, drag the field “Project” to the Column Fields section. Finally you might want to distinguish between the users. To achieve that all you have to do is drag the field “Name” to the Page Fields Section. No you can choose whether you want to display the bookings of all users, or whether only certain users are of interest to you. Now you can format the table the same way you would format a normal excel table and your first Pivot Table is scuccesfully created.
4. Create overview of bookings per project
Proceed in the same way as you did in step 3, only switch the fields “Date” and “Projects Name” and you are done.
5. Create overview of booking per user
Proceed in the same way as you did in step 3, only switch the fields “Name” and “Date” and you are done.
