ActiveReports 11
Multiple Datasets in a Data Region
ActiveReports 11 > ActiveReports User Guide > Samples and Walkthroughs > Walkthroughs > Page Report/RDL Report Walkthroughs > Data > Multiple Datasets in a Data Region

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.

Lookup Function

Syntax

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

Parameters

Usage

Limitations

This walkthrough explains the steps involved in using multiple datasets in a data region. The walkthrough is split into the following activities:

Note:

When you complete this walkthrough you get a layout that looks similar to the following at design time and at run time.

Design-Time Layout

 

Run-Time Layout

 

To add an ActiveReport to the Visual Studio project

  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.

To connect the report to a data source

  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 the datasets

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

To add controls to the report

  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.

To view the report

OR

See Also

Concepts