Defining Volatile Function Cache Lifetime in Excel Web Services

Sometimes you might need to configure the cache lifetime of the calculated values to days, hours, or to be handled during the session. This really depends on how frequent your users update the data. Some of my users change the data weekly and some they want the data to be recalculated every time to view the workbook. Unfortunately cache lifetime is not set per workbook but instead on each Trusted Location.

To configure cache lifetime, follow these steps:

  1. Launch your Central Administration site from the start menu

  2. Browse to the SSP site

  3. Under the Excel Services Settings click on Trusted file locations

    image

  4. Open any of your Trusted Locations by clicking on it and scroll down to the Calculation Behavior section.

    image

    The default value is 300 seconds.

    For example you can set it to:

    1. -1: values are calculated once on load
    2. 0: always calculated
    3. 1 through 20773600 seconds (24 days)

You can also ask your users to use the Calculate Workbook to recalculate the workbook values on the spot.

image

My advice to you is when gathering requirements from stake holders make sure to understand how often they update their files. This way you can deliver good performance and on the same time make sure to setup the cache lifetime in a satisfying way.