---
title: "Exercise 2 - Populating the Time Tracking"
canonical: "https://docs.getxporter.app/space/XPORTERCLOUD/528151/Exercise%202%20-%20Populating%20the%20Time%20Tracking"
format: markdown
---
> Macro (rw-ui-expands-macro)
> 
> > Macro (rw-expand)
> 
> > Macro (toc)

# Introduction

In this exercise, you'll learn how to populate <span style="color: #0d0d0d">the time-tracking data from Issues using pivot tables</span> in an [Xporter Template](https://docs.getxporter.app/space/XPORTERCLOUD/427622).

This template will consist of two sheets.

# Populating the Time-Tracking Data from Issues Using Pivot Tables

> Macro (rw-ui-steps-macro)
> 
> > Macro (rw-step)
> 
> Create the first sheet.
> 
> > Macro (rw-step)
> 
> To display the header, <span style="color: #0d0d0d">create a table with six columns and one row, including the following headers: </span>
> 
> | **Issue Type** | **Key** | **Summary** | **Estimated (Hours)** | **Remaining (Hours)** | **Logged (Hours)** |
> | --- | --- | --- | --- | --- | --- |
> 
> > Macro (rw-step)
> 
> Since the content below the header is dynamic, create a single-row table for the header, and add the `&{for issues...}` statement below it. This ensures that the header is printed only once. In an [Excel Template](https://docs.getxporter.app/space/XPORTERCLOUD/558471), merge a row of columns, and define the `&{for issues...}` inside those merged cells.
> 
> > Macro (rw-step)
> 
> Create another row table where the Issue Comments will be populated:
> 
> |  |  |  |  |  |  |
> | --- | --- | --- | --- | --- | --- |
> | ${IssueTypeName} | ${Key} | ${Summary} | %{Number("${OriginalEstimate}"/3600)} | %{Number("${RemainingEstimate}"/3600)} | %{Number("${TimeSpent}"/3600)} |
> 
> > Macro (rw-step)
> 
> Close the statement using the mapping `&{end}`
> 
> > Macro (rw-step)
> 
> Apply filters to the header row for the pivot table to function properly.
> 
> > Macro (rw-step)
> 
> Select the first cell of the header (*Issue Type*), find the *Sort & Filter* options, and select *Filter*.
> 
> > ℹ️ The first sheet is complete, extracting all the data you need. Now you will work on the second sheet.
> 
> > Macro (rw-step)
> 
> Create and configure a pivot table (a pivot table allows you to extract the significance from a large, detailed dataset):
> 
> 1. Click the first cell inside the header (*Issue Type*).
> 2. On the *Insert *tab, click *PivotTable*.
> 3. A dialog box appears. Excel automatically selects the data. The default location for a new pivot table is the new worksheet.
> 4. Click *OK*. The second sheet has now been created.
> 5. The PivotTable fields list appears.
> 6. Drag the *Key* field to *Rows*.
> 7. Drag the *Estimated (Hours)* field to *Values*. The* Remaining (Hours) *and* Logged (Hours)* are also going to *Values*.
> 8. For each *Estimated (Hours), Remaining (Hours) *and* Logged (Hours),* on *Values*, you have to set up a configuration:
>   1. Click *Value Field Settings*.
>   2. Choose* the type of calculation you want to use*. In this scenario, we are going to use *Sum*.
>   3. Click *OK*.
> 9. Right-click the pivot table and select *PivotTable Options*.
> 10. Select the Data tab *Data*.
> 11. Check *Refresh data when opening the file*.
> 
> The configuration of a pivot table is complete.
> 
> > Macro (rw-step)
> 
> Once the pivot table is set up, add a chart:
> 
> 1. Select the entire pivot table.
> 2. On the *Insert *tab, click *charts*. You can pick any chart. In this case, we are going to choose the *2-D Column*.
> 3. The chart appears on the sheet.
> 
> > ℹ️ To display only once both table and chart, you can filter the Issues iteration with: `&{for issues|filter=%{'${BulkIssueIndex}' == 0}}`
> > ℹ️ 
> > ℹ️ You'll have to close the code above with `&{end}` on the cell below the chart.
> 
> ## Examples
> 
> Below (Figure 1, 2) there are samples of how the [mappings](https://docs.getxporter.app/space/XPORTERCLOUD/558575) will be displayed in an Excel template.
> 
> ![Figure 1 - Excel template](media://948c0b74-058b-4539-8794-6f8e95caca28)
> 
> ![Figure 2 - Excel template](media://8e23ae95-7926-44a7-81bc-519a8efd41de)
> 
> Below (Figure 3, 4) there are samples of how the generated template will be populated.
> 
> ![Figure 3 - Populated file](media://bb28d302-3e79-4504-a9b2-ac800e0a17a6)
> 
> ![Figure 4 - Populated file](media://5645d1a2-6ebf-4cc3-884f-34b9f7d5b58c)


> ℹ️ **This exercise is now complete. Proceed to **[**Exercise **](https://docs.getxporter.app/space/XPORTERCLOUD/430016)[**3**](https://docs.getxporter.app/space/XPORTERCLOUD/528075/Exercise+3+-+How+to+Populate+Epic+Links)** to learn h****ow to populate epic links.**
> ℹ️ 
> ℹ️ If you like this exercise, please share your opinion on the page by just leaving a comment or a 👍. Your opinion is very important to us.
> ℹ️ 
> ℹ️ Thank you in advance. Enjoy our product!

<details>
<summary>Exercise 2 Resources</summary>

| **File** | **Description** |
| --- | --- |
|  | **Exercise 2** - Sample template file |
|  | **Exercise 2** - Sample generated file |
</details>


<details>
<summary>Support/Troubleshooting</summary>

If you have questions or technical issues, please [contact the Support team via the Customer Portal (Jira service management)](https://jira.getxray.app/servicedesk/customer/portal/2/user/login?destination=portal%2F2%2Fcreate%2F28) or [send us a message using the in-app chat](https://docs.getxporter.app/space/XPORTERCLOUD/18251779/In-App+Chat+Support).
</details>