Ssrs Continue Table on Same Page

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:

• add grouping on the report by the common field (from the Fixed Layout Settings with dataset = Dataset1),
• for one of the tables, set the dataset name the same as the report's dataset (DataSet1), and
• for the other table, Table2, set the dataset name as the name of the other dataset, DataSet2, and add a filter using the common field.

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.)

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.)

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.
    

    

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

See Also

newmantwours.blogspot.com

Source: https://www.grapecity.com/activereportsnet/docs/latest/online/multiple-tables-with-different-data.html

Share this post