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.
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.
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.
< 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.
|=iif(Fields!InvoiceRow.Value mod 2=0,”WhiteSmoke”,”White”)|
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.