Combine Multiple SSRS Reports

Question

Post reply

Combine Multiple SSRS Reports

myjobsinus

SSC Eights!

Points: 964
More actions
January 27, 2014 at 4:12 pm

#282742

Hi All,
I have 2 different SSRS reports as following.
Report 1
Customer--------------Orders--------------Order Date
Customer A ---------------5-------------------01/01/2014
Customer A ---------------2-------------------01/11/2014
Customer B ---------------7-------------------01/15/2014
Customer c ---------------4-------------------01/19/2014
Report 2
Customer --------------Order Totals
Customer A -------------------$2478.44
Customer B -------------------$487.36
Customer C -------------------$7896.93
Now i want to combine these 2 reports into 1 as following so that our client can print both the reports for same customer and post it out to them. without having to manually run multiple reports , print them and put into a package to send out by post.
Combined Report
Customer A
Report 1
Customer--------------Orders--------------Order Date
Customer A ---------------5-------------------01/01/2014
Customer A ---------------2-------------------01/11/2014
Report 2
Customer --------------Order Totals
Customer A -------------------$2478.44
--next page
Customer B
Report 1
Customer--------------Orders--------------Order Date
Customer B ---------------7-------------------01/15/2014
Report 2
Customer --------------Order Totals
Customer B -------------------$487.36
--next page
Customer C
Report 1
Customer--------------Orders--------------Order Date
Customer c ---------------4-------------------01/19/2014
Report 2
Customer --------------Order Totals
Customer C -------------------$7896.93
--------------------------------END--------------------------
Any idea how i can get this done, i have tried to use sub reports but not able to split the data within subreports for specific customers. any help will be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

, i have tried to use sub reports but not able to split the data within subreports for specific customers.

If your main report is just (CustomerID, CustomerName, OrderCount, OrderDate)

and your subreport is just (CustomerID, OrderTotal)

then you probably can do it with a single dataset and a single tablix.

If you create a query without the precalculated OrderTotals, then you could have a query something like this:

Customer(CustomerID, CustomerName)---Order(OrderID, OrderDate, CustomerID (FK))---OrderDetails(odOrderID, ItemID, Qty, Price)

then you could base a subreport on tables Order and OrderDetails, and do the totals in there (for the OrderCounts by OrderDate), but be sure to include the CustomerID in the dataset, because you're going to need it. (I'm getting to that). You would need to create a parameter in this (the subreport), so that you can link it to the CustomerID in the Customer's report (the outer report).

I found this video tutorial to be really helpful. (Apologies if links to youtube are frowned upon!)

http://youtu.be/6S8Zzqthsbw It's all of 7 minutes, and he walks you through setting up a main/subreport

myjobsinus SSC Eights! Points: 964 More actions · Answer 2

Hi pietlinden,

Thanks for your swift response. basically i have around 7 to 8 very complex reports that i need to merge. in my explanation i just tried to pick some sample simple scenario to better explain the functionality i am looking for. i can not combine all the data sets.

Many Thanks,

Alvin

pietlinden SSC Guru Points: 63503 More actions · Answer 3

You can create one "outer" report that acts as a container for the subreports, and then use a tablix for the subreports (or just place them, if you want) Just make sure you set up the parameters correctly on the subreports.