Had this come up with a client today - the problem with SSRS implementing basic “widow-orphan” functionality in a report.

Basically, client had a report by customer/location, that shows individual orders. A single order can take up several lines on the details.

The page break for customer/location works fine, since it has a group page break definition.

The problem is when an order starts near the bottom of the page, and finishes at the top of the next one. It would be great for SSRS to conditionally generate a page break early and show the entire order on the next page, but SSRS doesn’t have the “widow-orphan” protection that’s required there. (They have the “Keep together” group function, but doesn’t generate the kind of widow-orphan protection that’s needed)

There is only one way I've never known to address this. It’s not 100% full-proof, but is far better than nothing. And that’s to determine the max # of orders that can realistically appear on a page, and use that in the T-SQL NTILE() function to derive a new column that divided the rowcount by that maximum #. That new derived columns could be used as a group inside customer/location, but above the order details, with a page break defined when the value changes.

For instance, in the AdventureWorks demo DB….suppose you wanted to show 4 orders on a page by vendor…

select VendorID, PurchaseOrderID, 
   NTILE(   cast( (select count(*)/4 from Purchasing.PurchaseOrderHeader) as int)  )
           OVER (ORDER BY VendorID, PurchaseOrderID) as OrderGroup
from Purchasing.PurchaseOrderHeader
order by VendorID, PurchaseOrderID

The result would look like this:

1492  9        1
1492  88      1
1492  167    1
1492  246    1
1492  325    2
1492  404    2
1492  434    2
1492  513    2
1492  596    3
1492  683    3
1492  766    3
1492  849    3

Again, that last column can be used for a page break.