Newbie needs Crystal 10 Subreport assistance

[cmichailovlee]

I've been using Crystal reports for some time. However, I've only recently run into a situation that requires me to analyze data from different databases that aren't necessarily linked together.

Ideally, I'm trying to link a subreport to the main report using the date. On the main report, I obtain parameters from the user asking for the start date and then the end date. When I link to the subreport using the reference books I have on Crystal 10 and then run the report, the main report comes back with valid data, but the subreport comes back with nothing.

When I look at the sort criteria in the subreport where I usually set the parameters equal to the date field, I get the following formula:

{invoices.purchased_date} in {?from_date} to {?to_date} and
{invoices.purchased_date} = {?Pm-session_viewer.date_visited}

The ?from_date and the ?to_date are parameters that were initially part of the subreport when I had developed it earlier on it's own. However, what concerns me is that after linking in this report as a subreport, it adds that line:

{invoices.purchased_date} = {?Pm-session_viewer.date_visited}

When I take a closer look at this once the report is run, it looks like it's only passing a discrete value rather than a range from the main report. The main report also has a ?from_date and ?to_date parameter that asks the user for information.

So my question is: even if I remove the line:

{invoices.purchased_date} in {?from_date} to {?to_date} and

why this subreport not work? I've tried a number of different ways to get this data. I even tried assigning the parameters from the main report to shared variables, but then I get an error from the subreport saying it can't do that because the formula has to be run later.

Any assistance anyone can provide would be of much help. I've looked at The Complete Reference for Crystal Reports 10 from McGraw Hill and it does a great job of explaining the concept but doesn't go into much more detail than that. I've not found anything of substance out on the Business Objects site, either.

Thanks in advance!

Christopher

 

[kskid]

From the Main report, right click on the subreport and click on change subreport links

From the popup, you want to link {?From_date} and {?to_date}

This should create {?PM-FromDate} and {?PM-to_date} for the subreport. Leave "Select data in subreport based on field" unchecked



Edit the subreport and delete the old parameters you had.
Then click on Select Expert-> Show Formula -> Formula Editor and enter the following

{invoices.purchased_date} in {?PM-from_date} to {?PM-to_date}


That should do it.

 

[cmichailovlee]

That makes a lot of sense, thanks. However, even after linking the subreport to the ?from_date and ?to_data parameters from the main report, I get an error "This field name is not known" when I set the sort criteria to

{invoices.purchased_date} in {?PM-from_date} to {?PM-to_date}

When I go back to the subreport links page, it doesn't show the ?from_date and ?to_date parameters. I try highlighting the parameters, hitting the > button, make sure the checkbox is not checked, then click OK. But it's not saving my choices, therefore it's not able to pass that over to the subreport. Ideas?

 

[kskid]

From the popup, click on {?to_date} on the left side of the screen and then click on ">" button to get it to the right side. The {?PM-To_date} should show up in the lower left (that's what is in CR 8.5 but not sure about CR10)

 

[cmichailovlee]

It does do that initally. But once I hit "OK", the settings are apparently lost. No matter how many times I try it, I can't get it to be set to the main report parameters. Might be a bug in Ver 10.

 

[lbass]

Did you start by removing:

{invoices.purchased_date} = {?Pm-session_viewer.date_visited}

...from the subreport selection criteria? Just leave the line:

{invoices.purchased_date} in {?from_date} to {?to_date}

Then go to edit subreport links and for the main report field, move {?from_date} to the right, and in the lower left, use the dropdown to choose {?from_date} for the subreport (NOT the default {?pm-?from_date}). Repeat for the {?to_date}. At least this is how I would approach it.

-LB

 

[cmichailovlee]

I did manage to get it to work using the method you described above.

However, it returns all the results. Let me try to explain without confusing myself. The main report is grouped by date. The subreport is placed in the group footer. Ideally, I only want the subreport to run and return results that matches the main report's grouping by date. But when I have it set up to do as you mentioned before, it runs the entire range of dates that I initially input and returns it for each instance of the date that is grouped on the main report. Therefore, if I run a date range of 3 days, I'll have 3 grouping results on the main report with a total of 9 results, three for each day on each grouping...

Does that make sense?

 

[BobSuruncle]

Create 2 formulas in your main report.
1. @MinDate
Mimimum({Table.dateField},{Table.GroupField})

2. @MaxDate
Maximum({Table.dateField},{Table.GroupField})

In your Subreport Linking dialog, use these 2 formulas to link the the parameters in the subreport.

Cheers.
Merry Christmas.
Feliz Navidad.
Prospero Ano.
etc.


Bob Suruncle

 

[lbass]

If you have a group on {table.date} also, then you need to add a subreport link that links {table.date} from the main report to {table.date} in the subreport. The subreport should be placed in the group footer (or header) of the date group.

-LB