How to: Schedule a Workbook Calculation by using the Job Submission Task Pane

  • Article

The Job Submission task pane add-in for Microsoft Office Excel 2007 allows workbooks to be calculated on a Windows Compute Cluster Server (CCS) 2003. This topic describes how you can schedule Excel 2007 workbooks for calculation at a later date, on a one-time or recurring basis, or immediately.

You can use the Job Submission task pane add-in to schedule Excel workbooks for a one-time calculation on a compute cluster, for example, to perform a long running calculation after hours so the results can be ready for the next day. You can also schedule workbooks for calculation on a recurring basis, for example, for weekly, monthly, or quarterly reports or submitted to run for immediate calculation.

In this scenario, the scheduled workbooks are treated as template workbooks. Within Microsoft Office SharePoint Server 2007, the workbook is opened and the calculations are performed using Excel Services, but the results of the calculations are not saved to the original workbook. Instead, the results are saved to a new workbook in a SharePoint library. This allows the workbooks to be used for recurring calculations, saving the latest results to a new file while leaving the original workbook unchanged.

There are two steps to scheduling a workbook for calculation on a compute cluster. The first is to prepare and schedule the job using the Job Submission task pane. The second is to submit the job using the custom SharePoint Web form.

Note

For more information about how to submit a job using a custom Web form, see How to: Schedule or Submit a Workbook by using a Custom Web Form.

Scheduling a Workbook for Calculation

You can schedule a workbook for calculation at a later time. Following are the steps to take.

To prepare the scheduled job using the Job Submission task pane

  1. Open a workbook from a trusted SharePoint document library, or save a new or existing workbook to the SharePoint document library that is a trusted file location, as shown in Figure 1.

    Figure 1. Saving a workbook to a SharePoint site

    Excel Services Options button

    Note

    For information about how to create a trusted file location, see "Installing and Configuring Office SharePoint Server 2007" in Installing a Compute Cluster for the Job Submission Sample.

  2. In the Job Submission task pane, click the Configuration tab.

    Figure 2. Configuration tab

    Job Submission sample architecture

  3. Click Apply Defaults.

  4. When prompted, enter the name of the compute cluster head node.

  5. Edit the Scheduling Page URL to show the correct scheduling page for the SharePoint document library you are using. The Scheduling Page URL is the URL to your site, for example:

    https://servername/sites/sitename

  6. In the Job Submission task pane, click the Basic Settings tab, as shown in Figure 3.

    Figure 3. Basic Settings tab

    Selected range captured with Capture Range button

  7. Set the Calculation Input Range section. These are the cells that are used as inputs to the workbook.

  8. Set the Calculation Output Range section. These are the cells that contain the calculated results of the workbook.

  9. Set the Data Input Range section. These are the cells that contain a series of inputs to calculate.

  10. Set the Result Start Location section. This is the location within the workbook where successive results are stored.

  11. Enter the User Name and Password that you want to use to submit the calculation job.

    Note

    This is a required step, unless you are using Kerberos. Windows CCS 2003 uses Active Directory and requires authentication to use the cluster. You can use Kerberos to remove the need for explicit credentials.

  12. In the Job Submission task pane, click the Advanced Settings tab.

  13. Select the Auto populate XML file path check box, as shown in Figure 4. This automatically fills in the path to the XML job file.

    Figure 4. Advanced Settings tab

    Advanced Settings tab

  14. Click Create XML File (Without submitting job). This creates the XML job file for this workbook and stores it on the compute cluster head node.

  15. Click Schedule Job.

    Figure 5. Schedule Job button

    Schedule Job button

    This displays the Submit a Job to Compute Cluster Server SharePoint Web form. After you have filled out the Web form, the job is ready to schedule on the compute cluster, or to send to the compute cluster immediately. Many of the entries on the Web form are already filled out by the Job Submission task pane add-in, and can be left unchanged.

To submit the job using the SharePoint Web form

  1. In the Excel Services section (see Figure 6), do the following:

    Figure 6. Custom Web form

    Custom Web form

    1. Enter the location of the XML job file inInput Xml Location. This is the UNC path to the XML job file stored on the compute cluster head node. This value was set in the CCS Working Directory entry on the Configuration tab in the Job Submission task pane.

    2. Select whether the workbook job should be divided evenly among the compute nodes, or select and enter an Input Divisor to divide the compute job into smaller tasks. You can find a description of the Input Divisor in "Preparing the Job" in Job Submission Task Pane Add-in.

  2. In the Schedule section (see Figure 6), enter the Start Date and time, and the End Date and time. For recurring events, enter the Frequency in which to calculate the workbook and the ****.

  3. In the Template Configuration section (see Figure 6), do the following:

    1. Enter the Template Location. This is the URL path to the workbook you want to calculate.

    2. Enter the Result Row Start and the Result Column Start. This is the location within the workbook where successive results are stored. These values were set in the Job Submission task pane previously, and should not need to be changed.

    3. To create a header row above the output results in the workbook, select Include Header.

    4. Enter the Output Location. This is the URL path to the new workbook that will contain the results of the calculations.

    5. Select the Location Type.

  4. In the Compute Cluster section (see Figure 6), do the following:

    1. Enter the Compute Cluster Name.

    2. Enter the User Name and Password to use to submit the compute job to the compute cluster.

    3. To schedule the workbook for later calculation, click Schedule Job. Or, to submit the workbook for a scheduled calculation that begins immediately, click Submit Job and Display.

      Note

      The Submit Job and Display functionality in the Web form is different from the Job Submission task pane functionality. With the task pane, the calculations are returned to the open workbook in Excel 2007. With Submit Job and Display functionality, the workbook is submitted to the compute cluster immediately, but the results are returned in a new workbook that is stored in the SharePoint document library.

See Also

Concepts

Performing a Parametric Sweep using the Job Submission Sample
Job Submission Task Pane Add-in
Overview of Excel Calculation Services on Compute Cluster Server API
Dependent Technology Overview for the Job Submission Sample
Installing a Compute Cluster for the Job Submission Sample
Manually Installing the Job Submission Sample
Additional Resources