第 98 页

and end, and you will most likely (depending on your needs) want to change

the start and end width of the range (generally so they are the same value).

From the Fill tab you can change the color of the range to match its meaning

(generally green = good, red = bad).

The final output of your gauge is shown in Figure 30-26.

fiGure 30-25

fiGure 30-26

Designing reports .

661

expressions, Placeholders, and aggregates

Expressions provide the flexibility and power in your report, and are used everywhere from getting

a value from a dataset, aggregating data, transforming data, and performing calculations, through

to decision-making processes using conditional statements (IIF, and so on). Anything dynamically

inserted into the report when it is being generated is handled by an expression. You might think

of expressions as a formula that returns a value. Almost everything in a report can be controlled

by an expression, including most control properties. So far you’ve already seen the expressions

generated when you drag a field onto the report, and how the expression is “hidden” behind a

placeholder, which can be used to hide its complexity. All expressions start with an equals (=) sign

and return a single value.

Expressions can be categorized into simple expressions and complex expressions. Simple expressions

refer only to a single field, which may have an aggregate function applied. Simple expressions

will display a simplified version of the underlying expression as the label of the placeholder when

displayed in the report designer. An example of a simple expression is:

=Fields!Revenue.Value

This will display in the report designer simply as [Revenue].

Complex expressions, however, either reference multiple fields or include operators, and appear in

the report designer with <<Expr>> as their default placeholder label (although this can be changed

in the placeholder properties to something more meaningful). Complex expressions essentially use

VB for their syntax, although they still must consist of only a single line of code that returns a

value. They can, however, make calls to more complicated multiline functions if necessary, as will

be discussed in the next section. An example of a complex expression is:

=Fields!ProductCategory.Value + " sold to " + Fields!Country.Value

Now take a look at the process of creating

an expression. As previously noted, when

you drop a field onto a report it creates an

expression that returns the value of that field

from the dataset. To see this in action, drop

a table on a report and then drop a field from

the Report Data window into one of its cells.

As discussed earlier in the chapter, what is

being displayed in the cell is a placeholder

label. When you right-click the placeholder

you can select Expression from the menu to

view and edit its underlying expression. This

displays the Expression Builder window as

shown in Figure 30-27.

fiGure 30-27

662 . chaPter 30 reporTing

As its name might suggest, the Expression Builder helps you build expressions. At the top is the code

area where you can type in the expression, and below it is the category tree, category items list, and

a values list (which is only shown when values are available). The code area supports IntelliSense,

tooltips (displaying function parameters), and syntax checking (squiggly red underlines to show

errors), although unfortunately it doesn ’ t support syntax highlighting. The lower “ builder areas ”

help you build an expression, which is especially helpful when you don ’ t know the syntax or what

functionality is available. The Category tree allows you to drill down to select a category (such as

a dataset, an operator type, a function type, and so on). The Item list displays what is available

in that category, and the Values list (if values are available) displays the values for that item. For

functions and operators it will display some helpful information on the selected item (what it does

and examples of how it is used) in place of the Values list.

You will note when you are creating a report that many properties have an fx button next to

them (in the dialog windows), or an Expression entry (in their drop - down list in the Properties

tool window). This means that those properties can have expressions assigned to determine the

value that should be applied to them, and clicking this button or selecting this item from the

drop - down list will open the Expression Builder window in which you can create an expression

to control the value of that property. This is extremely useful in conditional formatting

scenarios, such as toggling the visibility or color of a control based upon the data being

displayed.

In conditional formatting scenarios you will fi nd the IIF function (Inline If)

very useful to choose between two values based upon the result of a given

expression (with the result being applied as the value of the property). Other

“ program fl ow ” functions that you will fi nd useful are the Choose and Switch

functions.

Sometimes you want to use a calculated value in multiple places in a report, and rather than

have the report recalculate the value multiple times, you ’ d like to calculate it once and reuse the

value (speeding up the generation of the report in the process). This is where variables can be

useful. Being named variables you may think that you can change their values (such as

using them in a running totals scenario), but unfortunately that isn ’ t the case. Their value

can only be set once, and then this value is used from that point on without it needing to be

recalculated.

Running totals are actually implemented in a report using the RunningValue

function (built into the reporting engine) in an expression.

Designing reports .

663

There are two types of variables: report

variables and group variables, with their

name matching their scope. The value of

report values are set in the Report . Report

Properties window, in the Variables tab

shown in Figure 30-28.

The variables defined here will be available

anywhere in the report. A new entry will

be created when you click the Add button,

where you can give the variable a name

and a value. If it’s a constant value you can

specify its value there, or you can click the fx

button to create an expression that calculates

the value. This calculation will only be

performed once, and the value will be reused

on subsequent references of the variable.

fiGure 30-28

The variables that are available to an expression can be found in the Expression

Builder under the Variables category.

So if you’ve created a variable called testVar, you can use it in an expression like so:

=Variables!testVar.Value

Another use for report variables is to define constant values for use in your report, enabling you to

centrally define values that are used in multiple places in your report without “hard coding” them in

those places.

The other type of variable is the group variable. This works in much the same way as the report

variables, except the scope of the calculated value is just the current grouping in a Table/Matrix/List

control (and any child groupings). Its value is calculated each time the grouping changes, so if you

have a calculation to make for each grouping (whose value is reused throughout that grouping), this

is how you would implement it. To create a group variable, open the Group Properties window, go to

the Variables tab, and then create and use the variable in the same way as demonstrated for the report

variable. You can test the behavior of how the calculated value is reused and subsequently recalculated

when the group changes by creating the following expression and seeing when its output changes:

=Round(Rnd() * 100)

custom code

Sometimes the built-in functions of the reporting engine are not enough to suit your purposes.

When you need a complex multiline function to perform a calculation or make a decision, this

664 .

chaPter 30 reporTing

must be written outside the expression

builder (because expressions can only exist

on a single line). You have two ways to

achieve this: by embedding the code in the

report itself, or by referencing an external

.NET assembly that contains your custom

functions. You can set up both of these

options at the report level from the Report .

Report Properties menu.

When you select the Code tab you will see

what is shown in Figure 30-29 (a custom

function is already entered for demonstration).

As you can see, this is a very sparse code editor. There is no syntax highlighting, error checking, or

IntelliSense, so it isn’t very friendly to use. If there is an error in your code it will be caught when

the project is compiled and the compilation will fail (pointing out the cause of the error in the Error

List tool window). After you’ve written your functions in here (using VB as the language) you can

add a textbox to your report, open the expression builder, and call them like so:

=Code.CustomFunctionTest("Test Input")

fiGure 30-29

Note that the IntelliSense in the expression builder doesn’t show the available

function names when you type Code. in the editor, nor does it show what

parameters the function takes. In addition, the only assemblies automatically

referenced for use are the System.Convert, System.Math, and Microsoft.

VisualBasic — if you need to use assemblies other than these you will need to

add references to them in the References tab, which is discussed shortly.

Calling the function shown in Figure 30-29 with this expression displays the following in the

textbox:

Hello from the custom function! Your input parameter was: Test Input

If you want to reuse the custom functions among multiple reports you are better off writing the code

in a .NET assembly, and referencing it from each report that requires its functions. You can create

a Class Library project, write the code (in either VB or C#), and then reference it in your report.

Unfortunately, you will face a few difficulties in ensuring that the report can find the assembly

and configuring its code access security settings so that the report has the permissions to execute

its functions — so it’s not a completely straightforward process. However, you are about to walk

through the process required to get it working here.

Designing reports .

665

Create a new project using the Class Library template called CustomReportingFunctions. Create a

class called MyFunctions, and add the following function to it:

Vb

Public Shared Function CustomFunctionTest(ByVal testParam As String) As String

Return "Your input parameter was: " + testParam

End Function

c#

public static string CustomFunctionTest(string testParam)

{

return "Your input parameter was: " + testParam;

}

You will also need to add the following attribute to the assembly to enable it to be called by the

reporting engine. This is added to AssemblyInfo.vb for VB developers (under the My Project

folder, requiring the Show All Files option to be on in order to be seen), and to AssemblyInfo.cs

for C# developers (under the Properties folder).

Vb

< Assembly: System.Security.AllowPartiallyTrustedCallers >

c#

[assembly: System.Security.AllowPartiallyTrustedCallers]

In order for the report to find the assembly, it must be installed in the Global Assembly Cache

(GAC). This means you need to give the assembly a strong name, by going to the Properties

of the custom functions assembly, opening the Signing tab, checking in the Sign the Assembly

checkbox, and choosing/creating a strong name key file. Now you can compile the project, and

then install the assembly in GAC by opening the Visual Studio Command Prompt, entering

gacutil -i < assembly_path >

and replacing < assembly_path > with the actual path to the compiled assembly.

Each time you update this assembly, remember to install it into the GAC again.

Now you can reference the assembly in the report. Open up the Report Properties window and go

to the References tab (as shown in Figure 30-30). Click the Add button, then click the ellipsis button

666 .

chaPter 30 reporTing

on the blank entry that appears. Find the

assembly (you may need to browse by file to

find it) and click OK.

Note the Add or Remove Classes area below

the Add or Remove Assemblies area. This

is used to automatically create instances of

classes in the referenced assemblies. You

made your function shared (or static as it

is referred to in C#) so you don’t need an

instance of the MyFunctions class. However,

if the function was not shared/static and you

needed a class instance you need to configure

these instances here (because a class cannot

be instantiated in an expression). To do

this, specify the class name (including its

namespace) and give it an instance name (that is, the name of the variable that you will use in your

expressions to refer to the instance of the class). The reporting engine will handle instantiating

the class, and will assign the reference to a variable with the given name so you can use it in your

expressions.

Now you are ready to reference your function in an expression, although slightly differently from

how you used the function when it was embedded in the report. You need to refer to the function by

its full namespace, class, and function name. For example:

=CustomReportingFunctions.MyFunctions.CustomFunctionTest("Test Input")

You are almost done, but not quite. The final piece of the puzzle is to specify that the assembly

should be run with full trust in the domain of the report engine. This is done when initiating the

report rendering process (which is covered later in this chapter) and requires the strong name of

the assembly.

Vb

Dim customAssemblyName As String = "CustomReportingFunctions, Version=1.0.0.0, " &

"Culture=neutral, PublicKeyToken=b9c8e588f9750854"