Syntax

=Lookup(<SourceExpression>, <DestinationExpression>, <ResultExpression>, <LookupDataset>)

Parameters

• Comparison Criteria: To compare the fields in the Source and the Lookup datasets. The criterion uses only the "=" operator.
• SourceExpression: An expression evaluating to a value from the dataset associated with the data region. If this expression is a FieldName, then the value of the Field from the dataset associated with the data region is used.
• DestinationExpression: An expression evaluating to a value from the dataset associated with the LookupDataset. If this expression is a FieldName, then the value of the Field from the dataset of the LookupDataset is used.
• ResultExpression: An expression evaluating to the Field from the LookupDataset returned by the Lookup function.
• LookupDataset: The dataset where the value from the data region's dataset is used to display the related attribute.

Usage

• The data type of the SourceExpression and the DestinationExpression should be same.
• When the Lookup function is used as a value expression in a data region, the expression is evaluated for each row or repeated data of the data region's dataset.
• The Lookup function returns one value if found, and null if no rows are found in the Lookup dataset.
• The Lookup expressions can be a part of aggregated expressions. A user can use the Lookup function in a table group or table header or footer, and sum all values for the table.

Limitations

• Only "=" comparison is supported between SourceExpression and DestinationExpression.
• Non-aggregate expressions such as multiply, mod, AND and OR, are not allowed in the comparison criteria.
• Only one level of Lookup is allowed, that is, nested Lookup functions are not supported.

1. Create a new Visual Studio project.
2. In the New Project dialog that appears, select ActiveReports 11 Page Report Application and in the Name field, rename the file as SalesResultReport.
3. Click OK to create a new ActiveReports 11 Page Report Application. By default a Page report is added to the project.

See Adding an ActiveReport to a Project for information on adding different report layouts.

1. In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or select Data Source from the Add button.
2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like SalesResultData.
3. On this page, create a connection to the SalesResult database. See Connect to a Data Source for information on connecting to a data source.

To add Dataset1

1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button.
2. In the DataSet Dialog that appears, select the General page and let the name of the dataset be Dataset1. This name appears as a child node to the data source icon in the Report Explorer.
3. On the Query page of this dialog, in the Query field enter the following SQL query.

   SQL Query	Copy Code
   SELECT M01Product.Category, M01Product.ProductID FROM M01Product

4. Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.
5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

   The Dataset1 contains following fields:

   • Category
   • ProductID

To add Dataset2

1. Repeat Steps 1 and 2 to add another dataset with name Dataset2.
2. On the Query page of this dialog, in the Query field enter the following SQL query.

   SQL Query	Copy Code
   SELECT * FROM T01Result

3. Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.
4. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

   The Dataset2 contains following fields:

   • ID
   • ProductID
   • Quantity
   • PDate
   • FY

1. From the toolbox, drag a Table data region onto the design surface of the report.
2. Go to the Properties Window to set the properties of Table data region as follows:
   

   Property Name	Property Value
   FixedSize	4in, 4in
   Location	0in, 0in
   Size	3.875in, 0.75in
   DataSetName	Dataset1

3. Hover your mouse over the text boxes of the Table Details row to access the field selection adorner and set the following fields in the table cells along with their properties.

   Cell	Field
   TextBox4	Category
   TextBox5	ProductID

   This automatically places an expression in the details row and simultaneously places a static label in the header row of the same column.

4. Select TextBox6 of the Table data region and from the Properties pane, set the following properties:

   Property Name	Property Value
   Value	=Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Quantity.Value, "DataSet2")
   TextAlign	Left

   The expression in the Value property returns the value of Quantity from Dataset2, corresponding to the related data field ProductID in Dataset1.

5. Select TextBox3 of the Table data region and from the Properties pane, set the following properties:
   

   Property Name	Property Value
   Value	Quantity
   TextAlign	Left

6. Select the header row using the row handle to the left and in the Properties Window, set the FontWeight property to Bold.

• Click the preview tab to view the report at design time.

OR

• Open the report in the Viewer. See Windows Forms Viewer for further information.