Calculating fields in reports

You can use Report Designer to create fields in your report that do not exist in the data source. This is useful if you want to create a dynamic field that is dependent on other attributes within the data source. To create a dynamic field, you can use a C# expression to work with string, date, or numeric field types.

There are two ways to create calculated fields in the Report Designer. The first method is to add a text box to the report and enter an expression into the text box that will get executed when the report is generated. The steps to this method are outlined below, including expression examples. The second method is to add an unbound Calculated field to the report. You can only do this from the Report Section tree. Right-click on the Calculated node of the tree and choose Add. This will create an unbound field that can be used in the report. The expression of the unbound field should be set in the Formula property of the field. Once the Formula is set, drag the field on to the section in the report where you want to use it.

Unbound fields allow you to use other unbound fields in the Formula. As a result, you can create Summary fields that use unbound fields to generate dynamic content in the report.

Note:

You will not be able to use unbound calculated fields to calculate a summary statistic that uses other summary statistics in the formula.

1. Open a report in the Report Designer.
2. From the Design Elements list, click TextBox and drag the element into position on the report layout.
3. Enter an expression in the Data Field area in the Element Properties grid. Expressions always begin with an equals (=) sign.

   Mathematical	=Quantity + 5 =Quantity - 3 =Diameter * 3.14 =Population / Area =(double)System.Math.Round(CostPerUnit*Inventory) For more information about the supported math functions, see the MSDN help for the Math Class.
   Conditional	=(ValueField == 1)?"One":"Not One" =(Price < 10)?"Clearance":"Price-Change" =(SoilType == System.DBNull.Value) ? "No soil type specified" : SoilType
   Date/Time	=System.DateTime.Now.ToString() =System.DateTime.UtcNow.ToString() For more information about the supported date/time functions, see the MSDN help for the DateTime Structure.
   Concatenated	="Average Cost: " + avgCostField =ZipCode + ZipCode4Digit
   String	=Name.Substring(0,4) =Name.Replace("Test", "Final") =Name.ToUpper() For more information about the supported string functions, see the MSDN help for the String Class.

4. Click Run Report to see a preview.
Check the spelling of the field names or the syntax of the expression if the field value returns the error message Control Script failed for Control.

Feedback on this topic?