A calculated fi eld in your dataset is equivalent to adding an expression to the SELECT clause in a Transact-SQL (T-SQL) statement . In fact, ideally you create a derived column in your da-taset query to get the desired value into the dada-taset from the data source, but in the case when you have no control over the source, such as when you’re accessing a view or a stored procedure, you can create the calculation you need .
In this procedure, you add a calculated fi eld to a dataset, and then use the calculated fi eld in a data region .
Add a calculated fi eld
1. Click Start, select All Programs, Microsoft SQL Server 2008, and click SQL Server Business Intelligence Development Studio .
2. On the File menu, select Open, and click Project/Solution .
3. In the Open Project dialog box, navigate to the Microsoft Press\Rs2008sbs\Chap05 folder in your Documents folder, and double-click Sales .sln .
4. In Solution Explorer, double-click Reseller Sales Margin Analysis .rdl .
This report contains a dataset that includes SalesAmount and TotalProductCost, which you use to create a calculated fi eld for profi t margin .
5. In the Report Data pane, right-click the ResellerSales dataset, and select Add Calculated Field .
6. In the Dataset Properties dialog box, in the empty row at the bottom of the fi eld list, in the Field Name text box, type Profi tMargin .
Add a calculated fi eld
Note You cannot include spaces in the name of the calculated field, and you must use a letter as the first character .
7. To the right of the Field Source text box, click the Expression button .
8. In the Expression dialog box, in the Category list, select Fields (ResellerSales), and then, in the Values list, double-click SalesAmount .
You can use the expression lists at the bottom of the Expression dialog box to build an expression by choosing a category, selecting a category item, and then selecting a value . When you choose the Fields category for a particular dataset, you can choose a value that corresponds to a field available in the selected dataset . Notice that your selection creates an expression, =Fields!SalesAmount.Value .
Important The equal sign (=) is required in your expression . Be careful not to remove it .
9. In the Category list, expand Operators, select Arithmetic, and then double-click the minus sign (–) .
Notice the red wavy line below the division symbol in the Set Expression For: Value box . This line is a visual cue that the expression currently contains an error, and in this case, it displays because the expression is incomplete . When you correct the problem, the wavy line disappears . This feature alerts you to a problem before you try to preview the report .
10. In the Set Expression For: Value text box, click at the end of the expression, type Fields!, and then, in the drop-down list that appears, double-click TotalProductCost . Instead of using the expression lists to build an expression, you can type the expres-sion directly into the expresexpres-sion box . As an aid to typing the expresexpres-sion, the Expresexpres-sion dialog box uses IntelliSense to display a list of valid fields when you begin an expression using the Fields collection syntax . This feature ensures that you get the correct name and case for the field name . However, the expression is not yet complete . You must still specify which property of the Fields collection item to use in the expression .
11. At the end of the expression, type a period (.), and then, in the drop-down list that appears, scroll down and double-click Value .
All properties available to the Fields collection item display in the IntelliSense list . Most of the time, you use the Value property in your expressions . Many of the properties you see listed are used only when you use an Analysis Services data source, which you learn about in Chapter 7, “Using Analysis Services as a Data Source .”
The Expression dialog box now looks like this:
Notice that the red wavy line no longer appears because your expression now validates correctly .
12. Click OK to close the Expression dialog box .
In the Field Source box for the calculated field, ProfitMargin, a placeholder for your expression appears, as shown here .
13. Click OK to close the Dataset Properties dialog box .
You can now use the ProfitMargin calculated field just as you would use any other field that comes directly from the dataset . In fact, you cannot distinguish the calculated field from other fields by looking at the list of fields in the Report Data pane . The only way to know if a field is calculated is to open the Dataset Properties dialog box and review the contents of the Field Source text box .
14. Click the tablix, right-click the Sales Amount column handle, point to Insert Column, and select Right to add a new column .
15. In the new column, in the detail row (to the right of [SalesAmount]), click the Field List icon, and select ProfitMargin .
16. Click the ProfitMargin column handle, and then, in the Properties window, in the Format text box, type C2 .
17. Save and then preview the report, as shown here .
You could, of course, create a text box expression to calculate the profit margin . However, whenever you have complex calculations that you use multiple times in the report, it is easier to define the calculation once and refer to it as needed .