Avoiding subreports -- sql server reporting services

Lorenz Ingold
6/27/2007 12:00:00 AM

We have the need of constructing a report with a main data category and two
subcategories, meaning the following: One customer can have n phone numbers
(so it is a 1:n relation from customer to phone numbers), and one customer
can also have n orders (so there is again a 1:n relation from the customer
to orders, but the orders and the phone numbers have nothing to do with each
other). The presentation should be like a grouping: One customer, then a
block with the phone numbers, then a block with the orders, and then the
next customer and so on. We know that using subreports would be the ideal
solution (working perfectly), but it has 2 severe drawbacks: 1. The
performance in case of a lot of data is simply not acceptable, and 2.
Subreports do not export into Excel. We need therefore an other solution. Is
there a way of doing it with grouping? So far we did not succeed; we tried
to insert a table into a footer field of the main table, but even if we
assign there a dataset, we can't select the exact fields of this dataset
(e.g. phonenumber), but only with aggregat functions (e.g.
First(phonenumber), which is not what we want).
What else could be done, if grouping doesn't work? Is the only other
possibility to use the programming extensibility (e.g. building our own data
provider)?

kathryn.buntyn NO[at]SPAM mustangeng.com
6/28/2007 8:39:51 AM

[quoted text, click to view]

Hi Lorenz,

I previously did something really similar by:
(1.) For each eventual group, I created a group on customer. (for
your example of orders and phone numbers, i would have two groups -
but both of them would be grouped by CustomerId).
(2.) I merged the group header row and inserted a table.
(3.) On the inserted table, I created a group (let's say phone number
from your example). I removed the inserted table's header and footer
and put the phone details into the detail line.
(4.) Repeat with your orders, addresses, whatever.

The only problem with this solution is that you will still lose the
details when rendering to Excel. While this solution did work for me,
I felt that I was probably hitting the problem with too complex a
hammer and would love to see someone else's more elegant solution.

Cheers,
Kathy

sql server reporting services : Avoiding subreports