Microsoft Office Tutorials and References
In Depth Information
Working with Workbook Parameters
Working with Workbook Parameters
You’ve learned about various ways to interact with the workbook, but none of them let you change an
actual value in a cell with user input. Because authoring workbooks in the browser is not a supported
scenario for this release of Excel Services, you cannot simply type into the worksheet cells in the browser.
There are some scenarios, however, that require you to change a few select cells that serve as input into
the workbook’s calculation mode. Changing these values recalculates the workbook and returns the
results. The workbook Parameters feature in Excel Services enables you to use the browser-based
interface to interact with complex calculation models.
The Parameters feature lets you define certain cells in the workbook as parameters. You can change the
values in these cells when you view the workbook in the browser. You can use workbook parameters to
do the following:
Create input for calculation models.
Drive filtering in the context of a dashboard.
This section focuses on the first use case. The second is covered in Chapter 12.
There are two steps to working with Workbook Parameters:
1.
Define the parameters.
2.
Use the parameters through the Parameters task pane in the browser.
Defining Workbook Parameters
Workbook parameters are basically single cells that have been given a name and defined as parameters
in the Excel Services Options dialog box. Not every cell can be used as a workbook parameter. The
primary requirement is for the cell to contain only a value. Specifically, this means that the cell cannot
contain a formula. There are a few more restrictions on which cells can be used as workbook parameters.
Following are the primary ones:
The cell can only contain a single value (not a formula).
The cell cannot be inside of a table.
The cell cannot be in the rows, columns, or data region of a PivotTable. (They can, however, be
on the report filter, also known as the PivotTable page field .)
The cell name must be a global in scope and not local (for example, sheet-specific).
In general, only those names that can be used as parameters are available in the parameter-definition
dialog box.
A special type of parameter is one that is associated with a PivotTable page field. You learn how to use
this parameter type in Chapter 12.
To define a cell as a workbook parameter, you must first give the cell a name, and then add it to the
workbook parameter list. You can do this through the Excel Services Options dialog box or using the Excel
Object Model. In the following Try It Out, you use the sample workbook to define a workbook parameter.
Search JabSto ::




Custom Search