An introduction to PivotTables for data manipulation
There are a number of options on the Dashboard to download data for manipulation and perhaps one of the easiest, most effective ways to summarize, analyze and report on this information is through PivotTables.
As such the following section gives an introduction of using the Asset Tracking XML Data Dump, containing both hardware and software information, to create a software only report.
The first stage is to retrieve the data from the Dashboard, from the asset information go to Reports, Asset Tracking, XML Data Dump and select either All Clients or a specific Client from the drop-down. Save the file when prompted and open in a spreadsheet program, in this example Excel 2007.
Once the information loads highlight range of data to include, where no data is highlighted the whole table is selected, then go to Insert and from Tables select PivotTable,PivotTable.
In the Create PivotTable wizard check the Select a table or range settings and Choose where you want the PivotTable to be placed, in a new or existing worksheet clicking OK to apply.
In the following example the full table was selected opening the PivotTable as a New Worksheet.
Each column of the Asset Tracking XML Data Dump contains a heading which is displayed under the PivotTable Field List in the PivotTable worksheet and it is possible to amend this column heading to a more meaningful name, either by editing the column heading before or after the PivotTable is created.
To begin populating the PivotTable select the required information from Choose fields to add to reports.
Right-click on the field name for the area selection context menu Report Filter, Column Labels, Row Labels, Values - otherwise when simply selecting the field it will appear under Row Labels. As such where the field is not in the required area, it is possible to simply drag it to the correct area.
When adding the required fields, it is useful to refer back to the source spreadsheet for details on the information each column contains.
After the PivotTable is populated it can be edited via PivotTable Options, available by right-clicking in the table. Its options include the ability to amend the Layout & Format, remove or add Totals & Filters, select the information to Display, determine what is included when Printing and how the Data for the table is stored. Each field column may also be edited by right-clicking on the column heading.
The following example software report example displays the chassis, device and discovered software for each machine with the columns headings renamed for clarity. New naming conventions: Chassis to Device Type, name2 to Device Name and name5 to Software.
Once the report is formatted including additional details such as logos it can then be printed off for presentation to the customer.
Please note, the option to download the XML or SQL Data Dump is only available for contracted customers.