Number of Page breaks based on value - SSRS 2010

• mariom 8167

  SSC Enthusiast

  Points: 197

  More actions

  April 25, 2017 at 2:01 pm

  #321087

  Hello

  I have created a shipment label document in SSRS that needs to be repeated X times based on the number of packages linked to a to entire shipment.

  We have a field in our dataset called "Total Number of Packages", and added this field to a table row. 

  The record of each shipment stored on one single row and not multiple rows (1 row for each package). So basically there isn't an iteration that we can link to the Table.

  So basically, is it possible to create a page break based on a value...

  e.g. If the total packages = 3, then create 2 page breaks, so that we have 3 pages (labels) in total.

  I have tried to adapt this procedure- https://sqlserverrider.wordpress.com/2011/06/20/page-break-at-nth-row-in-ssrs-report/ , however this is based on multiple rows, which isn't possible in this scenario.

  I hope this makes sense, and appreciate any assistance.

  Regards

• Lowell

  SSC Guru

  Points: 323504

  More actions

  April 25, 2017 at 2:19 pm

  #1939843

  you can use this trick to join your table with a total against a Tally or Numbers table to generate each label you need.

  it makes it a lot easier, i think: note I slapped to gether the "2 of 4 " message you'd expect on multi box shipments
  
With MySampleShipments(Invoice,Address,TotalNumberofPackages)
as
(
SELECT 'Invoice 1','74 Main Street',4 UNION all
SELECT 'Invoice 2','12 Brown Street',2 UNION all
SELECT 'Invoice 3','23 Running Street',1 UNION all
SELECT 'Invoice 4','45 Scissors Street',3
)

  SELECT  *,N,convert(varchar,MiniTally.n) + ' of ' + convert(VARCHAR,TotalNumberofPackages)
FROM   MySampleShipments
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
     FROM sys.columns) MiniTally
WHERE   MiniTally.n <= TotalNumberofPackages
ORDER BY Invoice,MiniTally.n

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!