Share via

Walkthrough: Create a Mortgage Calculator with the InfoPath Form Web Part

  • Article

Applies to: SharePoint Server 2010

In this article
Publishing the Loan Amortization Workbook to Excel Services
Designing and Publishing the Form
Creating a Web Part Page and Configuring the Web Parts
Connecting the Web Parts
Testing the Mortgage Calculator

This simple mortgage calculator is a great example of an application that uses a browser-enabled InfoPath form hosted in the InfoPath Form Web Part for entering and validating data, and an Excel workbook hosted in the Excel Web Access Web Part to perform complex calculations. By connecting the Web Parts, you can send the data that a user enters in the form to the Excel workbook.

This walkthrough consists of the following high-level steps:

  • Publishing the Loan Amortization sample template from Microsoft Excel 2010 to a document library in a site on Microsoft SharePoint Server 2010 using Excel Services.

  • Designing and publishing a browser-enabled form from Microsoft InfoPath 2010 to a document library in a site on SharePoint Server 2010.

  • Creating a Web Part page, adding the InfoPath Form Web Part and Excel Web Access Web Part to the page, and then defining the connections between the two Web Parts.

  • Testing the mortgage calculator page.

Follow the steps below to create a mortgage calculator on a Web Part page with the InfoPath and Excel Web Parts.

Publishing the Loan Amortization Workbook to Excel Services

To provide a workbook to perform calculations, publish a workbook based on the Loan Amortization sample template to a document library on SharePoint Server 2010 using Excel Services. To enable the workbook to work correctly with the Excel Web Access Web Part, you must remove protection and data validation from the workbook before publishing it. To provide a way to send data from the InfoPath Form Web Part to the workbook, you configure which cells to use as parameters for receiving data.

To publish the Loan Amortization workbook to Excel Services

  1. Open Excel 2010, and then click the File tab.

  2. Click New, click Sample Templates, and then double-click Loan Amortization.

  3. On the Review tab, click Unprotect Sheet.

  4. On the Home tab, click Find & Select, and then click Go to Special.

  5. Click Data Validation, click All, and then click OK. This selects all of the cells in the workbook that contain data validation.

  6. On the Data tab, click Data Validation.

  7. At the prompt The selection contains more than one type of validation. Erase current settings and continue?, click OK.

  8. In the Data Validation dialog box, click Clear All, and then click OK.

  9. Click the File tab, click Save & Send, click Save to SharePoint, and then click the Browse for a location button.

  10. Type the URL for a document library in the SharePoint Server 2010 site where you want to create the mortgage calculator Web Part page.

  11. Make sure the Open with Excel in the browser check box is selected, and then click the Publish Options button.

  12. On the Parameters tab, click Add, and then select the following named ranges in the Add Parameters dialog box: Interest_Rate, Loan_Amount, Loan_Start, Loan_Years, and Num_Pmt_Per_Year.

  13. Click OK twice, enter a name for the workbook, and then click Save to publish the workbook to the SharePoint document library with Excel Services.

In the next section, you will create and publish a browser-enabled form that will be used to send data from the InfoPath Form Web Part.

Designing and Publishing the Form

To provide a browser-enabled form for entering data to send to the Loan Amortization workbook, use InfoPath 2010 to create the form described in the following steps, and then publish it to a document library in a site on SharePoint Server 2010.

To design and publish the form

  1. Open InfoPath 2010, and double-click the Blank Form form template under Popular Form Templates on the New tab.

  2. In the Fields task pane, right-click myFields, click Add, type InterestRate, and then click OK. Repeat this step to add the following four fields: LoanAmount, LoanStart, LoanYears, and NumPmtPerYear.

  3. Drag the five fields from the Fields task pane to add them as Text Box controls to the form.

  4. Add a Button control to the form, and on the Properties tab in the ribbon change the Label of the button to Send Data.

  5. On the Properties tab, click Rules.

  6. In the Rules task pane, click New, and then click Action.

  7. Click Add, click Send data to Web Part, and then click Property Promotion.

  8. Click Add next to the list that is below the text The fields below will be available as SharePoint Web Part connection parameters.

  9. Select one of the five fields, set Parameter type to Output, and then click OK. Repeat this step for each of the remaining fields, and then click OK to close the Form Options dialog box.

  10. Click OK to close the Rule Details dialog box.

  11. Click the File tab, click Publish, and then click SharePoint Server.

  12. Name the form LoanForm, and then click Save.

  13. Step through the Publishing Wizard to publish the form template in a form library on the same SharePoint Server 2010 site where you published the Loan Amortization workbook.

In the next section, you will create a Web Part page, and add the InfoPath Form Web Part and the Excel Web Access Web Part to the page.

Creating a Web Part Page and Configuring the Web Parts

The following steps show how to create a Web Part page that contains the InfoPath Form Web Part and Excel Web Access Web Part, and how to configure the Web Parts to display the form and Loan Amortization workbook you published in the previous steps.

To create the Web Part page

  1. Open the SharePoint Server 2010 site where you published the Loan Amortization workbook and the InfoPath browser-enabled form.

  2. Click Site Actions, and then click More Options.

  3. Under Filter By, click Page, click Web Part Page, and then click Create.

  4. Name the page MortgageCalculator, choose the Layout Template you want to work with, specify the Document Library where you want to save the Web Part Page, and then click Create. The new page will open in the browser.

  5. Click Add a Web Part in the zone on the page where you want to insert the InfoPath Form Web Part.

  6. In the Categories list, click Forms.

  7. In the Web Parts list, click InfoPath Form Web Part, and then click Add.

  8. Click the link in the Web Part to open the tool pane.

  9. In the List or Library drop-down list of the tool pane, select the form library where you published your form, and then click OK at the bottom of the tool pane.

  10. Click Add a Web Part in the zone on the page where you want to insert the Excel Web Access Web Part.

  11. In the Categories list, click Business Data.

  12. In the Web Parts list, click Excel Web Access, and then click Add.

  13. Click the link in the Web Part to open the tool pane.

  14. Type the URL to the Loan Amortization workbook you published to Excel Services in the Workbook box in the tool pane, or click the button next to the Workbook box, navigate to the Loan Amortization workbook in the Select an Asset dialog box, and then click OK.

  15. Under Navigation and Interactivity, clear the Display Parameters Task Pane check box, and then click OK at the bottom of the tool pane.

Connecting the Web Parts

The following steps show how to specify the connection parameters between the Web Parts. You can use either edit the Web Part page in the browser and use the Connections command, or you can edit the page in SharePoint Designer 2010 and use its Add Connection command.

To connect the Web Parts by editing the page in the browser

  1. If the Web Part page is not already open for editing in the browser, click Edit Page on the Page tab of the ribbon.

  2. Click the drop-down arrow in the upper-right corner of the InfoPath Form Web Part, point to Connections, point to Send Data To, and then click Excel Web Access.

  3. In the Configure Connection dialog box, select Interest Rate in the Provider Field Name box, select Interest_Rate in the Consumer Field Name box, and then click Finish.

  4. Repeat steps 2 and 3 for the remaining four connection parameters (Loan Amount, Loan Start, Loan Years, and Num Pmt Per Year), and then click Finish after specifying each one.

  5. Click Stop Editing on the Page tab to save the page.

To connect the Web Parts by editing the page in SharePoint Designer

  1. Open the Web Part page in the browser, on the Page tab click the drop-down arrow under the Edit Page button, and then click Edit in SharePoint Designer.

  2. Right-click the InfoPath Form Web Part, and then click Add Connection.

  3. In the first page of the Web Part Connection Wizard dialog box, select Send Data To, and then click Next.

  4. Select Connect to a Web Part on this page, and then click Next.

  5. In the Target Web Part drop-down list, select Excel Web Access, and in the Target action drop-down list, select Get Multiple Filter Values From, and then click Next.

  6. Map each of the five fields in the InfoPath form to the corresponding cell in the Excel workbook, and then click Next.

  7. Click Finish, and then save the Web Part page.

Testing the Mortgage Calculator

Now that all the components of the mortgage calculator are on the Web Part page, you can test it.

To test the Mortgage Calculator

  1. Open the Web Part page that contains the two Web Parts in the browser.

  2. Enter values in the form displayed in the InfoPath Form Web Part, and then click the Send Data button.

The values entered in the InfoPath Form Web Part should be sent to the Excel Web Access Web Part, and the calculations in the workbook should be performed on those values.