ActiveReports 16 .NET Edition
ActiveReports 16 User Guide / Samples and Walkthroughs / Walkthroughs / Page Report/RDL Report Walkthroughs / Layout / Binding Multiple tables to Different Datasets in Page Reports
In This Topic
    Binding Multiple tables to Different Datasets in Page Reports
    In This Topic

    You may want to bind tables with different datasets (with at least one common field) to render them together on the same page of a report. In Page reports, this can be achieved using grouping. Now for grouping to apply on two tables, you need to:

    In the first table, grouping is stimulated by the grouping done on the report. In the second table, grouping is stimulated by the filter, thus, grouping the table that is not bound to the report dataset.

    Let us create the following report from scratch and illustrate binding two tables to two different datasets using grouping. The report is grouped by the common field, [ProductID].
    The following image shows the last two report pages where all data corresponding[ProductID]= 1 is rendered. We also set RepeatBlankRows property for both the tables to 'FillGroup' to allow rendering empty rows within a group.

    (For an enlarged image view, open the image in a separate tab.)

    Grouping on Multiple tables in Page report

    The following image shows the first two report pages where data corresponding to [ProductID]= 2 starts to render.

    (For an enlarged image view, open the image in a separate tab.)

    Grouping on Multiple tables in Page report

    Create a Report

    In the ActiveReports Designer, create a new Page report. 

    Bind Report to Data

    Connect to a Data Source

    1. As you create a new report, the Report Data Source dialog appears for you to configure the report data connection. You can also access this dialog by right-clicking the Data Sources node in the Report Explorer and then selecting the Add Data Source option.
    2. In the dialog, select the General page and enter the name of the data source, 'NwindDataSource'.
    3. Under Type, select 'Sqlite Provider'. 
    4. In the Connection String, enter the path of the .db, here, 'NWIND.db', for example
      Connection String
      Copy Code
      Data Source=C:\Data\NWIND.db
      
    5. Click the OK button to close the dialog and complete the data source connection.

    Add Dataset1

    1. Right-click the added data source and select Add Dataset.
    2. In the Dataset dialog, select the General page and enter the name of the dataset, 'DataSet1'.
    3. Go to the Query tab and enter the following query to fetch the required fields:
      Dataset Query
      Copy Code
      select t.CustomerID, ContactName, CompanyName,Address, t.OrderID, t.ProductID, ProductName, CategoryID, Quantity, t.UnitPrice, t.OrderDate from (select *  from (select * from Customers inner join Orders on Customers.CustomerID=Orders.CustomerID) as p inner join order_details on p.OrderID=order_details.OrderID) as t inner join Products on t.ProductID=Products.ProductID order by t.CustomerID
      

    Add Dataset2

    1. Right-click the added data source and select Add Dataset.
    2. In the Dataset dialog, select the General page and enter the name of the dataset, 'DataSet2'.
    3. Go to the Query tab and enter the following query to fetch the required fields:
      Dataset Query
      Copy Code
      SELECT * FROM Invoices
      

     

    Design Report Layout

    1. From the Report Explorer, go to the Report node and click open the Fixed Layout Settings link from the Properties window.
    2. In the FixedPage dialog, go to the General page and select DataSet1 as the Dataset name.
    3. Go to the Grouping page  and enter the expression [ProductID] in the Group on field. The grouping on the report is done by field common to both datasets.
    4. Drag and drop a Table data region (Table1) on the report’s designer.
    5. From DataSet1, drag the following data fields to the Details row of Table1:
      • [OrderID]
      • [OrderDate]
      • [CompanyName]
      • [OrderID]
      • [ContactName]
      and modify the table headers accordingly.
    6. With the table selected, set RepeatBlankRows property from the Properties window to 'FillGroup'. This will allow rendering empty rows within a group.
    7. Drag and drop a Table data region (Table2) on the report’s designer.
    8. From DataSet2, drag the following data fields to the Details row of Table2:
      • [Shippers_CompanyName]
      • [ShipCity]
      • [ShipCountry]
      and modify the table headers accordingly.
    9. With Table2 selected, click the Property dialog link to open the Table dialog.
    10. Go to the Filters page and click Add to add filters. We need to add a filter, which is a field common to both datasets.
    11. Enter [ProductID]as the Expression, Equal as the Operator, and [ProductID] as Value.
    12. With the table selected, set RepeatBlankRows property from the Properties window to 'FillGroup'. This will allow rendering empty rows within a group.
    13. Add two text boxes on the top of the table and their Value property to the following to add information about the page numbers and the product ID on each page:
      • TextBox1: "Page " & Globals!PageNumber & " of " & Globals!TotalPages
      • TextBox2: ="Product ID: " & Fields!ProductID.Value

      The following image shows the design-time image of the report. 

      Grouping on Multiple tables in Page report

    14. Preview the report.
      The final report is shown at the beginning of this page.
    See Also