Connecting Excel

1. Launch Excel and create a new blank workbook.
2. Click Data > Get External Data > From Other Sources > From Microsoft Query.
3. Double-click <New Data Source>.
4. In the Create New Data Source dialog box, give your new data source a name and select the SQL Server driver, then click Connect.
   The SQL Server Login dialog box appears.
5. In the Server field, enter the network computer name of the computer hosting the Cloud Cruiser database.
6. Deselect Use Trusted Connection and enter Cloud Cruiser’s database username as the Login ID.
   This is the Application User that you entered on the Database Settings page of the Cloud Cruiser installer. The default Application User is ccadmin . If you do not know this username and its password, ask your database administrator to provide them to you or to create another account with read access to the Cloud Cruiser database.
7. Enter the password for that user, then click Options.
8. Select the ccdb database, then click OK twice to close both the SQL Server Login and Create New Data Source dialog boxes.
9. In the Choose Data Source dialog box, select your new data source and click OK.
10. In the Query Wizard dialog box, select the ReportAccountStruct0 view from the list of available tables without expanding it, then click the arrow to move all of its columns into your query. Do the same with the ReportSummary view and click Next.
    The ReportAccountStruct0 view contains data about customer accounts based on the full account structure. If you want to view data in Excel based on an alternate account view you have created, instead select the ReportAccountStruct<view_ID> database view where <view_ID> is the numeric account structure ID.
11. If you receive an error message saying that you must join tables manually, click OK to dismiss it.
12. In the top pane of the query window, drag the Account UID column of one view onto the Account UID column of the other view.
    This joins the two views, creating a combined record of values from each wherever the Account UID in one view matches an Account UID in the other.
13. Choose File > Return Data to Microsoft Excel.
14. In the Import Data dialog box, choose PivotTable Report and click OK.
    It might take some time for Excel to load the data from this query.
15. Click Analyze > Relationships to define how to join the summary data to hierarchical account IDs and descriptions.
16. In the Manage Relationships dialog box, click New.
17. In the Create Relationship dialog box, make the following selections:
    • Table:ReportSummary​
    • Column (Foreign): Account UID​
    • Related Table: ReportAccountStruct0​
    • Related Column (Primary): Account UID
18. Click OK and then close the Manage Relationships dialog box.

You now have a PivotTable based on a join of charge summaries and account information. For instructions for using this table to analyze your data, see Analyzing charge data.

1. Launch Excel and create a new blank workbook.
2. Click Data > Get External Data > From Other Sources > From SQL Server.
   The Data Connection Wizard opens.
3. In Server name, enter the network computer name of the computer hosting the Cloud Cruiser database.
4. Select Use the following User Name and Password and enter the user name and password of the Cloud Cruiser database account, then click Next.
   This is the Application User and Password that you entered on the Database Settings page of the Cloud Cruiser installer. The default Application User is ccadmin. If you do not know these credentials, ask your database administrator to provide them to you or to create another account with read access to the Cloud Cruiser database.
5. Select the ccdb database.
6. Select the Enable selection of multiple tables option, then select the following views from the list:
   • ReportSummary
   • ReportAccountStruct0
     
     The ReportAccountStruct0 view contains data about customer accounts based on the full account structure. If you want to view data in Excel based on an alternate account view you have created, instead select the ReportAccountStruct<view_ID> database view where <view_ID> is the numeric account structure ID.
7. Click Finish.
8. In the Import Data dialog box, choose PivotTable Report and click OK.
   It might take some time for Excel to load the data from this query.
9. Click Analyze > Relationships to define how to join the summary data to hierarchical account IDs and descriptions.
10. In the Manage Relationships dialog box, click New .
11. In the Create Relationship dialog box, make the following selections:
    • Table:ReportSummary​
    • Column (Foreign): Account UID​
    • Related Table: ReportAccountStruct0​
    • Related Column (Primary): Account UID
      
      This joins the two views, creating a combined record of values from each wherever the Account UID in one view matches an Account UID in the other.
12. Click OK and then close the Manage Relationships dialog box.

You now have a PivotTable based on a join of charge summaries and account information. For instructions for using this table to analyze your data, see Analyzing charge data.