Because hierarchical data is structured into a recursive grouping, the process of aggregating values within the grouping works differs from standard aggregation calculations . You can use the same aggregate functions, but to account for the difference in data structure, you include the Recursive keyword as a third argument in the aggregate function .
In this procedure, you create an expression to count records in a recursive hierarchy .
Count records in a recursive hierarchy
1. Click the Design tab, and then, in the third column, right-click the text box in the detail row to the right of [Title], and select Expression .
2. In the Expression dialog box, construct the following expression:
=Count(Fields!EmployeeKey.Value, "Details", Recursive) - 1
When you use the Recursive keyword with an aggregate function, such as the Count function shown here, the aggregation applies to the scope of the specifi ed group . In this example, the scope is the Details row group, which includes each instance of a Count records in a recursive hierarchy
manager record plus the number of employee records associated with that manager . To get the right count of employees for a manager, you must subtract 1 from the result returned by the Count function .
3. In the Expression dialog box, click OK .
4. In the header row above <<Expr>>, type employee Count, and widen the column by dragging the edge of the column handle to the right until the static text displays on one line .
5. Save and then preview the report, as shown here .
Expressions are the key to informative, flexible, and dynamic reports . By using expressions, you can enhance the data retrieved from the data source and modify the appearance and behavior of data in your report . In Chapter 6, you continue to work with expressions and report items properties to add interactive features to your report .
Chapter 5 Quick Reference
To Do This
Add a calculated fi eld to a dataset In the Report Data pane, right-click the dataset, click Add Calculated Field, type a name in the Field Name text box in the bottom row, click the Expression button, and construct an expression for the calculated fi eld by typing the expression or using the expression lists .
Create a text box expression Right-click a text box, click Expression, and construct an expression .
Refer to the value in another text box
Assign a name to the text box (if you want), and then create an expression using the ReportItems collection . For example, to return the value of the BusinessTypeTotal text box, use this expression: =ReportItems!BusinessTypeTotal.Value Add global report information to
the report, such as a report title or page number
From the Toolbox window, expand the Built-in Fields folder, and drag an item, such as Report Name or Page Number to the design surface .
Use a simple expression For a dataset fi eld, enclose the fi eld name in brackets like this:
[SalesAmount], or
For a built-in fi eld, enclose the fi eld name in brackets and precede the fi eld name with an ampersand, like this:
[&PageNumber]
Use an aggregate function Construct an expression for the function by defi ning the func-tion, expression to aggregate, and optional scope, like this:
Sum(Fields!SalesAmount.Value, "ResellerSales") Sort data using an expression In the Row Groups pane, right-click the group to sort, select
Group Properties, Sorting, Add, click the Expression button, and construct an expression on which to base the sort . In the Group Properties dialog box, in the Order drop-down list, select A To Z for an ascending sort and Z To A for a descending sort .
Apply conditional formatting Select a report item on the design surface, and then, in the Properties window, in the drop-down list for a formatting property (such as Color or BackgroundImage/Value), select
<Expression…>, and create an expression using a program fl ow function: Choose, Iif, or Switch .
Create a group variable In the Row Groups pane, right-click a group, select Group Properties, Variables, Add, type a name for the group variable, click the Expression button, and create an expression to provide a value for the group variable .
To Do This
To Do This
Create a report variable On the Report menu, select Report Properties, Variables, Add, type a name for the report variable, click the Expression button, and create an expression to provide a value for the report variable .
Use a group variable or report variable in an expression
Use the following syntax: Variables!VariableName.Value where VariableName is the name of the group variable or report vari-able .
Create a simple report parameter and assign a default value
In the Report Data window, right-click the Parameters folder, select Add Parameter, type a name for the report parameter, and type a prompt string; select Default Values, Specify Values, Add, and type a value .
Create a dynamic connection string
In the Report Data window, right-click a data source, select Data Source Properties, Embedded Connections, select a con-nection type in the Type drop-down list, click the Expression button, and construct an expression to concatenate static text and a report parameter, similar to this:
="Data Source=" +
Parameters!ServerName.Value + ";Initial Catalog=AdventureWorksDW2008"
Create a dynamic dataset In the Report Data window, right-click a dataset, select Dataset Properties, click the Expression button, and construct an ex-pression to concatenate static text and a report parameter, similar to this:
="select col1, col2," +
Parameters!ColumnName.Value + "from table1"
Create a recursive hierarchy group In the Row Groups pane, right-click (Details), select Group Properties, Add, select the primary key fi eld in the Group On drop-down list, click Advanced, and select the parent key in the Recursive Parent drop-down list .
Identify a level in a recursive hi-erarchy
Use the Level function in an expression, like this: =Level()
Aggregate values in a recursive hierarchy group
Specify the name of the recursive group and add the Recursive keyword to an aggregate function, like this:
=Count(Fields!EmployeeKey.Value,
"Details", Recursive)
To Do This
181