Solved

Sub report -diff param - incorporate acct grouping

Posted on 2013-06-07
16
282 Views
Last Modified: 2013-06-18
Can some one suggest the best way to tackle the following:

I have an invoice report that is grouped by account.  I would like to incorporate a sub report that lists all the investment holdings on page two of each account's invoice.  So for example, first page is an invoice for investments purchased in May, and second page (sub report) details all the investments the account currently holds.  Now, I understand how to create each report separately as I already have done that (an invoice for prior month and a holdings report listing all investments that have already settled and not matured out yet).  How do I combined the two so that the main report prompts me to enter dates for invoices but also prompts for subreport date separately?

Also, I am having trouble getting each group to have the sub report show just the investments related to each account in the grouped section.  Right now it's showing all investments for all accounts in every account grouping, over and over.    

The way it needs to be set up to pull accounts:
I don't enter/select the actual account numbers to be returned, crystal does that by pulling all accounts where the investment is with specific issuers, and where it's a specific type of investment.

Thanks for your help.  Let me know if I am not clear enough on my requirements.

Brian
0
Comment
Question by:IO_Dork
  • 10
  • 6
16 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39230429
To limit the subreport to the account from the main report, link the subreport on it.

Right click the subreport
Click CHANGE SUBREPORT LINKS
Choose the ACCOUNT field from the main report
Make sure the box for SELECT RECORDS BASED ON THIS FIELD is checked
Select the ACCOUNT field from the subreport

TO get 2 prompts just make sure the prompts are different as
Enter Main report date
Enter Subreport date

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39230469
Ok, I'll try that. I already left the office, so I'll try this solution over the weekend.
0
 

Author Comment

by:IO_Dork
ID: 39248564
when modifying the subreport link, sould I choose the actual accountid field or the grouped accountid summary field?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39248568
I believe you will want the actual field

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39248681
seems fairly slow when running against the subreport....what should I be considering to make sure that the report is running as fast and efficient as possible? its over 5 min and still running...there should only be about 50 records.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39248757
Is there an index on the linking field?

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39257172
good question.  I'll look, but everytime I open the report it keeps trying to refresh and access the database automatically and i can't stop it b/c it takes too long for it to start reading records...over half hour, even then its hard to stop it from reading the database.
0
 

Author Comment

by:IO_Dork
ID: 39257193
ok its running faster now albiet still slow.  I had an extra table from another database that was not joined to anything b/c i decided not to use it and forgot to remove it.

However, it still seems to be in a never ending loop of reading database and then reading subreport records.  not sure where to look to fix this.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:IO_Dork
ID: 39257279
in the link tab for the sub report, why do I see the "subreport parameter field to use" in the pulldown start with ?Pm-investor.iaccountid?  Why does it default to this and should I change it to the other one in the list ?iaccountid...?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39257347
When you link a subreport, Crystal creates a parameter for you to use.  In this case, yes change to use the parameter you created.

Where is the subreport?
SOunds like it is in the details section or a group header/footer.

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39257360
i got it to run faster now.  I removed the subreport and reinserted it.  I also rearranged and enforced new link order in the database expert and added index fields in the report bursting index section of CR.

Subreport is in Group Header #1c

The only challenge I have left is trying to use a sum field from the subreport and have it display as a stand alone field in the main report...I guess thats not possible?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39257370
It can be done.

In the main report add a formula in the report header
WhilePrintingRecords;
Shared NumberVar SubReportTotal;
""

In the subreport add a formula to set the value
WhilePrintingRecords;
Shared NumberVar SubReportTotal;
SubReportTotal := Sum({YourField});   // Replace with the value you need displayed
""

In the main report (has to be in a section after the subreport
WhilePrintingRecords;
Shared NumberVar SubReportTotal;
SubReportTotal

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39257377
rats...I need it above the subreport....well I could always make another formula referencing the formula field that has to come after the subreport, no?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39257501
Crystal builds the report in a specific order (top -down), 1 section at a time.
Report Header is built first
Page header is built next and for each new page
Group header (if there is one)
Details - built for each record in the recordset
Group footer (if there is one)
Page footer built at the end of each page
Report Footer - built at the end of the report

Subreports are the last thing evaluated in a section so you can't even use the value in the same section.

If you need the value say in the group header but intend for the subreport to be in the group footer.
You can build another subreport to calculate the value and put it in the group header.

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39257532
ok, thanks!
0
 

Author Comment

by:IO_Dork
ID: 39257554
ok, got it to work, just have to learn how to control it.
0

Featured Post

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

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

13 Experts available now in Live!

Get 1:1 Help Now