Create a formula column in a Data View
With Microsoft Office SharePoint Designer 2007, you can easily create a column that displays the result of a calculation that was performed on other data in a data source.
In Office SharePoint Designer 2007, you use the XPath Expression Builder to create a formula column. XPath (XML Path Language) is a language for locating and processing information in an XML document. The XPath Expression Builder provides IntelliSense for XPath, making it possible for both novice and advanced users to create XPath expressions that perform complex calculations on data.
For example, Northwind Traders, a gourmet food distribution company, tracks their inventory in an XML file. They know how much inventory they have and the price of each unit, but they want their Data View to display the value of their inventory for each unit. You suggest that they create a formula column that displays the result of multiplying the units in stock by the unit price. Now Northwind Traders can quickly and easily access the value of their inventory for each item in stock.
To create the sample data and Data View that are used in the example, or to learn more about Data Views, see Create a Data View.
Open the page that contains the Data View to which you want to add a formula column.
Right-click the Data View, and then click Show Common Control Tasks. In the Common Data View Tasks list, click Edit Columns.
Tip You can also click the Data View and then click the arrow in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.
In the Edit Columns dialog box, in the Available fields list, click Add Formula Column, and then click Add.
The XPath Expression Builder dialog box opens. You can use the XPath Expression Builder to help build expressions. The XPath Expression Builder offers easy access to the names of the fields in your data source, as well as to many of the built-in functions that are available to you when you write XPath expressions.
In the XPath Expression Builder dialog box, under Select a field to insert, double-click the field that you want to insert in the expression. To insert the full path of the field, hold down CTRL while you double-click the field.
For example, to create the formula column that Northwind Traders wants in order to display the value of their inventory, double-click UnitsInStock. UnitsInStock appears in the Edit the XPath expression box.
In the Edit the XPath expression box, position the insertion point immediately after the field name, and then press SPACEBAR.
The IntelliSense list of XPath operators appears. IntelliSense for XPath helps you by providing a list of available fields or functions that are valid in the context of the expression.
In the IntelliSense list of operators, double-click the operator that you want.
In the example, because Northwind Traders wants to multiply UnitsInStock by UnitPrice, in the operator list, double-click the asterisk (*).
Press SPACEBAR. An IntelliSense list of available fields appears.
In the list of available fields, double-click the field that you want.
To create the formula column in the Northwind Traders example, double-click UnitPrice.
The final expression looks like the following.
At the bottom of the XPath Expression Builder, you can see a preview of the results of the data in the formula column as you build the expression in the Edit the XPath expression box.
Click OK twice.
A new formula column appears in the Data View.
Note By default, the column header is the expression that you created in the Edit the XPath expression box in the XPath Expression Builder (for example, UnitsInStock * UnitPrice). If you want to replace the default header, you can select the column header and then type the text that you want.