Excel Object Worksheets and Data

In CDM, every Excel object that is added to a report has a Database worksheet and one or more data worksheets.

A Database worksheet is used to contain values retrieved from an OLAP or relational database source or from an external Excel file source. Database worksheets are read-only and cannot be formatted or modified by users.

A data worksheet can be used to present data values in the report. The data worksheet is originally named Sheet1, but you can rename it if necessary.

You can use formulas to reference data contained in the Database worksheet. You can also include text or data in the data worksheet. You can add extensive formatting and formulas to enhance presentation of the information and perform further calculations. You can also add additional worksheets as required.

To switch between worksheets, click the worksheet tab in the Excel object.

You can use the functionality of Microsoft™ Excel formulas in an Excel object. There are hundreds of formulas available.

Add a Worksheet to an Excel Object

You can add another worksheet to an Excel object in CDM.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Right-click any worksheet tab in the Excel object and insert a new worksheet.
5. Select the newly added worksheet from the list, double-click the worksheet tab, and enter a new name.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Change the Order of Worksheets in an Excel Object

You can arrange the order of worksheets in an Excel object in CDM.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. To select the worksheet tab that you want to move, click and hold the left mouse button and drag the worksheet tab to the desired location.
5. Release the left mouse button.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Delete a Worksheet from an Excel Object

If you no longer need a worksheet in an Excel object in CDM, you can delete it.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Right-click the worksheet tab in the Excel object and delete. Ensure that the worksheet that you are deleting is not a data worksheet such as Sheet1. Data worksheets must be edited or deleted from the Manage Data Queries window.
5. Save your changes to the Excel object.
6. Click the Default View Mode icon in the status bar.
7. Save your changes to the report and check in the Excel object.

Protect a Worksheet in an Excel Object

There might be certain parts of a report that you do not want other users to modify, for example, compliance checklists. You can use cell locking in an Excel object in CDM and then apply password protection to prevent modification.

If you want to protect a worksheet in an Excel object, always use a password; otherwise, users can unprotect the worksheet and cells are not locked.

To protect a worksheet in an Excel object:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Find the protection area and enter a password to further protect the worksheet. To customize other actions that a user can perform while the worksheet is locked, select the appropriate checkboxes.
5. At the prompt that appears, enter the password again. Apply the settings to the worksheet.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Lock and Unlock Cells in an Excel Object

You can choose which cells can be edited when a worksheet is protected in an Excel object in CDM.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Select the cells that you want to lock or unlock.
5. Find the protection area and specify locking for the selected cells.

   Important: Cell protection is not yet enabled. For these settings to take effect, the worksheet must be protected.

6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

View the Source of a Cell Value in an Excel Object

You can view the value in a cell in the data worksheet of an Excel object in CDM from an Excel calculation, a formula that references data found in the Database worksheet, or a number manually entered by a user. The data worksheet is originally named Sheet1, but you can rename it if necessary.

To view the source of a cell value in an Excel object:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Click the cell containing the value that you want to investigate.
5. Look at the Formula bar to view the source of the cell value.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Reference a Cell Value in a Range in an Excel Object

You can use a VLOOKUP formula in an Excel object in CDM to reference and display a single value in the data worksheet from a range of cell values found in the Database worksheet.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Switch to the Database worksheet and determine the data value that you want to reference. Make a note of the cell coordinates for the data that you want.
5. Switch to the data worksheet. The data worksheet is originally named Sheet1, but you can rename it if necessary.
6. Determine what cell you want to display the data query value in.
7. Type an Excel VLOOKUP formula that calls on the cell values in the Database worksheet.

   =VLOOKUP ("Cash 5", Database ! A1:C11, 2, FALSE)

   Where:

   • The equal sign (=) indicates the start of the formula.
   • VLOOKUP is the name of the formula being used.
   • Cash 5 is the account name being looked up. It must be contained in quotes.
   • Database ! is the name of the worksheet being referenced. The exclamation point (!) identifies Database as another worksheet.
   • A1:C11 is the range of cells to be referenced in the Database worksheet.
   • 2 refers to the second column in the range of cells. The formula returns a value for the Cash 5 account from the second column.
   • FALSE returns an exact value match. TRUE returns either an approximate or exact match.
8. Press Enter.
9. Save your changes to the Excel object.
10. Click the Default View Mode icon in the status bar.
11. Save your changes to the report and check in the Excel object.

Reference a Cell Value in Another Worksheet in an Excel Object

You can create a reference in a data worksheet in an Excel object in CDM to a cell value found in the Database worksheet.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Switch to the Database worksheet and determine the data value that you want to reference. Make a note of the cell coordinates for the data that you want.
5. Switch to the data worksheet. The data worksheet is originally named Sheet1, but you can rename it if necessary.
6. Determine what cell you want to display the OLAP or relational data value in.
7. Type a cell reference formula that calls on the cell values in the Database worksheet.

   =Database ! C7

   Where:

   • The equal sign (=) indicates the start of the formula.
   • Database is the name of the worksheet being referenced.
   • Database ! is the name of the worksheet being referenced. The exclamation point (!) identifies Database as another worksheet.
   • C7 is the cell to be referenced in the Database worksheet.
8. Press Enter.
9. Save your changes to the Excel object.
10. Click the Default View Mode icon in the status bar.
11. Save your changes to the report and check in the Excel object.

Change the Weighting of Referenced Data in an Excel Object

When source data is imported into an Excel object in CDM, the value might be weighted as positive or negative as it is stored in the external source, for example, relational. You can use an Excel formula to adjust the weighting.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Switch to the Database worksheet and determine the data value that you want to reference. Make a note of the cell coordinates for the data that you want.
5. Switch to the data worksheet. The data worksheet is originally named Sheet1, but you can rename it if necessary.
6. Determine what cell you want to display the OLAP or relational data value in.
7. Type a cell reference formula that calls on the cell values in the Database worksheet and also changes the weighting to a positive value.

   = ( Database ! C7 ) * -1

   Where:

   • The equal sign (=) indicates the start of the formula.
   • Database ! is the name of the worksheet being referenced. The exclamation point (!) identifies Database as another worksheet.
   • C7 is the cell to be referenced in the Database worksheet.
   • *-1 multiplies the referenced value by a negative and reverses the value weighting.
8. Press Enter.
9. Save your changes to the Excel object.
10. Click the Default View Mode icon in the status bar.
11. Save your changes to the report and check in the Excel object.

Add a Chart to an Excel Object

You can add a chart to an Excel object in CDM.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Determine the data source for the chart:
   1. If required, import data into an Excel object.
   2. Review the data to determine what you want to display in chart format.
5. In the Excel object, click the worksheet that will contain the chart.
6. From the Excel menu, click Insert, then in the Charts section, select the type of chart you want to insert.
7. Configure the chart according to how you want it displayed, for example, design, layout, and format.
8. Select data for the chart.
9. From the Database worksheet or a data worksheet, select the source data range that you want to use. The data worksheet is originally named Sheet1, but you can rename it if necessary.
10. If required, add or edit legend entries and axis labels.
11. Save your changes to the Excel object.
12. Click the Default View Mode icon in the status bar.
13. Save your changes to the report and check in the Excel object.