Home > Cloud Cruiser 3 > Viewing financial data > Data analysis in Microsoft Excel > Analyzing charge data

Analyzing charge data

Table of contents
No headers

The procedure in Connecting Excel joins data about usage charges to data about customer accounts based on the full account structure. This enables you to aggregate charge data at any level in the hierarchy. For example, if you want to analyze total charges by day aggregated to the highest level in the structure, you can perform the following procedure.

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.

Last modified

Tags

This page has no custom tags.

Classifications

This page has no classifications.
© Copyright 2018 Hewlett Packard Enterprise Development LP