---
title: "Exercise 7 - Project Overview by Month"
canonical: "https://docs.getxporter.app/space/XPORTERCLOUD/527867/Exercise%207%20-%20Project%20Overview%20by%20Month"
format: markdown
---
> Macro (rw-ui-expands-macro)
>
> > Macro (rw-expand)
>
> > Macro (toc)
# Introduction
In this exercise, you'll learn how to populate the Issue time taking from some Issues with pivot tables on an [Xporter](https://marketplace.atlassian.com/apps/891368/xporter-export-issues-from-jira?tab=overview&hosting=cloud)-generated file.
This [Template](https://docs.getxporter.app/space/XPORTERCLOUD/427622) is going to have two sheets.
# Project Overview by Month (Issues Count by Month) Using Macros on an Excel File
## First Sheet - Issues
> Macro (rw-ui-steps-macro)
>
> > Macro (rw-step)
>
> Create the first sheet and name it *Issues*.
>
> > Macro (rw-step)
>
> To display the header, you must create it using a table with seven columns and one row:
>
> | **Key** | **Issue Type** | **Priority** | **Summary** | **Status** | **Date** | **Month** |
> | --- | --- | --- | --- | --- | --- | --- |
>
> > Macro (rw-step)
>
> Since all the table contents below the header are dynamic, first, you need to create a single-row table to be the header.
>
> > Macro (rw-step)
>
> Below, add the `#{for i=JQLIssuesCount|clause=project = ACD order by Key desc} `statement, so the header is printed only once.
>
> > ℹ️ - When using an [Excel template](https://docs.getxporter.app/space/XPORTERCLOUD/558471) to define an iteration for multiple columns, you need to merge a row of columns and define the `#{for i=JQLIssuesCount...}` statement inside those merged cells.
> > ℹ️ - The same thing must be done to define the `#{end}` statement of the same iteration. All content between the `#{for i=JQLIssuesCount...}` definition and the `#{end}` will be duplicated for each iteration.
>
> > Macro (rw-step)
>
> Create another row table where the Issue Comments will be populated:
>
> | | | | | | | |
> | --- | --- | --- | --- | --- | --- | --- |
> | ${JQLIssues[i].Key} | ${JQLIssues[i].IssueTypeName} | ${JQLIssues[i].Priority} | ${JQLIssues[i].Summary} | ${JQLIssues[i].Status} | ${JQLIssues[i].Created} | ${JQLIssues[i].Created} |
>
> > Macro (rw-step)
>
> Close the statement using the mapping `#{end}`
## Second Sheet - Chart by Month
> Macro (rw-ui-steps-macro)
>
> > Macro (rw-step)
>
> Create a pivot table.
>
> > ℹ️ A pivot table allows you to extract the significance from a large, detailed dataset.
>
> 1. To create a pivot table, click the first cell inside the header.
> 2. On the *Insert *tab, click *PivotTable*.
> 3. A dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is the new worksheet.
> 4. Click *OK*.
> 5. The second sheet was created. Name it *Chart by Month*.
>
> > Macro (rw-step)
>
> The *PivotTable* fields list appears.
>
> 1. Drag the *Month* field to rows.
> 2. Drag the *Key* field to values.
>
> > ℹ️ The configuration of the pivot table is concluded.
>
> > Macro (rw-step)
>
> Add a chart:
>
> 1. Select all of the pivot table.
> 2. On the *Insert *tab, click *charts*. In this scenario, the chosen chart type is the 2D Column.
> 3. The chart appears on the sheet.
>
> > Macro (rw-step)
>
> Use Macros to set up the *Month* field.
>
> 1. Go to *View* -> *Macros* -> *View Macros.*
> 2. In *Macros in*, select *This Workbook*.
> 3. Name the macro *MonthFormat* and click *Create*.
>
> > Macro (rw-step)
>
> A new window appears for you to insert the [Visual Basic for Applications](https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office) code. In the created module, add the following code:
>
> ```
> Sub MonthFormat()
>
> FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
>
> For i = 3 To FinalRow
>
> cleanDate = Left(Worksheets("Issues").Cells(i, "G").Value, 10)
>
> Worksheets("Issues").Cells(i, "G").Value = "'" + Format(cleanDate, "MMMM-yyyy")
>
> Next i
>
> Set pvtTable = Worksheets("Chart by Month").Range("A4").PivotTable
>
> pvtTable.RefreshTable
>
> End Sub
> ```
>
>
> > ⚠️ When you have a Template that only contains a [JQL](https://docs.getxporter.app/space/XPORTERCLOUD/527003) query or a JQL iteration, as in this case, you can export any Issue as a single exportation.
>
> > Macro (rw-step)
>
> After exporting, go to *View* -> *Macros* -> *View Macros* and run the *MonthFormat* macro.
>
> ## Examples
>
> Below (Figure 1, 2 and 3), there are samples of how the [mappings](https://docs.getxporter.app/space/XPORTERCLOUD/558575) will be displayed in an Excel template.
>
> 
>
> 
>
> 
>
> Below (Figure 4 and 5) there are samples of how the generated file will be populated.
>
> 
>
> 
> ℹ️ 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!
Exercise 7 Resources
| **File** | **Description** |
| --- | --- |
| | Exercise 7 - Sample template file |
| | Exercise 7 - Sample generated file |
Support/Troubleshooting
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).