This article will walk you through the PivotTable sample that ships with AutoTag and what you need to know when working with PivotTables.
AutoTag lets you create Excel PivotTables using either data from an existing worksheet or with data created dynamically from Windward tags. This feature was added in version 13 to add the following missing functionality
- Carrying across existing PivotTables referencing Excel data in a spreadsheet to the generated report output.
Ability to create PivotTables that can reference cells and cell ranges created by Windward tags to create a working PivotTable in the generated report output.
Where do I use this feature?
PivotTables are used when you need to dynamically filter a LOT of Excel data interactively in a spreadsheet. If you find yourself creating a LOT of data with Windward Tags that you then need to be able to view and filter interactively, then referencing those Windward tags with a PivotTable is your answer. You may also have a use for PivotTables already existing in your spreadsheet. Because Windward will now carry through working PivotTables to the report output, you have one less thing to change in your existing template.
While we aim to support every feature of the Microsoft Office products that we can, there are currently some things we are not able to do with PivotTables in Excel. For a full list see the article below.
Basic Pivot Table Example
The basic PivotTable example connects to Windward's public Northwind SQL database to retrieve a list of employees, shipment dates and their amounts. The spreadsheet contains two tabs:
- SQL_Data - contains Windward tags in a ForEach loop to return the set of data that will be referenced by the Pivot Table.
- SQL_Pivot_Table – contains the created Pivot Table based on the set of data returned by SQL_Data.
You can find the PivotTable sample template in your Documents/AutoTag folder or you can it from the link below.
Pivot Data Set
In the SQL_Data tab, we have created a simple table that retrieves the following columns dynamically by way of a Windward ForEach tag:
- Order ID
- First Name
- Last Name
- Shipment Date
When the report is generated, this table will return a list of the columns above with unique values on each row to create the data set that will be used in the PivotTable analysis.
Step-by-Step PivotTable Creation
Step 1: Data Selection
Selecting your data set from the SQL_Data table can be done by selecting cell A1 and dragging your mouse to cell F2. You should see a green box around your selection at this point.
On the Insert tab, with your data range still selected, click the PivotTable icon (shown above).
NOTE: Only select the header row and data tag rows. DO NOT include the ending ForEach tag in your selection
Step 2: Insert your Pivot Table
You will be prompted by the Create PivotTable window option Select a table or range with the Table/Range field pre-populated with your previous selection from Step 1, i.e. SQL_Data!$A$1:$F$2. Make sure New Worksheet is selected and click OK to proceed with the PivotTable creation.
NOTE: Because PivotTables can occupy an undetermined amount of space when generated, you can currently only have a single PivotTable per worksheet. Multiple PivotTables on the same worksheet is a current limitation.
Step 3: PivotTable Layout Areas
When the new worksheet is created, a blank PivotTable is created and you are then presented with the PivotTable Fields selection pane. Here you will choose which fields from your data set you want displayed and how you want them arranged.
The following PivotTable areas are available for you to drag and drop fields into:
- Filters – allows you to choose a field that will filter your data set, such as a date
- Columns – displays your data results by creating column after column of output
- Rows – displays your data results by creating row after row of output
- Values – displays values of your data and can perform special functions, like summation. This data will be created in a cross tabular view between your row and column fields
NOTE: You can group multiple fields in any of the areas below. In row and column output, your data will be grouped by the topmost selection and sub items will appear in a collapsible tree format
Step 4: Choosing Data Fields and Layout
Here is where the real creation of the PivotTable starts. This often involves playing with fields in different data areas to get your output just right. This section will guide you through how this template was built and give notes on why each field was placed in each area.
(Note the gold flags are shown in the larger image below).
Click on the field Ship Date and drag it into the Filters section. We selected this field because we wish to filter the displayed output by the dates that we choose for each order.
Click and drag the Order ID field into the Columns section. We chose this field because we want to be able to filter row and column data by one or many order IDs in the data set.
Click and drag the Country field into the Rows section. Then click and drag the Last Name field into the Rows section. Here we have chosen two fields. We wanted to group the rows of data by country first and then list the last name of each person in every country group. In the output you will see each country listed and the last name of each person indented under them. Each country will be collapsible.
The final field is the most critical because it determines which data is shown for each row and column value and how it is displayed. In a PivotTable, both the last column and last row are calculated as grand totals of the data in each respective column and row. This allows you to quickly analyze totals from both fields without needing to recalculate.
- Click and drag the Amount field into the Values area. You will notice that, by default, Excel uses a Count total for this field. However, since we are aware that this field is a monetary amount, we would rather have a Sum of the values to give a grand total.
- Click on the down arrow by the field in the Values area and choose Value Field Settings… In the Value Field Settings window choose Sum from the Summarize Values By area and click OK.
At this point your PivotTable should look like the image below:
Step 5: Run the Template and Interact with the PivotTable
- From the AutoTag tab click Output -> XLSX.
- You will be prompted to enter 2 date range variables (st_date and end_date), go ahead and use the default values listed and click OK.
Finally you get to work with the PivotTable you just created. Your template is now converted into an Excel output report with a fully functioning PivotTable based off dynamic data imported by Windward tags.
Step 6: Filtering the Data set
With your brand new shiny PivotTable you can now do some amazing analysis!
- If you want to filter any of your fields, with the value cell for Ship Date (B1) selected you only need to click on the filter icon in the PivotTable and select a single or multiple items.
- Select the following dates
- Next we want to reduce filter the orders to only a select few. Select the filter icon near the Column Labels cell and choose the following orders numbers below.
- Your output should now appear as the image below. Notice the Grand Total rows and columns.
Also note that because we chose many fields to display in the Rows area, the first field (Country) cannot be sorted because it is displaying all possible countries for the Last Name values that are shown. Therefore, you can only filter on the Last Name field.
Step 7: Drilling Down the Data set
If you would like to see additional information for a value listed, you just need to double click on the value. We would like to know the Order ID associated with the amounts shown for Mr. Buchanan.
- Double click on Buchanan in cell A6 and you will be presented with a Show Detail window.
- Select the field containing the detail you want to display, Order ID in the example.
- Selecting Order ID from the Show Detail window returns the PivotTable with all order IDs in row form for Mr. Buchanan for the dates we have selected.
Congratulations! You have now built and filtered a PivotTable based on dynamic data imported from your data source with AutoTag.