Share via


Expressions in Excel Rendering

Expressions are used in reports to calculate the value of a report item, or to calculate values for style and formatting properties or other report item properties. Reporting Services supports expressions that are written in Microsoft Visual Basic. When rendering to Microsoft Excel, the Visual Basic formulas are translated to Excel formulas when possible. The formula must only refer to report items, on the current page of the report and not directly to field values. The report item references are converted to an appropriate cell reference. For more information on expressions in a report, see Working with Expressions in Reporting Services.

Formulas defined in Report Definition Language (RDL) using Visual Basic expressions are allowed to use "+" for string concatenation. However, Excel does not support the "+" operator for string concatenation. So for formulas to be translated correctly, the report should use the "+" operator to denote addition on numeric operands, and the "&" to denote concatenation on string operands.

Formulas and Functions for Text Boxes

Not all text box operators and functions are supported when a report is exported to Excel. Using operators and functions that are not allowed causes the formula conversion to fail. Instead, the calculated value of the formula is placed in the cell as a constant.

The following operators are allowed:

: + - / * ^ & < <= <> = > >=

The following functions are allowed:

Abs, Atan, Choose, Cos, DateValue, Day, DDB, Exp, FV, Hour, Int, Ipmt, Left, Minute, Month, Now, Nper, Pmt, PPmt, Pv, Rate, Right, Second, Sign, Sin, Sln, Sqrt, Syd, Tan, Today, Year.

Additional Visual Basic functions are allowed, but are translated to an appropriate Excel function name during conversion. The table below maps these function names.

Visual Basic .NET function Excel function

Asc

Code

Cdate

DateValue

Chr

Char

DateSerial

Date

Hex

Dec2Hex

Iif

If

Lcase

Lower

Oct

Dec2Oct

Ucase

Upper

For all other cases, such as an unsupported expression, a formula is not generated for Excel. Instead, the value is placed in directly in the cell. When you want no formulas generated in Excel for the entire report, only the values, you can use the device information setting of OmitFormulas to suppress formula generation. For more information on OmitFormulas and other device information settings, see Reporting Services Device Information Settings.

See Also

Concepts

Design Considerations for Report Rendering

Other Resources

Reporting Services Programming

Help and Information

Getting SQL Server 2005 Assistance