March 13, 2017 at 3:56 am
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