Chapter 21
Using Embedded and Referenced Code
What's in this chapter?
Building advanced expressions
Using custom code to extend formatting
Using custom code to apply business logic
The real power behind Reporting Services is its ability to creatively use data groups and combinations of report items. Calculations and conditional formatting may be added by using simple to intermediate programming code. Whether you are an application developer or a report designer, this chapter contains important information to help you design reports to meet your users' requirements and to raise the bar with compelling report features.
Perhaps we've put the cart before the horse with regard to expressions. In previous examples, you typed expression text without receiving an explanation. This couldn't be avoided, because using expressions is central to doing a lot of interesting things in Reporting Services. But we've just gotten started!
Any textbox bound to a dataset field or built-in field actually contains an expression. But in an effort to simplify the design interface, expressions are no longer displayed in the Report Designer as they were prior to the 2008 version of Reporting Services. Perhaps this might make life less hectic for entry-level report designers, but the rest of us have to be mindful that what we see in the Designer is not exactly what's going on behind the report design surface.
You'll recall that you can build simple composite expressions in a textbox by dragging items from the Report Data pane into a textbox. For example, if you want to display the page number and total number of report pages in the report footer, insert a textbox into the report footer and do the following:
This produces an expression that appears like this in the Report Designer:
[&PageNumber] of [&TotalPages]
If you have worked with versions of Reporting Services prior to 2012, you will notice an improvement in the user experience. After the cursor leaves the textbox, the Report Designer no longer displays the following non-descriptive label in gray:
<<Expr>>
You get to see the expression that was typed in the textbox. Now let's get to the bottom of this. What value is really stored in this textbox? Unfortunately, if the expression is created by using the designer (instead of the Expression Builder), you no longer can right-click and choose Expression to find out. Instead, these types of expressions are built as “text runs” inside a paragraph defined for the textbox. To see what is really going on under the covers, you would need to open the RDL file using a text editor such as Notepad. You will find an XML snippet as follows:
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Globals!TotalPages</Value>
<Style />
</TextRun>
<TextRun>
<Value> of </Value>
<Style />
</TextRun>
<TextRun>
<Value>=Globals!PageNumber</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
However, if you prefer to build your expressions in a more “programmatic” way, you can always use the Expression Builder dialog and type in the following:
=Globals!PageNumber & " of " & Globals!TotalPages
Don't worry; the next section explains the detailed steps to accomplish this task. This type of expression built by hand-coding in the Expression Builder is stored slightly differently in the RDL file:
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>
=Globals!PageNumber & " of " & Globals!TotalPages
</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
Notice that the RDL generated is slightly less verbose and contains only one TextRun element, which holds the expression you typed in the Expression Builder. If you've worked with previous versions of Reporting Services, this will look familiar. It's the same Visual Basic expression code that Reporting Services has used all along.
A little history behind this may be worth pointing out. You'll recall that Reporting Services was originally designed to be an application developer-centric tool, used by programmers in Microsoft Visual Studio. As time went on and the product matured, the powers that be at Microsoft took a good hard look at Reporting Services and realized that the industry was asking for a more information worker-centric reporting tool. Several incremental steps have helped Reporting Services become this dual-identity product that appeals to both programmers and business users. The downside is that, in places, the product can be a bit schizophrenic. In addition to the Designer's drag-and-drop expressions and the Expression Editor's expression syntax differences, the built-in fields in the Report Data pane are referred to as members of the Globals collection within true report expressions. The term built-in fields is just a friendly term, not a syntax convention.
Using the Expression Builder
You've already used a few expressions in the basic report design work you've done so far. Any field reference is an expression. In the Group Properties dialog, you used a field expression. In the previous example, we used an expression to show the page number and total pages so that it reads “X of Y” when the report is rendered. Expressions are used to create a dynamic value based on a variety of built-in fields, dataset fields, and programming functions. Expressions can be used to set most property values based on a variety of conditions, parameters, field values, and calculations. Let's take a quick look at common methods to build simple expressions. We'll explain the previous example, only this time in the Expression Builder.
To display the page number and page count, right-click the textbox and select Expression, and then use the Expression window to create the expression. You can use two methods to add expressions to the expression text area. One method is to select items from the category tree and member list and double-click an item to add to the expression. The other method is to simply type text into the expression text area. This uses the IntelliSense Auto List Members feature to provide drop-down lists for known items and properties.
The finished expression should read as follows:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
The Expression window (also called the Expression Builder) should appear, as shown in Figure 21.1.
The term globals (or built-in fields) applies to a set of variables built in to the Reporting Services namespace that provide useful information such as page numbers, report name, and path. A list of available global variables, fields, and parameters can be found in the Expression Builder.
You'll see this dialog again. In fact, you probably will use it often. In the Properties window, you can set many property values by using the drop-down list to select the item labeled <Expression … >. In the custom Properties dialog for each report item, the Expression dialog is invoked using the button labeled fx adjacent to each property value.
In previous chapters, you learned how parameter values are passed into a query to limit or alter the result set. Parameters may also be used within the report to modify display characteristics by dynamically changing item properties. For example, we can affect grouping expressions of data regions based on values in parameter variables. A report's parameters collection is publicly accessible from the expression window and can be included as part of expressions.
Calculated Fields
Custom fields can be added to any report and can include expressions, calculations, and text manipulation. This might be similar in functionality to alias columns in a query or view, but the calculation or expression is performed on the Report Server after data has been retrieved from the database. Calculated field expressions can also use Reporting Services global variables, custom code, and functions that may be unavailable in a SQL expression.
Let's start with a basic report that displays product details. You will replace a simple expression previously used in a textbox with a calculated field. Figure 21.2 shows a textbox used to calculate the profit margin for each product by subtracting the StandardCost field from the ListPrice. The Expression dialog is shown for this textbox.
Rather than performing the calculation on the textbox, let's add a calculated field to the dataset definition so that this calculation can be reused by other objects in the report.
Use the Report Data pane in the Report Designer to select the dataset you want to use. Right-click the dataset and choose Add Calculated Field, as shown in Figure 21.3.
The Dataset Properties dialog opens, as shown in Figure 21.4. On the Fields page, click the Add button to add a new item to the Fields collection. Type the new field name, and then click the expression button (fx) next to the Field Source box on this new row.
When the Expression dialog opens, simply type or build the same expression as before. Verify the results with Figure 21.4, and then click OK on both of these dialogs to save the newly calculated field to the dataset.
Using the calculated field is no different from using any other field derived from the dataset query. Just drag and drop the new field from the Report Data pane to the textbox on the report. Note the Profit field reference in the textbox, as shown in Figure 21.5.
You can use the expression button to invoke the Expression Builder to use any functionality available within the design environment in addition to the database fields exposed by the dataset query. These calculations will be performed on the Report Server rather than on the database server.
Conditional Expressions
You've seen some simple examples of using expressions to set item values and properties. Let's look at one more example of a conditional expression, and then we'll discuss using program code to handle more complex situations. We'll create a simple Product Inventory report that uses conditional formatting. The table in this report returns a list of products with current inventory values. The Product table in the AdventureWorks_WroxSSRS2012 database contains a ReorderPoint value that informs stock managers when they need to reorder products. If the inventory count falls below this value, you can set the inventory quantity to appear in red next to the name. Using a conditional expression in this manner is similar to using conditional formatting in Excel.
The following example uses a dataset with this SQL expression:
SELECT Product.Name, Product.ReorderPoint,
ProductInventory.Quantity, Product.ListPrice
FROM Production.ProductInventory
INNER JOIN Production.Product
ON ProductInventory.ProductID = Product.ProductID
ORDER BY Product.Name
The table bound to this dataset has four columns: Name, ReorderPoint, Quantity, and ListPrice. On the Quantity textbox in the table's detail row, the Color property is set to an expression containing conditional logic instead of being set to a value. You can use the Expression Builder or just type this expression into the Properties window under the Color property:
=IIF(Fields!Quantity.Value < Fields!ReorderPoint.Value, "Red", "Black")
We've done the same thing with the Font > FontWeight property so that if the inventory quantity for a product is below the reorder point value, the quantity is displayed in both red and bold text.
Preview the report to check the results; they should look like Figure 21.6.
IIF() Is Your Friend
Even if you're not a programmer, learning a few simple Visual Basic commands and functions will prove valuable and will likely meet most of your needs. The most common and useful function you're likely to use in simple expressions is IIF (immediate if). As you saw in the previous example, the IIF() function takes three arguments. The first is a Boolean expression that returns either True or False. If the expression is True, the value passed into the second argument is returned. Otherwise (if the first expression is False), the third argument value is returned. Take another look at the expression used in the previous example:
=IIF(Fields!Quantity.Value < Fields!ReorderPoint.Value, "Red", "Black")
If the expression Fields!Quantity.Value < Fields!ReorderPoint.Value yields a True result (where Quantity is less than ReorderPoint), the value “Red” is returned. Otherwise, the value returned is “Black”.
In cases where an expression may return more than two states, IIF() functions can be nested to form multiple branches of logic. In this example, three different conditions are tested:
=IIF( Fields!Quantity.Value < Fields!ReorderPoint.Value, "Red", IIF(Fields!ListPrice.Value > 100, "Blue", "Black" ))
Let's analyze the logic. If Quantity is not less than ReorderPoint, the third IIF() function argument is invoked. This contains a second IIF() function, which tests the ListPrice field value. If the value is greater than 100, the value “Blue” is returned; otherwise, the return value is “Black”. According to the definition of this function, the second argument is the TruePart value, and the third argument is the FalsePart value. This means that the value in the second position is returned if the expression evaluates to True, and the value in the third position is returned if it is False.
Beyond the simplest nested functions, expressions can be difficult to write and maintain. In addition to decision structures, you can use common functions to format the output, parse strings, and convert data types. Count the opening and closing parentheses to make sure that they match. This is yet another example of where writing this code in a Visual Basic class library or forms project is helpful because of the built-in code-completion and integrated debugging tools. Consider using these other functions in place of nested IIF() functions.
The SWITCH() function accepts an unlimited number of expression and value pairs. The last argument accepts a value that is returned if none of the expressions resolves to True. You can use this in place of the previous nested IIF() example:
=SWITCH( Fields!Quantity.Value < Fields!ReorderPoint.Value, "Red", Fields!ListPrice.Value > 100, "Blue", 1=1, "Black" )
Unlike the IIF() function, the SWITCH() function has no FalsePart value. Each expression and return value is passed as a pair. The first expression in the list that evaluates to True causes the function to stop processing and return a value. This is why we included the expression 1=1. Because this expression always evaluates to True, it becomes the catchall expression that returns “Black” if no other expressions are True.
Visual Basic supports many of the old-style VBScript and VB 6.0 functions, as well as newer overload method calls. In short, this means that there may be more than one way to perform the same action. Table 21.1 describes a few other Visual Basic functions that may prove useful in basic report expressions.
Hundreds of Visual Basic functions can be used in some form, so this list is just a starting point. For additional assistance, view the Online Help index in Visual Studio, under Functions [Visual Basic]. This information is also available in the public MSDN library at http://msdn.microsoft.com.
Using Custom Code
When you need to process more-complex expressions, it may be difficult to build all the logic into one expression. In such cases, you can write your own function to handle different conditions and call it from a property expression.
You can take two approaches to managing custom code. One is to write a block of code to define functions that are embedded in the report definition. This technique is simple, but the code will be available to only that report. The second technique is to write a custom class library compiled to an external .NET assembly and reference this from any report on your Report Server. This approach has the advantage of sharing a central repository of code, which makes updates to the code easier to manage. It also gives you the freedom to use any .NET language (C#, VB). The downside of this approach is that the configuration and initial deployment are a bit tedious.
Using Custom Code in a Report
A report can contain embedded Visual Basic .NET code that defines a function you can call from property expressions. The Code Editor window is simple; it doesn't include any IntelliSense, editing, or formatting capabilities. For this reason, you might want to write the code in a separate, temporary Visual Studio project of type “VB class library,” to test and debug before you place it into the report. When you are ready to add code, open the Report Properties dialog. You can do this from the Report menu. The other method is to use the Report Designer right-click menu. Right-click the Report Designer outside of the report body and select Properties. On the Properties window, switch to the Code tab, and write or paste your code in the Custom Code box.
The following example starts with a new report. Here is the code, along with the expressions you will need to create a simple example report on your own. The following Visual Basic function accepts a phone number or social security number (SSN) in a variety of formats and outputs a standard U.S. phone number and properly formatted SSN. The Value argument accepts the value, and the Format argument accepts the value Phone or SSN. You use it only with phone numbers, so you can omit the SSN branch if you like.
‘*************************************************************
‘ Returns properly formatted Phone Number or SSN
‘ based on format arg & length of text arg
‘*************************************************************
Public Function CustomFormat(ByVal text as String, ByVal format as String) as_
String
Select Case format
Case "Phone"
Select Case text.Length
Case 7
Return text.SubString(0, 3) & "-" & text.SubString(3, 4)
Case 10
Return "(" & text.SubString(0, 3) & ") " _
& text.SubString(3, 3) _
& "-" & text.SubString(6, 4)
Case 12
Return "(" & text.SubString(0, 3) & ") " _
& text.SubString(4, 3) & "-" & text.SubString(8, 4)
Case Else
Return text
End Select
Case "SSN"
If text.Length = 9 Then
Return text.SubString(0, 3) & "-" _
& text.SubString(3, 2) & "-" & text.SubString(5, 4)
Else
Return text
End If
Case Else
Return text
End Select
End Function
The dataset in this report gets its data from the Vendor and related tables in the AdventureWorks_WroxSSRS2012 database and returns three columns: FirstName, LastName, and Phone. The SQL expression used to retrieve this information is as follows:
SELECT FirstName, LastName, PhoneNumber FROM Purchasing.vVendorWithContacts
These three columns are used in a table bound to the dataset. The Value property of the Phone column uses an expression that calls the custom function preceded by a reference to the Code object:
=Code.CustomFormat(Fields!PhoneNumber.Value, "Phone")
Figure 21.7 shows the report in design layout view.
Using a Custom Assembly
Rather than embedding code directly into each report, you can use a custom assembly as a central repository of reusable code to extend the functionality of multiple reports. In Reporting Services, custom assembly support is enabled by default. However, the code in the assembly has restricted access to system resources due to Code Access Security policies (as discussed in the next section). If you intend for the assembly to interact with the filesystem or perform data access, you need to modify some configuration settings to grant the appropriate level of access to your code. We'll discuss these conditions after a simple walk-through to create an assembly that won't require any special settings. For this discussion, you should have a basic understanding of .NET and how to create and build libraries using Visual Studio.
To begin, open Visual Studio, then from the File 
New Project menu, create a class library project. You can write this code in any .NET language, because it will be compiled into an assembly that uses a low level intermediate language (IL), common to .NET. The methods you create can be either static or instanced. It's easier to use static methods so that you don't have to manage the instancing and life of each object. This simply means that you will declare public functions in your class using the static keyword in C# or the Shared keyword in Visual Basic. Using the same code logic as in the previous example, the Visual Basic class code would look like this:
Public Class Report_Formats
‘*************************************************************
‘ Returns properly formatted Phone Number or SSN
‘ based on format arg & length of text arg
‘*************************************************************
Public Function CustomFormat(ByVal text as String, ByVal format as String) _
As String
Select Case format
Case "Phone"
Select Case text.Length
Case 7
Return text.SubString(0, 3) & "-" & text.SubString(3, 4)
Case 10
Return "(" & text.SubString(0, 3) & ") " _
& text.SubString(3, 3) _
& "-" & text.SubString(6, 4)
Case 12
Return "(" & text.SubString(0, 3) & ") " _
& text.SubString(4, 3) & "-" & text.SubString(8, 4)
Case Else
Return text
End Select
Case "SSN"
If text.Length = 9 Then
Return text.SubString(0, 3) & "-" _
& text.SubString(3, 2) & "-" & text.SubString(5, 4)
Else
Return text
End If
Case Else
Return text
End Select
End Function
End Class
In order to test the external assembly code from a Visual Studio IDE report preview window, you must build the class library project in Debug configuration and then copy the output DLL assembly to the IDE's PrivateAssemblies folder. The default path to this folder is C:Program FilesMicrosoft Visual Studio 10.0Common7IDEPrivateAssemblies.Once the assembly is copied, you will need to open a new instance of Visual Studio in order for it to load the assembly in memory.
In the Report Properties dialog (this is where you entered the code in the previous topic example), select the References page, as shown in Figure 21.8. Add the reference by browsing to the assembly file. The reference line shows metadata from the assembly, including the version number.
To use a custom method in an expression, reference the namespace, class, and method using standard code syntax. The expression for the CustomFormat method should look like this:
=Reporting_Component.Report_Formats.CustomFormat(Fields!Phone.Value, "Phone")
The report should look exactly like it did in the previous example.
After debugging and testing the code, you are ready to deploy it to the Report Server. Save and build the class library project in Release configuration, and then copy the assembly (DLL) file to the ReportServerin folder. The default path to this folder is C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesReportServerin.
Custom Assembly Security
When you use a custom assembly deployed to your Report Server, the assembly must run with the appropriate level of security access. This feature, Code Access Security (CAS), is a common challenge for all server-side .NET applications running prior to .NET 4. A thorough discussion of this topic is beyond the scope of this book. If you are a seasoned developer, these should be familiar topics. If you are not, you should consult a .NET application developer to help configure your custom assemblies.
In short, the steps to deploy and configure an assembly to run on your Report Server are not much different from any other remotely deployed component. The permissions depend on the resources the assembly uses. For example, a component that interacts with the local filesystem or that consumes features of another component or database connections requires privileges to do so. The following are some of the more common steps to make custom assemblies more accessible:
For additional assistance with specific security considerations and configuration details, use SQL Server 2012 Books Online to look up the topic “Using Reporting Services Security Policy Files.”
Errors, Warnings, and Debugging Code
When you preview or try to deploy a report, all the expressions and embedded code in the report are cranked through the .NET Common Language Runtime debugger and native code compiler. If no errors are found, an assembly is built on the Report Server. This means that when reports execute, all the expression and program code actually runs from compiled binaries rather than from the Visual Basic source code.
Errors are listed in the IDE task window if this process fails. The Report Designer has a quirk that can be a bit confusing until you get used to it. Along with errors that prevent code compilation and report deployment, another set of information shows up in this list. Some conditions may cause Reporting Services to be less than ecstatic about your code, but not unhappy enough to prevent it from compiling. These are called warnings, and they appear on the task list below any errors. The confusing thing is that Visual Studio displays this window only when errors occur, unless you have it pinned and always visible. This means that you can build a big, elaborate report that runs perfectly until you make one small mistake in the code. When you try to preview this report, you might suddenly see 30 issues on the task list. These may include “can't deploy shared datasource…” and “textbox42 has a BackgroundColor set to … which is invalid.” If this happens, don't get excited; this is just how the Designer works. Those warnings were there all along. Visual Basic just didn't put the list in front of you until you committed a serious infraction. Start at the top of the list, and work your way down until you see an error description that makes sense. Double-click this line. In most cases, doing so takes you to the properties for the offending report item, allowing you to make the correction and move on.
When you test reports in the Visual Studio Report Designer, your custom assembly is loaded into memory when it is first invoked and cannot be unloaded until you exit Visual Studio. This means that if you make code changes and redeploy the assembly, these changes may be unavailable to the report unless you restart the Visual Studio process (devenv.exe). The best way to work around this issue is to deploy your report to your local Report/Web Server and test it using the Report Manager.
Summary
Expanding on the basic design concepts and building blocks you learned about in the previous chapters, in this chapter you raised the bar and created more powerful and compelling reports using custom expressions and code techniques.
Expressions and custom programming take report design to new heights by allowing a single report to deliver more functionality, behaving more like a multifunction business application than a traditional report.
By leveraging the .NET framework and its runtime, you can create highly specialized code modules that can do everything from simple string manipulations to complex mathematical and statistical calculations. You can even load data from files in the file system or make web calls to other servers. The possibilities are endless when you have such a powerful feature to leverage from inside your reports.