About pivot tables
A pivot table is a tool to summarize, organize, sort, reorganize, group, count, total, or average selected rows and columns of data in a spreadsheet or database table by dragging and dropping data fields. A pivot table doesn't actually change the spreadsheet or database itself, but it does allow you to transform columns into rows and rows into columns and allows grouping by any data field. Because pivot tables summarize data, you can use them to find unique values in a field—this is an excellent way to quickly see all the values that appear in a field and find typos or other inconsistencies. You can use pivot tables to create charts and reports.
When data is returned from a DataMiner data source, one of the tabs you see at the bottom of the window is Pivot Table. Available data fields from the selected data source are shown at the top of the DataMiner window.
You drag and drop columns of data into the rows and columns of the pivot table. You drag these fields into the areas of the pivot table that indicate Drop Row Fields Here, Drop Column Fields Here and Drop Data Fields Here.
Save a pivot table
When the rows and columns of data are arranged the way you want, you can use the Save As button in the lower-right corner of the window to save the pivot table data in *.xls, *.csv, *.html, *.rtf, *.xml, or *.txt format for use in reports and other programs such as Microsoft Excel.
Pivot table example
The WIP data source in DataMiner is typically displayed in a pivot table to provide optimal data visualization.
Log into the FactoryLogix Analytics client application.
Select the Data Miner/Data Analysis button.
In the DataMiner window, select New Workbook.
Select the NPI data category on the left side of the window, then double-click the CAD-BOM data source.
Select the data you want to display using the tabs and buttons on the right side of the window, then select the Execute button.
Tip
When using a Look Up dialog to locate and select data (Customer, for example), enter an asterisk * character to return all customers. When you use subsequent buttons such as Assembly, Process, and Batch, only assemblies, processes, and batches are returned for the selected customer.
The data table is displayed.
Select the Pivot Table tab at the bottom of the window.
Drag the Customer field to the area labeled Drop Row Fields Here.
Drag the Internal Part Number field into the area labeled Drop Row Fields Here.
Drag the Actual Quantity field into the area labeled Drop Data Items Here.
A new column is created and the Actual Quantity Total is summarized automatically at the bottom of the column and labeled (in this example) Aegis Total.
Right-click the Data area to specify how you want to to summarize the data (Min, Max, Sum, Average, and so on). For this example, we selected Sum.
Sort and filter each field individually as needed by right-clicking a column and selecting the desired option.
Continue to add fields to the Columns area to display the returned data any way you want.
The pivot table summarizes all data at the end of each row/column automatically.