Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invoice report with two tablixes; how to approach?


Invoice report with two tablixes; how to approach?

Author
Message
NoDBA
NoDBA
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 461
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
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
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
NoDBA
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 461
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
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search