Loopig Each Record in dataset

Hi,
My report requirement of customer activity report.
For each customer, my report should show the summary by purchase
catgory and then individual transaction details.
Customer Name: Tom Tim
Books: 100
Computers : 2
Others: 23
(then all 125 transactions should be listed).
Same summary and detail should be repeated for each customer in the
same single report. (there are aound 200 customers)
How can I achieve this? (other than Sub report). Sub-report seems to be
very slow in rendering? Also If I use List control, it does not allow
table inside list with out aggregate.
So I couldn;t use list control
Thanks,
VasanthSubreport is how you have to solve this. Make sure that you have appropriate
indexing so the subreport query runs quickly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1137791468.342737.278100@.g47g2000cwa.googlegroups.com...
> Hi,
> My report requirement of customer activity report.
> For each customer, my report should show the summary by purchase
> catgory and then individual transaction details.
> Customer Name: Tom Tim
> Books: 100
> Computers : 2
> Others: 23
> (then all 125 transactions should be listed).
> Same summary and detail should be repeated for each customer in the
> same single report. (there are aound 200 customers)
> How can I achieve this? (other than Sub report). Sub-report seems to be
> very slow in rendering? Also If I use List control, it does not allow
> table inside list with out aggregate.
> So I couldn;t use list control
> Thanks,
> Vasanth
>|||The performance is very poor when we try to render in PDF. We checked
SP (in sub-report) which takes 1-2 seconds for each customer. But
rendering that takes more than 30 mins depending on data.|||Your question was initially about subreports. Based on what you want to do,
subreports are the way to do it. There are differences with rendering
different formats. HTML and CSV are very fast, PDF and Excel are much slower
(order of magnitude slower). Your hypothesis that the problem is the
subreports most likely is not the issue. The issue with PDF is either
complexity or the size of the data (number of rows and size of rows). Also,
do you have any images? If so, what format is the image in. Try the report
without images and see if that helps. If you want to know how much the
optimal time is then render it to html and see how long that takes. That is
the best case.
Is this for a user viewing on the screen or is this destined for printing?
If it is the user then try using drill through. Drill through is quite fast.
Also, does it have to be pdf? As I said, html is much faster.
RS does all rendering in memory. So you can also try adding additional RAM
to see if that would help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1137851207.701517.281770@.g14g2000cwa.googlegroups.com...
> The performance is very poor when we try to render in PDF. We checked
> SP (in sub-report) which takes 1-2 seconds for each customer. But
> rendering that takes more than 30 mins depending on data.
>|||Thanks Bruces
1) Total records: 20,000 + ( 100 customer * 200 recods average)
2) This is purely for audit record keeping purpose. Not online report
3) We do not have any images
4) HTML report is coming quickly around 10 mins. PDF seems to be the
killer.. Takes for ever. (HTML size is ? 50 MB which we do not prefer)
5) We tried another sample report that just display 20,000+. PDF comes
in 5-10 mins. But if the same 20K+ records have been run with
sub-reports, it goes down in performance.
5) RAM is 4 GB|||RAM is fine. If it takes 10 mins for html and 30 for PDF then you are doing
pretty good. Usually the difference is greater than that. You might be able
to do something to get the data down quicker (playing with indexes) but that
would only affect at most 10 mins of the total time. The extra 20 minutes is
PDF rendering issues. My suggestion is to just schedule it to run at night.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1138029125.861955.154170@.o13g2000cwo.googlegroups.com...
> Thanks Bruces
> 1) Total records: 20,000 + ( 100 customer * 200 recods average)
> 2) This is purely for audit record keeping purpose. Not online report
> 3) We do not have any images
> 4) HTML report is coming quickly around 10 mins. PDF seems to be the
> killer.. Takes for ever. (HTML size is ? 50 MB which we do not prefer)
> 5) We tried another sample report that just display 20,000+. PDF comes
> in 5-10 mins. But if the same 20K+ records have been run with
> sub-reports, it goes down in performance.
> 5) RAM is 4 GB
>