Share via


Using Parameters in Expressions

New: 17 July 2006

This topic provides a number of examples of the syntax you need to include references to report parameters in an expression. Report parameters are one of the global collections you can reference from an expression. For more information, see Using Global Collections in Expressions (Reporting Services).

Parameters are evaluated when a report is processed. Expressions can be used anywhere in a report item property or text box property that provides the (Fx) or <Expression> option. For examples of where you can use expressions, see Expression Examples in Reporting Services.

Report parameters can be one of the following types: Boolean, DateTime, Integer, Float, or String. Report parameters are single-value or multivalued. You can set the multivalue property for all types except Boolean. A multivalued parameter is a zero-based array of values. For more information about setting report properties, see Creating Report Parameters and Setting Report Parameter Properties.

Single Value Parameters

The following table provides examples of using a single-value parameter in an expression.

Using a Single Valued Parameter in an Expression

The following table shows examples of the syntax to use when you include a reference to a single value parameter of any data type in an expression.

Example Description

=Parameters!<ParameterName>.IsMultiValue

Returns False.

Checks if a parameter is multivalue. If True, the parameter is multivalue and it is a collection of objects. If False, the parameter is single-valued and is a single object.

=Parameters!<ParameterName>.Count

Returns the integer value 1. For a single-valued parameter, the count is always 1.

=Parameters!<ParameterName>.Label

Returns the parameter label, frequently used as the display name in a drop-down list of available values.

=Parameters!<ParameterName>.Value

Returns the parameter value. If the Label property has not been set, this value appears in the drop-down list of available values.

=CStr(Parameters! <ParameterName>.Value)

Returns the parameter value as a string.

=Fields(Parameters!<ParameterName>.Value).Value

Returns the value for the field that has the same name as the parameter.

Setting a Filter Using a Single Value Parameter

Filters are set using the Filter tab of the property pages for datasets, data regions, or data groupings. You define the filter expression by entering values in the filter grid on the Filter tab. The following table represents the filter grid. The following example assumes the values for Fields!EmployeeID.Value and Parameters!EmployeeID.Value are the same data type.

(Filter) Expression Operator Value And/Or

= Fields!<FieldName>.Value

Any operator that compares one value to another. For example:

=

>=

<

=Parameters!<ParameterName>.Value

Preset value enabled when you enter more than one expression.

Multivalue Parameters

The following table provides examples of using a multivalue parameter in an expression.

Using a Multivalue Parameter in an Expression

Expressions can be used anywhere in a report item property or text box property that provides the (Fx) or <Expression> option.

Example Description

=Parameters!<MultivalueParameterName>.IsMultiValue

Returns True or False.

Checks if a parameter is multivalue. If True, the parameter is multivalue and is a collection of objects. If False, the parameter is single valued and is a single object.

=Parameters!<MultivalueParameterName>.Count

Returns an integer value.

Refer to the number of values. For a single-value parameter, the count is always 1. For a multivalue parameter, the count is 0 or more.

=Parameters!<MultivalueParameterName>.Value(0)

Returns the first value in a multivalue parameter.

=Parameters!<MultivalueParameterName>.Value(Parameters!<MultivalueParameterName>.Count-1)

Returns the last value in a multivalue parameter.

=Split("Value1,Value2,Value3",",")

Returns an array of values.

Create an array of values for a multivalue String parameter. You can use any delimiter in the second parameter to Split. This expression can be used to set defaults for a multivalue parameter or to create a multivalue parameter to send to a subreport or drillthrough report.

=Join(Parameters!<MultivalueParameterName>.Value,", ")

Returns a string that consists of a comma-delimited list of values in a multivalue parameter. You can use any delimiter in the second parameter to Join.

Setting a Filter Using a Multivalue Parameter

Filters are set using the Filter tab of the property pages for datasets, data regions, or data groupings. The following example assumes the values for Fields!EmployeeID.Value and Parameters!EmployeeID.Value are the same data type. Multivalue parameters can only appear in the Value column.

Expression Operator Value And/Or

= Fields!<FieldName>.Value

An operator that tests set inclusion. The only available choice is: IN

=Parameters!<MultivalueParameterName>.Value

Preset value set when you enter more than one expression.

Examples of Referencing Parameters from Custom Code

You can reference the global parameters collection via custom code in a Code block of the report definition or in a custom assembly that you provide. The parameters collection is read-only and has no public iterators. You cannot use a Visual Basic For Each construct to step through the collection. You need to know the name of a parameter defined in a report definition before you can reference it in your code. You can, however, iterate through all the values of a multivalue parameter. For more information, see Using Custom Code References in Expressions (Reporting Services).

Description Reference in Expression Custom Code definition

Passing entire global parameter collection to custom code. This function returns the value of a specific report parameter MyParameter.

=Code.DisplayAParameterValue(Parameters)

Public Function DisplayAParameterValue(

ByVal parameters as Parameters) as Object

Return parameters("MyParameter").Value

End Function

Passing an individual parameter to custom code. This example returns the value of the parameter passed in. If the parameter is a multivalued parameter, the return string is a concatenation of all the values.

=Code.ShowParametersValues(Parameters!DayOfTheWeek)

Public Function ShowParameterValues(ByVal parameter as Parameter)
 as String
   Dim s as String 
   If parameter.IsMultiValue then
      s = "Multivalue: " 
      For i as integer = 0 to parameter.Count-1
         s = s + CStr(parameter.Value(i)) + " " 
      Next
   Else
      s = "Single value: " + CStr(parameter.Value)
   End If
   Return s
End Function

See Also

Tasks

How to: Add, Edit, or Delete a Report Parameter (Report Designer)

Reference

Expression Examples in Reporting Services

Concepts

Using Single-Valued and Multivalued Parameters

Other Resources

Using Custom Assemblies with Reports
Tutorial: Adding Parameters to a Basic Tabular Report
Tutorial: Advanced Features Using Parameters

Help and Information

Getting SQL Server 2005 Assistance