Solved

Added Subreport to detail section but report is too slow

Posted on 2011-09-07
7
627 Views
Last Modified: 2012-05-12
I have a report that shows a master record and need to show several line details form 2 different tables. If I use joins and groups then the data repeats. I added a subreport to show the details from table 2 while table one is the detail of master (group table) . Report takes long time to populate the subreport linked with invoice id (Subreport is a SQL command)  What can I do to make it faster?

sample
Master table invoce
Invoice 10001
details 1
details 2
details 3
details 4

Subreport Invoice 10001
service 1
service 2
service 3
0
Comment
Question by:sweetbuttercup
  • 3
  • 3
7 Comments
 
LVL 19

Accepted Solution

by:
GJParker earned 250 total points
ID: 36496704
The performance degradation is down to using the subreport in the deetails section of the main report, it's not clear how you are using the data from the sub, but try creating a group on Invoice ID and move teh subreport to the group header. If this doesn't suit your situation then it may be better to revert back to linking the tables and handling the record duplication with running totals.

HTH
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 36496800
Or if you need it after the main details put the subreport in the group footer.

When you put a subreport in the detail section it runs for every record in the report.

What are the tables and how did you join them?

mlmcc
0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 36497446
The report is organized in 4 group levels. Is ther any way to suppress the detal level if G4 count > 1 then show it in the last record of G4?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 100

Expert Comment

by:mlmcc
ID: 36497579
Put it in the G4 footer.  It will show after the details is complete.

mlmcc
0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 36497645
I tried moving the sub report to the GF but it's still slow. I'm trying to get rid of the sub-report and use only Groups- joins. That's why i'm asking if there's any way to suppress the detal level if G4 count > 1 then show it in the last record of G4?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36497796
Yes.

Try using

Count({SomeField},{G4GroupField}) > 1

SomeField is a field that will have values if there are records like the primary key
G4GroupField - The field G4 uses for grouping

mlmcc
0
 
LVL 1

Author Closing Comment

by:sweetbuttercup
ID: 36504952
No matter where I placed the sub report, the performance was affected so couldnlt use the sub report option. I added more table links to the same table (service) with alias to be able to show ll the results linked to line 1, line 2, line 3,... then created a formula to concatenate the results and show it.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now