Subreport in Group Footer of Main Report Not Showing Data

[WhiteKnight2K]

I know I said I wouldnt post another question today but I've got this project to finish and I cant figure out for the life of me why my report setup wont generate the subreport results. Heres the layout of my report
Main Report (contains Year parameter field)
Group By Municipality
Group Header
.
.
Details (displays records (Trees) based on Year parameter)
.
.
Group Footer 1a (contains a summary of data from Details section)
Group Footer 1b (contains a Subreport which is linked on the Year parameter field from the Main Report and displays Firewood records based on Year)

Here's the dilemma, for some reason, if the Main Report displays no records (trees) based on the Year selected, then the Subreport also is blank. However, the subreport should be displaying data (Firewood) on the chosen year. It seems that the Subreport will only display records (Firewood) if the Main Report Details has records (Trees) for the chosen year. This should not be the case, as not all areas will have Trees and Firewood for the chosen year...(i.e. some will only have Firewood).

Any ideas? Could this be a problem with linking? I've tried various linking combinations to no avail.

Thanks in advance for your help!

 

[lbass]

A subreport is like a table added with a left outer join. You can only get records in the subreport that match the linking field in the main report. You probably need to make both trees and firewood subreports. Is there a municipality table that you could use in the main report and that would allow linking of both trees and firewood tables?

I'm making a lot of assumptions about your tables. Maybe you should specify what tables you are using, what fields from each you are using in your report, and how you are linking the subreports to the main report.

-LB

 

[WhiteKnight2K]

Thanks for the reply lbass. Here's some more information:

"I'm making a lot of assumptions about your tables. Maybe you should specify what tables you are using, what fields from each you are using in your report, and how you are linking the subreports to the main report."

Within a Municipality there are many Property. Each Property can have many Tree Groups associated with it. A single Tree Group can either consist of a) Trees, or b) Firewood.

Here's how the tables are related:
Municipality->Property->Tree Group-> Firewood OR Trees

Municipality.MunicipalID is linked to Property.MunicipalID.
Property.PropertyID is linked to TreeGroup.PropertyID.
TreeGroup.TreeGroupID is linked to either Trees.TreeGroupID OR Firewood.TreeGroupID.

Now, I have left outer-joins for all the links (not sure if this is correct...I have a hard time understanding which tables should come first and which should have inner-join or left-outer join links)


Anyway, my Main Report has a parameter field called Year which accepts a range (StartYear and EndYear).

In this report I want to display all existing Trees and Firewood, that fall in the year range, for each Municipality (i dont want to display municipalities that do not have Trees or do not have Firewood)

Now, in the Main Report I have a Group set up on Municipality. Then I have a Details section to list out the Trees that belong to this municipality and fall within the specified year range.
To display the Firewood following all Trees for that Municipality, I had to create a Subreport and I've placed this subreport in the Group Footer of the Main Report.

I've linked the Subreport on Municipality.ID and also on the {?Year} parameter field.

On its own, the subreport generates the records expected. Also, if the Municipality displays Trees in the Main Report, then the Firewood will show up in the Subreport. However, if the Municipality does not display any Trees, then the Subreport will not display the Firewood records, even though there are records.

Please help me to understand where I am going wrong. I figured this would be easy, but I spent most of the afternoon trying different things and nothing has worked so far.

I dont understand why the Firewood will not show up if there are no Trees for that Municipality. It should be independent since the Firewood subreport is linked on the same Municipality ID and year range parameter and its in the Group Footer of the Main Report, so it should show following the Tree records, even if no Trees exist for that Municipality.

 

[WhiteKnight2K]

On a related note, how does the Links window correspond to the report data...i.e. how will outer joins affect data in comparison to inner joins?

 

[lbass]

I think the problem might be with your year parameters. Please specify what {table.field} you are limiting in the record selection formula with your parameters. If it is a date field in your Trees table, you are in effect undoing the left join. I would remove the parameter from the record selection formula and instead, use the parameter to suppress unwanted records in both the main report and the subreport by going to the section expert in each->details->suppress->x+2 and entering something like:

not(year({table.date}) in {?startyear} to {?endyear})

-LB