You are on the Xporter Server documentation. If you are looking for Xporter Cloud documentation, you can find it in this page.

  • Created by Unknown User (sdrg), last modified by user-0caa0 on Jan 24, 2018

In this exercise, you'll learn how to populate the Issue time tracking from some issues with pivot tables on a Xporter generated file.

This template is going to have two sheets.

Let's create the first sheet. If you want to display the header you must create the Header using a table with 6 columns and 1 row:

Issue TypeKeySummaryEstimated (Hours)Remaining (Hours)Logged (Hours)

Since all the table contents below the Header are dynamic, firstly we need to create a single row Table to be the Header, and below add the &{for issues...} statement, so the Header is printed only one time.

On this case is a template on Excel, so don't forget the note below.

Using an XLSX template, please take note that to define an iteration for multiple columns, you need to merge a row of columns and define the &{for issues...} inside that merged cells. The same thing should be made to define the &{end} of the same iteration. All content between the &{for issues...} definition and the &{end} will be duplicated for each iteration.

With that done, you create another row table where the Issue Comments will be populated:

${IssueTypeName}

${Key}${Summary}%{Number(“${OriginalEstimate}”/3600)}%{Number(“${RemainingEstimate}”/3600)}%{Number(“${TimeSpent}”/3600)}

Now close the statement using the mapping &{end}.

Finally, we need to give filters to the headers of the table in order for the table to work with the pivot tale. To do this, Select the first cell of the header ("Issue Type"), find the "Sort & Filter" options, and select "Filter".

Well, the first sheet is complete, extracting all the data we need. Let´s go to the second sheet.

We are going to 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 on 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 for you. The default location for a new pivot table is the New Worksheet.
  4. Click ok. The second sheet was created.

Now, The PivotTable fields list appears.

  1. The Field Key we are going to drag on the Rows.
  2. Estimated (Hours) we are going to drag on the Values.
  3. The Remaining (Hours) and Logged (Hours) are also going to Values.
  4. For each Estimated (Hours), Remaining (Hours) and Logged (Hours) on the Values, we are going to set up a configuration:
    1. Click on Value Field Settings.
    2. In Choose the type of calculation you want to use. We are going to use the Sum.
    3. Click ok.
  5. Right-click the pivot table and select PivotTable Options
  6. Select tab Data
  7. Check Refresh data when opening the file.

The configuration of a pivot table is done but you are going to add a chart.

  1. Select all pivot table.
  2. On the Insert tab, click charts. You can pick any chart. In this case, I am 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.

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:

If you like this exercise, please share your opinion on the page by just leaving a comment or a (thumbs up). Your opinion is very important to us.

Thank you in advance.

Enjoy our product. (big grin)

Here's the Exercise 2 related files:

FileDescription
Exercise2Template

Exercise 2 Sample Template file

Exercise2GeneratedExercise 2 Sample generated file
  • No labels