loop through records.. alternative to nested subreports? 1

[Tracey]

Hi

I have a report which based on one company's records, and contains two subreports.

Subreport1 shows all incidents which have occured between two date ranges for this company, based on a one to many link. Incident table has companyid and dates.

Subreport2 shows all hazards and associated recommendations where the hazard has been entered in the daterange, OR the recommendation for that hazard is in the range. This is a one to many, hazards are linked to recommendations via a foreign key hazardid in recommendation table.

My problem now is that i also need to list all workzones that hazard is present in. This is held in the database as a many to many, wokzones linked to hazard via hazardworkzone junction table.

What i guess i would like to do to be able to display this info in a group after (or before) the recommendations (which are in the details section of my subreport), is to have a formula that loops through the records for this hazard's workzone and print them out at every run through hazards. can this be done? I was thinking something along the lines of:

while not eof do
first
if hazardworkzone.hazardid = hazard.hazardid then
{workzone.workzonename}
next

is this possible, even practacle? I know i cant nest subreports and cant think of another way to do this.

 

[IanWaterman]

Tracey

Can you group the main report on Hazard_id.

If yes you could then insert your second subreport into this group, and also link main report to subreport on this hazard ID. Subreport will then run for each Hazard_id.

Split the Main report hazard_id group into two sections, subreport in top section. Then create another subreport also linked by hazard id, that looks at your workzones.

You should then get Hazards, with recommendations with workzones listed underneath.

Hope this is clear.

Regards

Ian Waterman
UK Crystal Consultant.

 

[Tracey]

eek
the first thing is that when i add a section based on hazardid and put my sub2 in that, i then get the headings of sub2 for every hazard, even tho my selection criteria have ruled it out. (as u said, the subreport prints out for every hazard, whether it fits the criteria or not)
I tried suppressing the headers etc as explained in thread149-56495 but then i get none or all... grrrrrr

Here is my sub2 selection criteria:

(({HAZARD.NOTIFICATIONDATE} in {?Pm-?LowDate} to {?Pm-?HighDate}) or
({RECOMMENDATION.SYSTEMDATE} in {?Pm-?LowDate} to {?Pm-?HighDate})) and
{HAZARD.HAZARDID} = {?Pm-HAZARD.HAZARDID}

Im getting more and more lost in this one..

 

[IanWaterman]

Tracey

Your selection criteria looks OK and should only allow the subreport to run for the main group Hazard_id.

The only ting I would do different on your selection is to wrap the in ranges in [ ] brackets.

(({HAZARD.NOTIFICATIONDATE} in [{?Pm-?LowDate} to {?Pm-?HighDate}]) or
({RECOMMENDATION.SYSTEMDATE} in [{?Pm-?LowDate} to {?Pm-?HighDate})]) and
{HAZARD.HAZARDID} = {?Pm-HAZARD.HAZARDID}

Try restricting main report to a single hazard Id and see if that has an affect on the subreport (it should).

Ian

 

[Tracey]

yes, thats right
if i restrict it to one hazardid i get only the one. But I need to see all hazards which were entered within the daterange or whose recommendations were entered in the daterange....

 

[IanWaterman]

This is puzzling, your subreport link works OK when the main report is limited to Hazard_ID. But when you allow all hazards in the main report the sub report brings back all hazards, when if linked properly and placed within the main report Hazard_id group it should only run for that specific Hazard_id.

If the report is not too big, email a copy to me with some sample data, and I will take a look.

ian_waterman@hotmail.com

It should be working as you describe it.

Ian

 

[Tracey]

cheers Ian... thanks for taking the time.. its on its way