Many a time, we need to display varied data from different datasets into one data region. This is now possible by using the Lookup function in a data region.
The Lookup function returns a value corresponding to a related or a common field with the same data type in another data set. It is set as an expression in the Value property of a data region's Textbox. The Lookup function in ActiveReports is similar to the Microsoft Excel's VLOOKUP.
Add Dataset1
- 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.
- In the Add Dataset 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.
- 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- Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.
- 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
Add Dataset2
SQL Query |
Copy Code
|
---|---|
SELECT * FROM T01Result |
The Dataset2 contains following fields:
Property Name | Property Value |
---|---|
FixedSize | 4in, 4in |
Location | 0in, 0in |
DataSetName | Dataset1 |
Cell | Field |
---|---|
TextBox4 | Category |
TextBox5 | ProductID |
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.
Property Name | Property Value |
---|---|
Value | Quantity |
TextAlign | Left |
The final report is shown at the beginning of this page.