...
On this case is a template on Excel, so don't forget the note below.
| Info |
|---|
Using an XLSX XLS template, please take note that to define an iteration for multiple columns, you need to merge a row of columns and define the #{for i=JQLIssuesCount...} inside that those merged cells. The same thing should be made to define the #{end} of the same iteration. All content between the #{for i=JQLIssuesCount...} definition and the #{end} won't will be duplicated for each iteration. |
With that done, you 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].CreatedDateCreated} |
Finally close the statement using the mapping #{end}.Well, the first sheet was completed. Let´s go to the second sheet whose name can be "Chart by month".
We are going to create a pivot table.
...
- To create a pivot table click on the first cell inside the header.
- On the Insert tab, click PivotTable
- A Dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is the New Worksheet.
- Click ok. The second sheet was created. Rename it to "Chart by Month".
Now, The PivotTable fields list appears.
- The Field Month we are going to drag on the Rows.
- The monthKey we are going to drag on the Values.
The configuration of the pivot table is done but you we are going to add a chart.
- Select all pivot table.
- On the Insert tab, click charts. You can pick up all chart. On this case, I am going to choose the 2-D Column.
- The chart appears on the sheet.
Well, the second sheet was completed. We are going to repeat the steps of the second sheet.
...
To display once time the chart and the pivot table, you must put this code in the first cell:
| &{for issues|filter=%{'${BulkIssueIndex}' == 0}} |
...
We are going to use the Macros to set up the monthMonth field.
We need to go on the tab Go to View -> Macros -> View Macros -> Edt -> ThisWorkBook
The steps to set up are:
...
. In Macros in: select This Workbook. Name the macro "MonthFormat" and click Create.
A new window appears to insert the VBA code. In the created module, insert 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 |
| Info |
|---|
On the first step is when export the template we want to see the first page. The second step is going to do an iteration about the Date and set up on Month the year and the month Finally, the third step is update the pivot table with the new dates on the second sheet. |
| Note |
|---|
When you have a template that only contains a JQL query or a JQL iteration, on as in this case is true, you can now bypass going to an unrelated Issue to export it and go straight to from the menu Xporter Reports. |
After exporting, you should go to View -> Macros -> View Macros and run the MonthFormat macro.
Below there is a sample of how the mappings will be displayed in an Excel template:
Below there is a sample of how the generated file will be populated:
| Info |
|---|
If you like this exercise, please share your opinion on the page by just leaving a comment or a Thank you in advance. Enjoy our product. |
. Your opinion is very important for us.
| Expand | ||||||
|---|---|---|---|---|---|---|
| ||||||
Here's the Exercise 2 related 7 related files:
|