Setting up the workbook

You can add views to Excel workbooks, thus creating briefing books for Excel users to specify, review, and analyze data. Using Excel, you can add multiple views to the workbook, whereas creating workbooks through View Manager only places a single view in the workbook.

Each time an Excel workbook is opened; the views definitions are checked for changes and updated to use any new settings. All changes to view definitions are made in View Manager.

The following procedure provides a quick overview of how to create a workbook before going into the details.

If you must stop work on a workbook and leave it open, you can verify that your view definitions have not changed while you were away by using the Reload View Definition command.

Note: Do not use merged cells in a worksheet that contains an Excel Services view. You can get unpredictable results.
  1. In View Manager, define the views.
  2. Start Excel and specify the URL for the Decision service via the User Preferences command on the CPM Views menu. This enables you to connect to the server before performing administrative tasks such as inserting views into the worksheet.
  3. Export any views to spreadsheets that are in separate workbooks. If several views are in one workbook, export only the first view. Before inserting a view into a worksheet, you are prompted to sign in to the server. You can insert additional views via the Excel Services toolbar commands. Exporting to a view will leave the workbook open in Excel and you will already be in the design mode, so you can skip to step 6.
  4. If you did not export any views, open Excel and connect to the server.
  5. Click Design Mode for Views.
  6. Insert the appropriate view into the worksheet.
  7. Edit the worksheet as required for your application. You can add graphics and charts, format cells, add formulas, and use any other functionality available in Excel.
  8. If you are creating a view that is fairly static (no pivoting or rotation, for example), insert any required rows or columns into the data areas. You can also add formulas to these elements.
  9. Repeat steps 5-7 for other views. You can use multiple worksheets and insert multiple views on each worksheet.
  10. Preserve the formatting of any cell ranges in the data area using the Preserve Range Properties command.
  11. Set any necessary workbook properties using the Workbook Properties command.
  12. Clear the contents of the views (without removing the links to the database) using the Clear View command. This clears all the views on the worksheet and prevents display of your data and dimension members until users have passed security.
  13. Optionally, create work areas for users outside the data areas, use the Protection tab on Excel's Format Cells dialog box to remove protection for those cells. Clear the Locked option.
  14. Protect each worksheet using the Protection command. This prevents changes to the worksheet. We recommend using a password, and that the password must be the same for each worksheet in the workbook. Protecting a worksheet this way will not prevent users from specifying data in cells that allow data entry or cells that you have unlocked.
  15. Save the workbook. You should still be in the design mode.
  16. Distribute your workbooks using the method that best suits your needs.