Creating invoice types SSRS reports

  • I was trying to create invoice types reports in SSRS. I was following blog post on creating those invoice layout. In order to get fixed neat layout for invoice type report , I use stored procedure to fill up for empty data rows with the empty lines. Everything is fine until the description lines are getting long which is long enough to drop to next line. At that point, my layout of the table got hay-wired. Because the number of rows are calculated as fixed one line. So, when the row length gets wider,the calculations all became invalid. How should I correct it? The best way is to edit stored procedure? If so,  how can I start amending my stored procedures? Anyone can give me some idea? Thanks in advanced.  This is the article I make a reference : https://www.intertech.com/Blog/use-sql-server-reporting-services-to-generate-an-invoice-document/"> https://www.intertech.com/Blog/use-sql-server-reporting-services-to-generate-an-invoice-document/
    CREATE PROCEDURE [dbo].[uspInvoiceData]( @InInvoiceNbr int ,@InLinesPerPage    int)ASDECLARE @TotalRows intDECLARE @Remainder intDECLARE @NumPages intDECLARE @NextPageRows intset @TotalRows= 0SELECT 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 CarrierNameinto #tempInvoicefrom Orders ojoin Customers c on c.CustomerID = o.CustomerIDjoin [Order Details] d on d.OrderID = o.OrderIDjoin Shippers s on s.ShipperID = o.ShipViajoin Products p on p.ProductID = d.ProductIDwhere o.OrderID = @InInvoiceNbr SET @TotalRows= @@ROWCOUNTIF @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    ENDELSE    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    ENDSELECT * from #tempInvoice order by InvoiceRow ascreturn

Viewing 0 posts

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