Invoice report with two tablixes; how to approach?

Question

Invoice report with two tablixes; how to approach?

NoDBA

Right there with Babe

Points: 720
More actions
August 7, 2014 at 3:10 am

#310013

Hi,
I am trying to create my first report. I have two tablixes (tables) and two datasets. What I am trying to do is an invoice report that exports to pdf and can be sent via email.
The first table/dataset includes information about the recipient such as address, date and invoice_id. The second table/dataset shows the positions, price, tax and so on.
So, what I want to know is how to achieve that both tables are displayed only once on each page of the out-coming report. Is it grouping I have to familiarize myself with on a deeper level?
I tried a different approach with subreports already, but the outcome for both setups always displayed the first table all over the pages and then the second table for the rest of the pages.
If possible I would like to find a solution without subreports, since this is only the first step in creating a report which needed 4 subreports in Crystal Reports. I am worried about performance issues in SSRS with that much subreports and it is performance issues that lead us to the idea to leave CR for SSRS.
I am working with SSRS 2008 R2 and Visual Studio 9 if that is of any importance.
Anything else I need to add for you to help me, please let me know.
Any tip, hint or approach is appreciated.
Thanks

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

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

geoff5 Hall of Fame Points: 3560 More actions · Answer 1

You are on the right track with thinking that grouping is the key to your best solution. My suggestion is to incorporate all the values from the upper tablix dataset into the same dataset you are using for the second tablix. That will result in a lot of redundant information in the second dataset results, but then if you group the tablix by a value they all share (like the primary key for the table that lies behind the first dataset), then put all that information in a header row of that group, it will print only one time for each iteration of that value.

With this solution you can delete the top tablix and just use one tablix grouped by whatever value the top tablix had to identify each row uniquely.

NoDBA Right there with Babe Points: 720 More actions · Answer 2

Thank you very much geoff, it works.

It is quite a query, since each of the two datasets was already joined together from several tables...

And to complete the report, I have to incorporate several more queries, but for the report development side it seems the easiest solution to me.

However, I am curious if there are other approaches that can be taken. E.g. with several tables or subreports?

I know there is no one-fits-all solution, but can you tell, if the "one big query" approach is generally to prefer?

Thanks again, you helped ending a looong struggle.

Flo

geoff5 Hall of Fame Points: 3560 More actions · Answer 3

I would say that in general I try to put as much data into a single dataset as possible to minimize subreports or other even more complex ways of presenting one-to-many data. A single master tablix with grouping levels (sometimes parent/child and sometimes adjacent grouping levels) is my standard approach to any such report design.

I am glad that my suggestion got you past a difficult barrier in design. Nice to know that I could contribute in part to your ultimate success.