Pivot Table and Project Timesheet in Microsoft Dynamics AX 2012
- November 20, 2015
- Posted by: cemblog
- Category: Microsoft Dynamics
What are Project Timesheets?
Project timesheets are the detailed breakdown of the hours spent on a project by an employee. In Microsoft Dynamics AX businesses can have multiple projects running in tandem with many employees working across them. Each worker will enter their time into the system for every activity they partake in and for how long they worked on each action.
Due to this Project timesheets are often very long with many duplicate entries for different times and days. Sorting and summarizing this information can become very tedious and difficult the larger a project is. This is where a handy feature in Excel, called pivot table, comes in hand.
What is Pivot Table?
Pivot table is a function in Excel that has the ability to take a data table and summarize the information quickly and efficiently. In addition to summarizing your data it gives you increased functionality with sorting and viewing the information. Its greatest usability comes in a data table with a lot of duplicates information like a project timesheet.
Before we start it is important to note that the data must be together in a table (though it doesn’t need to be formatted as one) and must have a title row. Please also see the end of this guide on how pivot table displays information.
How to use Pivot Table on a Project Timesheet:
Before the timesheet can be put into a pivot table the data must be exported from Dynamics AX into Excel. To do this first you must navigate to project management and follow the below instructions.
- Click Project management and accounting > Inquiries > Timesheet Details.
- Select the period, the time frame, the employee and or the
- One the data populates hit Ctrl +T to export the data.
Now that we have our data exported it is time to create our pivot table. For the following example I have exported data using only period and time frame as filters. This means my data will show multiple projects and multiple employees. As mentioned before the larger your data the greater your benefit from the use of pivot table will be.
- Take your data table and highlight all the data
- Go to the Insert tab and select the pivot table under the tables
3. A pop up will show up asking you if you want the pivot table to be in a new worksheet or an existing worksheet.
a. If you select new it will open a new tab with the table.
b. If you wish to place it in an existing sheet, first select that option and then select the cell you wish the table to post to. You might get an error if there is not enough room or if you selected the cell before choosing the existing sheet option.
4. Once you click okay a new menu will open on the right hand side of the screen. This menu will help you select what data you wish to display and how you want it displayed.
5. Click the fields you would like to add from the Choose fields to add to report
6. Drag the fields between the below areas to show the data how you wish.
a. Under the Values area the way the value is displayed (sum, count, average, etc.) can be changed by clicking the arrow next to the field and selecting Value Field Settings.
b. A field can be show multiple times by dragging it from the top half to the desired box below.
7. Once your fields are all selected and in the right display field your pivot table is complete.
Once your pivot table is complete you can filter the data as you please. You can also change the table at any time simply by clicking on the table to reopen the pivot fields menu.
Pivot Table Functions
In this example a user will be able to quickly filter through projects and view the hours based on their approval status. The below shows how a pivot will display information and gives a few tips on how the different fields can be used.
• Rows: Info will show up on the left hand side of the table. Best used with a category. In the below example employee name was used. Usually interchangeable with column.
• Value: Will display in the main body of the table. Best for numerical data. In the below example hours worked was used. The value can be changed be using the drop down menu and selecting value field settings.
• Column: Will display on the top of the table. Best used with a category. In the below example approval status was used. Usually interchangeable with row.
• Filters: Will display above the table. Best used for a category for the rest of the data. In the below example project name was used. By sorting I can see one project at a time.
This concludes our project timesheet tutorial. Keep in mind that pivot table can be used across a vast array of data table exported from Dynamics AX not just timesheet. Any large data tables can surely benefit from pivot tables ability to concisely summarize and utilize data.