SSRS Two Datasets and one of them have page break between instance

[SyahmiCensof098]

Hello there. I am new to SSRS, but I am maintaining it in Microsoft Visual Studio Community 2022, along with the Microsoft RDLC Report Designer extension. I'm having some difficulty keeping two separate datasets on the same page. One dataset has traits that cause page breaks between groups, whereas the second dataset has no such properties. Both datasets contain separate SQL Server queries and cannot be joined. The second dataset has connections to the first dataset. I understand two datasets cannot coexist within same one tablix table. Allow me to break down.

The first dataset has a list of records for items along with all other facts about that item, whereas the second dataset contains a list of people who are responsible for each item in the first dataset. As a result, the records in the Second dataset can be assigned at random to an endless number of two or three people responsible for one item. That is why I am unable to combine two queries of them since if I do, one item may have three people responsible for it, resulting in duplicate things with distinct people in charge.

1. I tried using a rectangle with properties that keep the contents together if at all possible. I also experimented with different combinations, such as placing both tablix tables in the same rectangle, placing the first tablix table outside the rectangle with a page break between each group, and placing the second tablix table inside the rectangle, and many more. However, none of these combinations produced satisfactory results.

2. I experimented with tablix table attributes, including page break options and a variety of combinations, but the results were not satisfactory.

I realize that the header and footer can be repeated if numerous pages are produced, however the tablix table cannot be placed inside the header and footer, unfortunately.

What I'm attempting to accomplish is to display both datasets of tablix tables on the same page, even if one of the tablix tables has a page break between instance. For example, both dataset tablix table should display on each pages but only First dataset tablix table contains different values per pages while Second dataset tablix table shall have same values in each pages.

The current result is that if there are three items, the page break will be three pages with different items from the first dataset of the tablix table, but the second dataset of the tablix table will only display in the last item, which is on page three; if there are four items, the second dataset will display on the last page of the fourth item.

I look forward to hearing from you guys. Thank you!

[Sahir_Maharaj]

Hello @SyahmiCensof098,

Can you please try this approach:

1. Place both datasets in separate tablix tables.
2. Then, configure the page break properties to display the second dataset on every page (while the first dataset tablix continues to paginate through its items.)

I've found that sometimes, the preview doesn’t fully reflect the layout due to rendering limitations in SSRS. I'd recommend to test by exporting to PDF or another format to see the final layout.

Note: if items don’t align perfectly, I'd recommend adjusting the height of the rectangle and padding to control the spacing.

Hope this helps.

[SyahmiCensof098]

Hi @Sahir_Maharaj ,

Appreciate your respond on my post. Unfortunely the solution does not solve the problem. Let me brief why.

From your approach, 

1. Place both datasets in separate tablix tables.
2. Then, configure the page break properties to display the second dataset on every page (while the first dataset tablix continues to paginate through its items.)

1. Let's said i create four datasets and each dataset apply to four different tablix tables

2. Then I follow approach "configure the page break properties to display the second dataset on every page" as above and for Third and Forth tablix tables having same page break properties as Second tablix table.

3. As approach "while the first dataset tablix continues to paginate through its items.", I have configure First dataset page break properties as shown above.

4. Let's said i have three different items which to be expected three differents pages produced.

5. Result of produced report, First page for First item display the First tablix table only.

6. Same goes for Second page for Second item.

7. However, Third pages for Third item display all the tablix table suppose to.

What I want to achieve is that all four tablix tables in report should be on all three pages of those three items just the different values of First tablix table meanwhile all values in Second, Third and Forth tablix table shall remain same on all three pages as intend to be.

Once again, I appreciate you, @Sahir_Maharaj for approach recommendation. Unfortunely the approach does not solve the problems.