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