How to join subreport to main report based off subreport's data 1

[choochoowinn2009]

Hi,

Would you please assist me with this? I know what I'd like to see on my end-results, but I'm not quite sure how to go about it. "X"s are the results on my main report, and "Y" is the result on my sub-report.



Here's my data:

ID Service Beg Date End Date
3549 H1111 8/11/2010 8/12/2010
3549 99999 8/16/2010 9/15/2010 x main report
3549 98104 8/18/2010 12/15/2010
3549 B1498 12/16/2010 1/16/2011
3549 A148 8/18/2010 12/15/2010
3549 S99144 8/16/2010 9/14/2010 x main report
3549 H1111 8/18/2010 8/20/2010 y sub-report


1) My sub report is only going to have ID's that have service code of H1111 between beginning beg date of 08/18/2010 and ending of beg date of 08/30/2010.
2) My main report is going to have ID's that have any other service codes where the beginning of beg date < the date of the beg of #1 (which is 08/18/2010)
and where the ending of my end date > the date of the beg of # 1 (which is also 08/18/2010).
3) my end result should be "Y" for the subreport
4) my end results should be "x's" for the main report

 

[lbass]

For the main report, you would just write a formula like this:

if {table.service} <> "H1111" and
{table.begdate} < {?Start} and
{table.enddate} > {?Start} then
"X"

For the sub, you would add links on the ID and on the date parameter {?Start} (be sure to use the dropdown to select the parameter and not the default {?pm-?Start}). Add an {?End} parm to the subreport (you don't seem to need it in the main report) and then modify the record selection formula (report->selection formula->record):

{table.ID} = {?pm-table.ID} and
{table.service} = "H1111" and
{table.begdate} >= {?Start} and
{table.begdate} < {?End}+1

Then add "Y" to the sub report header (if that's really what you want to see) and suppress the other sections.

-LB

 

[choochoowinn2009]

How would you write the IF's formula in the main report if I want to show all data in that "x" row? ( I need to show ID, Service, Beg Date, End Date) ( I meant to say that the "x" is the result row)

Thanks!

 

[choochoowinn2009]

Also, where would I put the formula on my main report?

 

[lbass]

Use a record selection formula like this:

{table.service} <> "H1111" and
{table.begdate} < {?Start} and
{table.enddate} > {?Start}

...although this means that no IDs would appear in the sub that aren't also in the main report--not sure whether that matters to you.

-LB

 

[choochoowinn2009]

I'm confused. Am I putting that formula into my main report or in my report selection record formula? My end results should be that no ID should be in the sub that are showing up in the main report.

 

[lbass]

What? Your example in your first post shows ID 3549 appearing both in the main and in the subreport. Or do you mean service codes?

Yes I meant for you to add that formula in the main report->selection formula->record area.

-LB

 

[choochoowinn2009]

Oh yes, I meant no service codes should appear in both main report and sub report.

when I put that formula in my selection record area, it seems to jumble up since I have other selection criteria as well.

 

[lbass]

I don't know what "jumble up" indicates. Please explain the problem this creates--all it should do is limit the records further.

I guess you could suppress the details with this formula:

not (
{table.service} <> "H1111" and
{table.begdate} < {?Start} and
{table.enddate} > {?Start}
)

...but that would be much less efficient.

-LB