Using 2 date fields in Subreport

[mflahive]

Using CR9 - I am trying to get expenses per month for a pool of NoteID's. I am working on a Subreport which is located in the main report's group header (Grouped by {table.as of date}. Here is how the subreport is setup now:

Group Header 1 {table.As of date}
Group Header 2 {table.expense paid date}
Details (supressed)


I have the following variable in Group Footer 3:

Whileprintingrecords;
Shared Numbervar Exp;

Exp := Exp + sum({table.expenses amount}, {table.expense paid date}, "monthly");

I also have a reset variable for Exp in Group Header 1. The report is formatted for multiple columns because I need to show the Acquired date of the pool and the last four months. I am using the following formula in the record selection to get those dates:

((month({table.as of date}) = month({table.acquired date}) and
year({table.as of date}) = year({table.acquired date}))
or
(month({table.as of date}) >= month(DateAdd ("m", -4, CurrentDate))
and year({table.as of date}) >= year(DateAdd ("m", -4, CurrentDate))))

The test pool I am working with only had expenses in the months of 9/04, 10/04 & 11/04. Using the setup above, my results are just a repeat of the expenses for the inner group dates on the outer group dates. I need the subreport to show zero's for the months there weren't any expenses and show the expenses in the proper months incurred.

I think I just need a way to link the {table.expense paid date} and the {table.as of date} within the report but everything I have tried so far hasn't worked. Please let me know if you have an idea.
Thanks.

 

[Madawc]

I'm not sure I understand what you're after. If you want the subreport to show all of the months, then give it a group based on the month value of {table.As of date}. Have a left-outer link to {table.expense paid date}, so that the month gets shown even if there is nothing on {table.expense paid date} for that month.

Madawc Williams (East Anglia, UK)

 

[mflahive]

I only need to see expenses paid on the date the Pool was acquired and the last four months (from today's date).

If I were to change the inner and outer groups per your suggestion then I would get all of the months where expenses were incurred and only the months that had expenses.

I hope that makes sense and helps you understand the info I am trying to gather. Thank you though
M