Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invoice report with two tablixes; how to approach? Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 3:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:58 AM
Points: 69, Visits: 438
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
Post #1600522
Posted Thursday, August 7, 2014 8:34 AM This worked for the OP Answer marked as solution
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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.
Post #1600667
Posted Friday, August 8, 2014 1:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:58 AM
Points: 69, Visits: 438
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
Post #1601054
Posted Friday, August 8, 2014 3:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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.
Post #1601338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse