This topic discusses report expressions.
Overview of Report Expressions
Reports often need values that are not fixed (e.g. retrieved from some column value in a row set). This is where expressions come into play, since they allow for multiple column values, concatenation, mathematical formulas, and more.
Expressions are evaluated when a report is executed.
Expressions can:
- Be used for any string property in the dialogs.
- Be created using the Report Expression Editor.
- Include fields and aggregates of fields (aggregates are built-in functions such as: MAX, MIN, SUM, AVG, etc., see the Common Aggregates Functions section below ).
- Contain custom code.
-
Have constants, global values and parameters. Some globals can only be used in certain report sections, such as page headers and footer, and will not be relevant to expressions used for the gauge.
next to a relevant property displayed in a dialog.
Figure 1: Report Expression Editor.
Syntax and Expression Examples
The syntax for report expressions is "=Operator(Fields!ColumnName[,"DatasetName"])".
Note that qualifying the expression with the dataset name is not required if a textbox has an expression icon () beside it.
Example 1
Expression: "=Sum(Fields!LineTotal.Value)" (without specifying dataset name)
Expression: "=Sum(Fields!LineTotal.Value,"SalesDetails")" (specifying dataset name)
Evaluates to: the sum of the LineTotal column values for the SalesDetails dataset.
Example 2
Expression: "=4*AVG(Fields!LineTotal.Value)" (without specifying dataset name)
Expression: "=4*AVG(Fields!LineTotal.Value,"SalesDetails")" (specifying dataset name)
Evaluates to: 4 times the average of the LineTotal column values for the SalesDetails dataset.
Common Aggregate Functions
The following table lists the more common SQL aggregate functions (i.e. operators), which can be used in expressions:
| Function | Description |
|---|---|
| AVG(column value) | Gets the average value of a column. |
| COUNT(column) | Gets the number of column rows. |
| COUNT(*) | Gets the number of selected rows. |
| COUNT(DISTINCT column) | Gets the number of distinct values. |
| FIRST(column) | Gets the value of the first record in a specified field (not supported in SQL Server 2000*) |
| LAST(column) | Gets the value of the last record in a specified field (not supported in SQL Server 2000*) |
| MAX(column) | Gets the largest value of a column. |
| MIN(column) | Gets the smallest value of a column. |
| SUM(column) | Gets the total sum of a column. |
Refer to the SQL Server documentation for a complete listing of aggregate functions.
See Also
Getting Started
Using The Gauge Control
Gauge Designer Dialog
Gauge Designer Overview
Sub-Gauge Designer Dialog
Sub-Gauge Designer Overview
Grouping and Sorting Properties Dialog
Grouping, Filtering and Sorting Dialog Overview