Fixed Report Layout

Question

Fixed Report Layout

Jody Wood

SSC-Addicted

Points: 487
More actions
April 19, 2010 at 3:55 pm

#219312

I have a need to create a report that contains a statement and related check details. The location of the check data is specific. The statement may contain many details that force it to a new page. These statement details need to print in the same area on following pages and the check area will be blank. Is this possible in Report Services?
Here is an example:
{Page 1}
Jack Smith Check amount: $100.00
123 Main St Date: 4/19/2010
City, ST 12345
Qty Description Amount Extended
1 Part ABC $25.00 $25.00
5 Part XYZ $10.00 $50.00
{Check section}
4/19/2010
Jack Smith $100.00
123 Main St
City, ST 12345
One hundred dollars and no/100
{Page 2}
Jack Smith Check amount: $100.00
123 Main St Date: 4/19/2010
City, ST 12345
Qty Description Amount Extended
1 Part LMN $25.00 $25.00
{Check section - blank here}
{Page 3}
Tom Wilson Check amount: $50.00
789 Main St Date: 4/19/2010
City, ST 12345
Qty Description Amount Extended
2 Part ABC $25.00 $50.00
{Check section}
4/19/2010
Tom Wilson $50.00
789 Main St
City, ST 12345
Fifty dollars and no/100

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jody Wood SSC-Addicted Points: 487 More actions · Answer 1

I believe I may be able to handle this through the ranking functions, where I determine which page the details need to display on. Just not sure yet how to work the ranking functions to accomplish this yet. Any tips would be helpful. Thanks.

Jody Wood SSC-Addicted Points: 487 More actions · Answer 2

I figured out how to handle this in T-SQL for 2005.

DECLARE @RowsPerPage int

SELECT @RowsPerPage = 2

;WITH TotalPages

AS

(

SELECT [CheckNumber], [TotalPagesPer] = CEILING(CAST(COUNT(*) AS decimal(10,4)) / CAST(@RowsPerPage AS decimal(10,4)))

FROM Table1

GROUP BY [CheckNumber]

)

SELECT

@RowsPerPage AS [RowsPerPage],

CEILING(CAST(ROW_NUMBER() OVER (PARTITION BY Table1.[CheckNumber] ORDER BY Table1.[PaymentDate], Table1.[Amount]) AS decimal(10,4)) / CAST(@RowsPerPage AS decimal(10,4))) AS [Page],

TotalPages.[TotalPagesPer],

Table1.*

FROM Table1

INNER JOIN TotalPages ON

Table1.[CheckNumber] = TotalPages.[CheckNumber]

Hopefully this will help someone else too.

This provides the page of pages for each check number to be used within Reporting Services.

Jody Wood SSC-Addicted Points: 487 More actions · Answer 3

I got my initial idea for this from here.

http://www.easkills.com/ssrs/determinepagenumberinssrs20052008

That source did not quite produce the results I was expecting, so that is when I brought in the use of the casting as a decimal and using ceiling to determine the actual number.