Analyzing charge data

To view total charges by day rolled up to the top level of your account structure

1. In Excel, create a PivotTable based on your external data source that joins charge summaries and account information.
   If you have just completed one of the previous Excel connection procedures then you already have this PivotTable and can skip to step 2. If you have previously performed one of these procedures but no longer have the PivotTable in your Excel workbook, perform the following steps:
   ​Click Insert > Tables > PivotTable, select Use an external data source and click Choose Connection, then select the data source you created. In Excel 2013, this is <DBserverName> ccdb Multiple Tables and in Excel 2010, it is the name of the query you saved from Microsoft Query. Then click Open, and in the Create PivotTable dialog box click OK.
2. In the PivotTable Fields pane to the right, find the field from the ReportAccountStruct0 view that holds the description for customers at the top level of your account structure and drag it to the ROWS box below.
   For each level in your default account structure, there is a < levelName > ID field and a < levelName > Desc field. The former holds the part of a customer’s account ID at that level and the latter holds the corresponding description. For example, if the top level of your account structure is “Service Provider,” drag the Service Provider Desc field to the ROWS box. This will give you one row per customer at the top level of your hierarchy, with the description displayed. You could instead display the partial account ID by dragging Service Provider ID .
3. Find the Accounting Date field from the ReportSummary view and drag it to the COLUMNS box.
4. Drag the Total Amount field to the VALUES box.

Your worksheet now shows all charge data in the system for every top-level customer (in this example, service provider) broken down by each accounting day loaded. You can further break down this charge data by adding other fields, such as Resource Group Title, Resource Description, and even lower levels of the account structure, to the ROWS and COLUMNS boxes. See the documentation for the PivotTable feature of your version of Microsoft Excel for information about its capabilities and how to use them.