CHAPTER 3 INTRODUCTION TO REPORTING SERVICES DESIGN WITH SQL SERVER DATA TOOLS
Expressions
Throughout this section, you’ll use fields from the dataset to create sample report segments. Because the
values from the fields are derived from expressions that are essentially VB.NET code, we will cover them
now because they play a crucial role in the report design process.
You can use expressions to produce a value for any report item that uses them. In SSRS, you can
assign expressions to almost any report property, from formatting such as color or padding to the value
of a textbox. A simple expression such as that of a field assignment is commonly used while designing
reports. In fact, every time you add a field to an area of a report, it’s automatically converted to an
expression, like so:=Fields!FieldName.ValueAn expression is signified by prefacing its content with the equal sign (=). You can also concatenate
expressions with other functions and literals. We will show several examples of expressions throughout
the book. We will list several sample expressions here and show you how to assign them to report items:=Parameters!ParameterName.Value: Used to assign the value of a parameter to a
report item such as a textbox or cell in a table.=IIF(Fields!FieldName.Value > 10, "Red", "Black"): Used for conditional
expressions. In this case, it would set the text color for a property to red if the
value of FieldName were greater than 10, black otherwise.=Fields!FieldNamel.Value & " " & Fields!FieldName2.Value: Used to
concatenate the value of two fields.=Avg(Fields!FieldName.Value):Used to aggregate functions such as Sum, Avg,
Min, Count, and Max that return the aggregate, minimum, or maximum value of
the specified field.=RowNumber(Nothing): Used to maintain a running total for the row numbers in
a report. “Nothing” in this case is a scope parameter passed to the function
indicating a grouping or dataset. The scope parameter could be a group name
or dataset, in which case a new row count would begin at the end of each group
or dataset.In SSRS for SQL Server 2005, the expression builder application used inside the report development
environment was rebuilt to give it the type of functionality needed to assist users create useful
expressions easily. Thankfully, all releases since then have retained the more advanced expression
builder, which lists most of the common functions, along with examples using their syntax; in addition,
they are categorized by type (Text, Conversion, and Date & Time). This makes it much quicker to find the
right function and place it as part of the expression you are building. Another great feature that
developers have become accustomed to, and frankly should not have to live without, is IntelliSense, a
contextual, in-line command completion feature. As you can see in Figure 3-13, as you type an
expression (in this case a field value expression from your stored procedure), you are prompted with all
the possible selections based on that expression. Once the expression is complete and syntactically
correct, you can click OK to make the expression part of the report object where you have associated it. If
any syntax errors exist, a standard red underline indicates the problem, and a mouse-hover over it will
display the type of error, in most cases “Invalid Syntax.” This is a quick introduction to expressions in
SSRS, but in Chapter 6, we will use them to perform tasks such as setting default parameter and property
values at runtime.