Struggling to generate an invoice or other form-based document in SSRS to match a paper form? This can be challenging, but getting the following things right definitely makes it easier:

• Getting the proper number of records to print per page
• Breaking the page in the proper place.
• Filling blank area of the form with blank form.

I’ve also found that the use of a table control allows us to generate detail lines for the invoice. However, the table control will only generate lines for records that are returned from the data source.

As far as I can determine, there is no native way to pad a table with any number of records to show blank records. Nor could I fill to the end of the form rows to achieve a fixed repeat area independent of the number of actual data records.

Then I discovered a solution: a stored procedure. The stored procedure can generate blank rows to fill in the form when the number of data records do not match exactly what the form needs.

Example:

The invoice has 3 detail records, while the form has room for 20 detail records. By default (with no special processing), the form generated would have 3 detail lines with column headings and totals after (see Figure1).  The generated form will not replicate the stock preprinted paper form (columns going to the bottom with totals at the bottom).

This example is using the Northwind Database, which you can download from Microsoft.

< Figure 1>

While this may be good enough in some cases, replicating  a standard paper form requires extending the columns to the bottom of the page. In order for the columns to extend to the bottom of the page, we need to add a specific amount of blank records.  This is where the stored procedure comes to the rescue.

Figure 2 shows the end result. The columns flow to the end of the page and there is exactly 20 lines of detail.

If there were more than 20 lines a new page would be generated based on the page break on the calculated field of page.

 < Figure 2>

The Stored Procedure

There are two key parts of the stored procedure.

Part 1 is the setting of the Invoice Row field. This will be the current row number of the data set returned.

Part 2 is the generation of blank lines to fill out the last page of the invoice.

CREATE PROCEDURE [dbo].[uspInvoiceData]
( @InInvoiceNbr int
 ,@InLinesPerPage	int
)
AS
DECLARE @TotalRows int
DECLARE @Remainder int
DECLARE @NumPages int
DECLARE @NextPageRows int
set @TotalRows= 0
SELECT 
 ROW_NUMBER() OVER( ORDER BY d.ProductID ) as InvoiceRow
,o.OrderID
,o.OrderDate
,o.Freight
,o.ShipAddress
,o.ShipCity
,o.ShipCountry
,o.ShipName
,o.ShipPostalCode
,o.ShipRegion
,c.CompanyName
,c.ContactName
,c.Address
,c.City
,c.Region
,c.PostalCode
,c.Country
,d.Quantity
,d.UnitPrice
,d.Discount
,p.ProductName
,p.ProductID
,s.CompanyName CarrierName
into #tempInvoice
from Orders o
join Customers c on c.CustomerID = o.CustomerID
join [Order Details] d on d.OrderID = o.OrderID
join Shippers s on s.ShipperID = o.ShipVia
join Products p on p.ProductID = d.ProductID
where o.OrderID = @InInvoiceNbr

SET @TotalRows= @@ROWCOUNT
IF @TotalRows=0
	BEGIN
		WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
			BEGIN
				SET @TotalRows= @TotalRows+1
				INSERT  #tempInvoice
				(InvoiceRow
				,OrderID
				,CompanyName
				,CarrierName
				,Quantity
				,UnitPrice
				,Discount
				,ProductName
				,ProductID)
				 VALUES 
				 (@TotalRows
				 ,@InInvoiceNbr
				 ,''
				 ,''
				 ,0
				 ,0
				 ,0
				 ,''
				 ,0)
			END
	END
ELSE
	BEGIN
		SET @Remainder =  @TotalRows%@InLinesPerPage -- get remainder
		IF @Remainder !=0 
		BEGIN
			-- Get the current page increase by 1 becasue we have a remainder.
			SET @NumPages = @TotalRows/@InLinesPerPage  +1 
			SET @NextPageRows = @NumPages * @InLinesPerPage
			WHILE @TotalRows < @NextPageRows -- Add Blank Rows
			BEGIN
				SET @TotalRows= @TotalRows+1
				INSERT  #tempInvoice
				(InvoiceRow
				,OrderID
				,CompanyName
				,CarrierName
				,Quantity
				,UnitPrice
				,Discount
				,ProductName
				,ProductID) 
				VALUES 
				(@TotalRows
				,@InInvoiceNbr
				,''
				,''
				,0
				,0
				,0
				,''
				,0)
			END
		END
	END
SELECT * from #tempInvoice order by InvoiceRow asc
return

The Page Break in the Report

To get a consistent page break after a number of detail records, the dataset has a calculated field defined.
Calculated field: Page
Expression is shown in the figure below
The expression will increment the page number based on the current row (Fields!InvoiceRow.Value) and the number of rows wanted per page (Parameters!pNumRowsPerPage.Value).

=iif(Fields!InvoiceRow.Value   Mod Parameters!pNumRowsPerPage.Value=0 
,Fix(Fields!InvoiceRow.Value   / Parameters!pNumRowsPerPage.Value)
,Fix(Fields!InvoiceRow.Value /   Parameters!pNumRowsPerPage.Value +1))

The invoice detail table format options.

Creating columns with no bottom lines.

By default the table presents borders on all sides of the columns and rows. To modify this you need to set the BorderStyle Top and Bottom to “None” in the row details. This will give the effect of continuous columns.

Create alternate background colors for detail lines.

A nice form presentation effect is to alternate the background color of rows.
The toggling of the background color for each detail line can be accomplished by adding an expression to the
BackgroundColor property of the detail line cells.
This will toggle the background color between WhiteSmoke and White.

Conclusion

This report was designed to print a single Invoice at a time, but with some minor enhancements to the store procedure we could support printing multiple invoices at one time.