Multiple rows into one table
Quick question that probably has a very simple answer but I'm pretty new to SSRS and still working my way through it all. I've got a report that is a sales invoice and therefore pulling information from different tables and putting it all together. I've got the basics down, it's built within a list so it creates a new page for each invoice. The problem I'm having is I have a table within my list to contain the items the customer has ordered, this works but only outputs single rows and then the other rows of items the customer has ordered are outputted to a new report so each item has it's own report. What I need it to do is place all the items ordered by one customer into a single table in one report. SELECT Pub.Invoice.InvoiceNumber, Pub.Invoice.CustomerCode, Pub.Invoice.CustomerPurchaseOrder, Pub.Invoice.CurrencyCode, Pub.Invoice.InvoiceDate, Pub.Invoice.OrderNumber, Pub.InvoiceLine.Description, Pub.InvoiceLine.UomCode, Pub.InvoiceLine.QuantityShipped, Pub.InvoiceLine.NetPrice, Pub.Invoice.ShipAddress1, Pub.Invoice.ShipAddress2, Pub.Invoice.ShipAddress3, Pub.Invoice.ShipAddress4, Pub.Invoice.ShipAddress5, Pub.Invoice.ShipPostCode, Pub.PaymentTerms.PaymentTermCode FROM Pub.Invoice INNER JOIN Pub.InvoiceLine ON Pub.Invoice.InvoiceNumber=Pub.InvoiceLine.InvoiceNumber INNER JOIN Pub.PaymentTerms ON Pub.Invoice.PaymentTermCode=Pub.PaymentTerms.PaymentTermCode WHERE Pub.Invoice.InvoiceNumber = 1 This is my code so far, is there anything obvious that would be making it create a seperate page for each item? I'm using 2008 R2 connecting to a progress Db through an ODBC if that makes a difference.
July 10th, 2012 6:06am
Hi, First you need to add a group on CustomerCode and then in group property, select page break between each group instance. Next, you need to un-check the same check-box for the group done on Invoicenumber. To see how to do this, have a look at "To add a page break to a row group in a table, matrix, or list" in below link.. http://technet.microsoft.com/en-us/library/dd207058.aspx- Chintak (My Blog)
July 10th, 2012 6:21am
Hi Chintak, Thanks for the reply, at the moment the only group I have is the "Details" groups which is automatically created when I inserted my list area. I've tried removing it and grouping on CustomerCode instead and it doesn't work it just shows me the first records for each unique customer code which would be fine if it then inserted the various items for each customer into the table but it doesn't.
July 10th, 2012 6:29am
Hi, I've tried changing the details group properties to group on customercode but it just gives me a single page with the first item row in the table
July 10th, 2012 7:36am
Hi, As you have changed it to CustomerCode, it will now add a pagebreak when Customer changes.. So, are you not getting the rest of rows, or they are on the same page?- Chintak (My Blog)
July 10th, 2012 7:57am
The rest of the rows that would normally be placed onto seperate pages are now being discarded and all it's giving me back is the first page still with only 1 item row in the table.
July 10th, 2012 7:59am
Hi, Ideally, it should not happen. Can you please let me know what if there is anything in filter of detailes group propery and also what's checked on the Page Break tab?- Chintak (My Blog)
July 10th, 2012 8:47am
Hi, There are no filters and all page break boxes are unchecked.
July 10th, 2012 9:01am
Hi, In this case there should be all the rows retruned in the first pageupto InterActive pagebreak limit. Can you try checking the first box "Between each intance of group" on pagebreak tab of details group? - Chintak (My Blog)
July 10th, 2012 9:04am
Hi, That doesn't seem to make any difference. I've attached an image just to help incase I didn't explain very well. At the moment when I run my report all of the boxes return the same information, apart from the table with description etc in which returns the different values for each item which is supposed to be included on the invoice. I just need a way to get the report to display all items with matching information such as invoicenumber and customercode in that table rather than each item on it's own report page. Thanks for all your help so far.
July 10th, 2012 9:11am
Hi, Ok got it. You can disacrd the previously given suggestions. Now, First Check the pagebreak optons on first tablix inside list (Invoice No), and select Tablix Properties as shown below Now, in page break section make sure that non of the boxes should be checked, ot atleast the "Add pagebreak after" should't be checked. Repeat same procedure for cutomerCode tablix. See if this works for you. - Chintak (My Blog)
July 10th, 2012 9:35am
Hi, Invoice Number and Invoice Date aren't a tablix they're text boxes within the list tablix, the customer code, description and payment terms tablixs all have unchecked page break options and still no change.
July 10th, 2012 9:39am
Hi, While looking for it, I come accross this interesting issue. http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/6dbb3a1b-7178-4c1b-9e30-2136f5110ca6/ I will do some some test and update you on this...- Chintak (My Blog)
July 10th, 2012 10:09am
Hi, I'll check the link and see if it solves anything or at least points me in the right direction. I'm not so sure it's a page break issue though. It seems as if reporting services is taking the items as completly seperate things when in reality I want it to see that they have the same invoice,customer and order numbers and place them all in the same table; because at the moment it's doing exactly what I want but treating the tablix for description etc as a txt box and only placing one row into it.
July 10th, 2012 10:14am
I just started a new report so I could have a fresh start and using the exact same sql if I just place a table onto the report this is what I get. Which is exactly what I want but I need it to also display all my other information contained in the screen shot above in a list fashion so I can pull more than one invoice from the db at a time.
July 11th, 2012 4:53am
Hi, I have created similar report and aaded it to my sky drive https://skydrive.live.com/redir?resid=989BE032AA75CCF0!197 - Chintak (My Blog)
July 11th, 2012 6:15am
Hi Tom, Glad to know that.. Kindly revert if you have any more questions in this report.- Chintak (My Blog)
July 11th, 2012 6:55am
My next question is does anyone have any ideas on how to split my reports up into a single page for each parameter. For example the parameters I want to be able to sort by are order no. from to, invoice no. from to and order no. from to. Inputting these at the moment gives me all of the records matching the ranges in one big report, this is obviously no use as I need it to be single reports for the information matching each record found if that makes sense. My first thought was to put everything in a list then use it's grouping to split the information but to do this I need to remove the groups from my other tablixs contained in the report which means I have the same issue I did before with everything going into seperate pages. I think I've explained what I'm trying to achive.
July 19th, 2012 5:42am