SSRS Complex Expressions

Complex expressions can contain multiple built-in references, operators, and function calls, and appear on the design surface as <>. To see or change the expression text, you must open the Expression dialog box or type directly in the Properties pane. The following table lists typical ways you can use a complex expression to display or organize data or change report appearance, including the property to set, the dialog box you typically use to set it, and the value for the property. You can type an expression directly into a dialog box, on the design surface, or in the Properties pane.

Functionality Property, Context, and Dialog Box Property Value

1. Calculate aggregate values for a dataset. Value property for a placeholder inside of a text box. Use Placeholder Properties Dialog Box, General. =First(Fields!Sales.Value,"DataSet1")
2. Concatenate text and expressions in the same text box. Value for a placeholder inside of a text box that is placed in a page header or page footer. Use Placeholder Properties Dialog Box, General. ="This report began processing at " & Globals!ExecutionTime
3. Calculate an aggregate value for a dataset in a different scope. Value for a placeholder inside of a text box that is placed in a tablix group. Use Placeholder Properties Dialog Box, General. =Max(Fields!Total.Value,"DataSet2)
4. Format data in a text box depending on value. Color for a placeholder inside of a text box in the details row for a tablix. Use Text Box Properties Dialog Box, Font. =IIF(Fields!TotalDue.Value < 10000,"Red","Black")
5. Calculate a value once to refer to throughout the report. Value for a report variable. Use Report Properties Dialog Box, Variables. =Variables!MyCalculation.Value
6. Include specific values for more than one field from a dataset. Filter equation for a group in a tablix. Use Tablix Properties Dialog Box, Filters. For data type, select Boolean.
7. =IIF(InStr(Fields!Subcat.Value,"Shorts")=0 AND (Fields!Size.Value="M" OR Fields!Size.Value="S"),TRUE, FALSE)
8. =
9. TRUE
10. Hide a text box on the design surface, that can be toggled by the user using a Boolean parameter named Show. Hiddenproperty on a text box. Use Text Box Properties Dialog Box, Visibility. =Not Parameters! Show .Value
11. Specify dynamic page header or footer content. Value for a placeholder inside of a text box that is placed in the page header or footer. ="Page " & Globals!PageNumber & " of " & Globals!TotalPages
12. Specify a data source dynamically by using a parameter. Connection string on the Data source. Use Data Source Properties Dialog Box, General. ="Data Source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks2008R2"
13. Identify all the values for a multivalue parameter chosen by the user. Value for a placeholder inside of a text box. Use Tablix Properties Dialog Box, Filters. =Join(Parameters!MyMultivalueParameter.Value,", ")
14. Specify page breaks for every 20 rows in a tablix with no other groups. Group expression for a group in a tablix. Use Group Properties Dialog Box, Page Breaks. Select the option Between each instance of a group. =Ceiling(RowNumber(Nothing)/20)
15. Specify conditional visibility based on a parameter. Hidden property for a tablix. Use Tablix Properties Dialog Box, Visibility. =Not Parameters!< boolean parameter >.Value
16. Specify a date formatted for a specific culture. Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General. =Fields!OrderDate.Value.ToString(System.Globalization.CultureInfo.CreateSpecificCulture("de-DE"))
17. Concatenate a string and a number formatted as a percentage to two decimal places. Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General. ="Growth Percent: " & Format(Fields!Growth.Value,"p2")