Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Printing Forms by Consolidating SQL - SSRS

RSS
Modified on Wed, Jul 28, 2010, 1:02 PM by Administrator Categorized as SSRS (SQL Server Reporting Services)
This page is a Draft. Its content is not complete and might contain errors.

Overview

SQL Server Reporting Services does not permit placing field values in the Page Header and Page Footer sections of a report, at least not with the Field!FieldName.Value syntax. This article explains a work-around for this in SSRS 2005 by consolidating the SQL into a single statement.

Walkthrough

SQL

  • Consolidate your SQL into a single statement, including header data, detail lines, shipping instructions, comments, etc.

  • Wrap your single statement in a Common Table Expression called MyData, adding the following code which calculates page numbers.

{copytext|div1}
with MyData as (
    -- Your SQL goes here
    )
/*===============================================================================================*/
,a as (
    select
        *
        ,LinesPerPage       = @LinesPerPage
        ,RowNumber          = row_number() over (partition by OrderNumber order by LineNumber)
        ,LinesThisRecord    = 1 + case when Qty = 0 then 0 else 1 end
    from
        MyData
)
/*===============================================================================================*/
,b as (
    /*--- Base Case ---*/
    select
         a.*
        ,LinesThisPage      = LinesThisRecord
        ,PageNumber         = 1      
    from
        a
    where 1=1
        and RowNumber = 1

    /*--- Recursive Case ---*/
    union all select
         this.*
        ,LinesThisPage      = this.LinesThisRecord + case 
                                -- If LinesThisRecord would put us over the limit, ...
                                when prev.LinesThisPage + this.LinesThisRecord > this.LinesPerPage
                                    then 0 -- ... then reset the running total for this page; ...
                                else prev.LinesThisPage  -- ...otherwise, add to the running total.
                                end

        ,PageNumber         = prev.PageNumber + case
                                -- If LinesThisRecord would put us over the limit, ...
                                when prev.LinesThisPage + this.LinesThisRecord > this.LinesPerPage
                                    then 1  -- ... then start a new page; ...
                                else 0      -- ... otherwise, continue with the current page.
                                end
    from
        a this
        inner join b prev
            on prev.RowNumber = this.RowNumber - 1
            and prev.OrderNumber = this.OrderNumber
    )
/*===============================================================================================*/
select
     *
    ,LinesLeftThisPage      = LinesPerPage - LinesThisPage
    ,TotalPages             = max(PageNumber) over (partition by OrderNum)
from
    b
OPTION (MAXRECURSION 1000)

Report Design

  • Place a List control in the Report Body. This will contain all the other controls.

  • Edit properties for the the List control via right click > Properties. Set the Data set name property, then click the Edit details group button.

List Properties Dialog

List Properties Dialog


  • Edit the grouping to be first by a Document ID field (order number, BOL ID, etc.), then by PageNumber. Also, select the Page break at start and Page break at end checkboxes.

Grouping and Sorting Properties

Grouping and Sorting Properties


  • Layout the fields on the report design surface.

  • Add about 30 lines in the Table Footer of the Detail Table. (The number of lines will depend on the number of lines per page on your report.)

  • Enter a sequence of number in the first cell of each line. This step is optional, but it will help you with the next step.

Numbering the Table Footer Lines

Numbering the Table Footer Lines


  • Set the Visiblity > Hidden property of each table footer line to =Last(Fields!LinesLeftThisPage.Value) < LineNumber. For example, the first line will have =Last(Fields!LinesLeftThisPage.Value) < 1

  • To prevent the line numbers from printing, either set the font color to white or clear the cells.

  • Add a Report Parameter called LinesPerPage, with a default value of 25.

  • In your dataset, map it to the @LinesPerPage variable.

  • Find a document ID (invoice number, BOL ID, etc.) with at least 25 line items. Using that document ID, experiment with values of the LinesPerPage parameter till you get the paging working.

  • Using the value from the previous step, set the Default Value of the LinesPerPage report parameter, and make the parameter Hidden.


ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.