Expression Editor
The Expression
Editor can create formulas for data display and formatting options. Most
of the properties available for report objects have an <Expression...>
option in the drop-down list. The Edit Expression dialog box has a
color-coded syntax editor with Undo and Paste buttons. It also supports
IntelliSense, so functions display parameter options as you type.
The Treeview
control on the left has a list of syntax categories. Select one of
these to fill the center list of syntax options. The description on the
right panel explains how to use each feature. Double-click the item in
the center panel to have it pasted into the expression. This approach is
very useful for dataset fields and report parameters. The Constants
category is specific to the property being edited and will show numbers,
colors, or other appropriate values.
The example expression in Figure 19 sets the text color based on the value of two other fields.
Code and Custom Assemblies
The
Expression Editor can reference .NET code that is stored with the
report definition or in a custom assembly installed on the developer
machine and report server. Use the Report Properties dialog box to find
the Code and References tabs.
The Code page
presents a simple text editor where you can add multiple instance-based
methods written in Visual Basic. There is no color coding or
IntelliSense. Consider writing the methods in a regular Visual Basic
project and pasting the code into this window. To access the code in an
expression, use the Code class as follows:
=Code.MyConvert(Fields!Price.Value)
You can write
custom assemblies in any .NET language that uses static or instance
methods. The calling syntax is different with a static member:
=MyNameSpace.MyClass.MyStaticMethod(Fields!Price.Value)
Make sure the assembly
is loaded to the Reporting Services bin folder or the global assembly
cache (GAC). Note that custom assemblies are loaded once for each
development session and changes do not appear until Visual Studio is
restarted.
Interactivity
The Reporting Service
delivers static and interactive reports. Much of the interactivity comes
from the use of query and report parameters. These values are known
only by the end user running the report. Parameters can also be supplied
from a table when you run subscriptions (as described later in this
chapter).
Parameters and Filters
Most parameters
are based on data source query input values. The exact syntax needed to
create a parameter depends on the data source type. In SQL Server, the
parameters start with the @ symbol.
The Chap21 solution has
a report sample called Parameter.rdl that includes several query
parameters. In this case, three datasets are created. The Category query
lists all possible categories. The SubCategory dataset has a Filter
expression that asks for the CategoryID. When you run this query on the
Data tab, you are prompted for a CategoryID value. The third dataset is
Product, which also has a filter asking for a SubCategoryID.
When this report is run
on the Preview tab, a drop-down list is created for each parameter. You
must specify a category before the dependent SubCategory list is enabled
and filled. These parameters dynamically build on each other through
expressions (as shown in Figure 20).
On the Layout tab, use the Report menu or right-click on the design surface to show the Report Parameters menu option (Figure 21).
Notice that each dataset with a parameter creates a matching Report
parameter. For each parameter, you can specify the name, data type, and
prompt. If you need to associate data values with a parameter, these
can be hardcoded or derived from a query. Default values can also be
defined in this dialog box. The extra datasets in this report are used
to fill the report parameter from
a query. The order of the parameters is important because, in this
example, a CategoryID is required before a SubCategory can be selected.
This is just one
example of adding interactivity to a report. Parameters have many
purposes in advanced report building. They can appear in titles,
headers, expressions, and other calculations. Reports with parameters
can be linked to each other to create sophisticated drill-down
reporting.
Document Maps
Another interesting
interactive feature is the document map, which you can see on the left
side of the rendered DocumentMap.rdl report sample (Figure 22).
Each control has a
Navigation tab in the property pages. Setting the Document Map entry to a
field or expression causes the map to be displayed. Set the document
map to a different grouping level to get the nested look in the sample.
Document maps render in HTML, XLS (as a cover worksheet), and PDFs (as a
document map).
