Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Sub report -diff param - incorporate acct grouping

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
IO_Dork
Asked:
IO_Dork
  • 10
  • 6
1 Solution
 
mlmccCommented:
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
 
IO_DorkAuthor Commented:
Ok, I'll try that. I already left the office, so I'll try this solution over the weekend.
0
 
IO_DorkAuthor Commented:
when modifying the subreport link, sould I choose the actual accountid field or the grouped accountid summary field?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
I believe you will want the actual field

mlmcc
0
 
IO_DorkAuthor Commented:
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
 
mlmccCommented:
Is there an index on the linking field?

mlmcc
0
 
IO_DorkAuthor Commented:
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
 
IO_DorkAuthor Commented:
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
 
IO_DorkAuthor Commented:
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
 
mlmccCommented:
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
 
IO_DorkAuthor Commented:
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
 
mlmccCommented:
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
 
IO_DorkAuthor Commented:
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
 
mlmccCommented:
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
 
IO_DorkAuthor Commented:
ok, thanks!
0
 
IO_DorkAuthor Commented:
ok, got it to work, just have to learn how to control it.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now