Post reply

Number of Page breaks based on value - SSRS 2010

  • 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

  • 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!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply