Pro SQL Server 2012 Reporting Services

CHAPTER 7  USING CUSTOM .NET CODE WITH REPORTS

190

Listing 7-2 shows how to use a conditional expression in the Color property of a textbox to set the

color of the text depending on the return value of the function call.

Listing 7-2. Using a Conditional Expression

=IIF(Code.ExceedMaxCost(Sum(Fields!Visit_Count.Value,

"table1_Group1"),Sum(Fields!Estimated_Cost.Value, "table1_Group1")),"Red", "Black")

The method ExceedMaxCost determines whether the type of treatment has had more than 10

instances in the time span and the average cost was more than 45 per visit, and returns True if so or False

if not. Using a Boolean return value makes it easy to use the method in formatting expressions, because

the return value can be tested directly instead of needing to be compared to another value.

Using the IIF native SSRS function, we can pass the custom ExceedMaxCost directly and evaluate

the return value. IIF evaluates the first parameter and will return the second parameter when True and

the third when False.

When a treatment type exceeds the maximum average cost allowed, ExceedMaxCost returns True,

which sets the value of the textbox Color property to Red, which in turn will cause the report to display

the text in red. If not, then ExceedMaxVisits returns False, and the Color property is set to Black.

Using the ExceedMaxCost Function in a Report

Now we’ll walk you through how to actually add this expression to the report. First, select the field in the

report to which you want to apply the expression. In this case, select the treatment name textbox from

the report in design mode, as shown in Figure 7-4.

Figure 7-4. Adding the expression to the report

Second, with the textbox selected, go to the Properties window, and select the Color property (see

Figure 7-5). If the Properties window is not being shown, you can select View > Properties Window to

make it visible from within Visual Studio.

b