Starting point for formula using subreport and main report values

[tubbers]

Hi, I'm using Crystal XI with a SQL Server MDB. I have read only privileges so can't create views or stored procedures.

I have a main report (grouped by Patient Account) and subreport and I need to pass a value from the main report detail section to the appropriate record in the subreport detail section which is placed in the main report's Group Footer 1a.

The links for the main/sub reports are:

{CDRReport.UniversalServiceCode} = "3400162" and
{CDRPatient.PatientAccountNumber} = {?Pm-AHI_PATIENT.Site_Patient_Id}
and
{CDRReport.ObservationDateTime} >= {?Pm-@First Order}

[b]@First Order [/b]
Minimum ({AHI_ORDER.Start_Datetime},{AHI_PATIENT.Site_Patient_Id})

Here's a sample:

[COLOR=red][b]GH 1[/b][/color] 
Patient Name:
Acount Number:
[COLOR=red][b]D[/b][/color]    
Begin DTTM     End DTTM     Give Rate
9/3/06 8:32    9/5/06 21:03    17 [COLOR=green]**value to pass[/color]
9/5/06 21:05   9/6/06 8:47     16
9/7/06 12:58   9/7/06 19:10   
9/7/06 19:12   9/7/06 23:20    17
9/7/06 23:22   9/8/06 12:01    15
9/8/06 12:04   9/8/06 19:13    16
9/8/06 19:16   9/9/06 2:53     17
9/9/06 2:56    9/11/06 10:27   19
[COLOR=red](end detail section)
[b]GF 1a[/b]  (begin subreport)[/color]
Observation DTTM     Results   Range   Tiration   Give Rate
9/3/06 15:00         66        N       1          [COLOR=green]17[/color]
9/4/06 5:15          70        Y       2          [COLOR=green]17[/color]
9/5/06 4:50          123       N       3          [COLOR=green]17[/color]
9/5/06 13:53         105       Y       4          [COLOR=green]17[/color]
9/6/06 4:50          66        N       5          [COLOR=green]16[/color]
9/6/06 21:15         84        Y       6          [COLOR=green]off[/color]
9/7/06 5:00          62        N       7          [COLOR=green]off[/color]
9/7/06 13:00         116       N       8          [COLOR=green]titrate[/color]
9/7/06 22:26         152       N       9          [COLOR=green]17[/color]
9/8/06 5:00          65        N       10         [COLOR=green]15[/color]
9/8/06 18:15         68        N       11         [COLOR=green]16[/color]
9/9/06 1:10          65        N       12         [COLOR=green]17[/color]
9/10/06 5:10         76        Y       13         [COLOR=green]19[/color]
9/11/06 5:30         107       Y       14         [COLOR=green]19[/color]
[COLOR=red]**end subreport
GF 1b[/color]
Number of tirations: 12
Time to therapeutic range (in hours):  ~217

In the subreport, Tiration is a running total field and the number is passed back to the main report and used in the group footer.

What I need to do is pass the Give Rate from the main report down the the appropriate record in the subreport based on the Start and End times of the order and the time the test was taken. If a test was taken while an order was stopped, then the give rate in the subreport should say "off".

I believe the ultimate goal is to get this information down to the subreport so that the detail section of the main report can be supressed.

I'm not sure that I've even set this report up properly to achieve this.

Is there a way to accomplish this? I apoligize in advance if there is not sufficient information or if this is confusing.

 

[lbass]

I think you would be better off making the subreport your main report, and then using the current main report detail data in a subreport that is placed in the detail section and linked on the patient ID. Also use {CDRReport.ObservationDateTime} as a linking field, and in the subreport record selection area, make sure that section of the formula looks like:

{table.begindttm} <= {?pm-CDRReport.ObservationDateTime} and
{table.enddttm} >= {?pm-CDRReport.ObservationDateTime}

Then you could suppress all sections/fields except the give rate field which would display in the detail section of the main report.

-LB

 

[tubbers]

Thank you, I was able to make the report work in the way I needed to by switching the main report and subreport like you suggested.