Grouping results of subreports by date using 2 date fields

[BeeBear]

Hi,

I'm using Crystal V10, and have a report that I want to add a monthly grouping level to.

All the data comes from 3 separate sub reports, and is just all passed through to the main page to display and calculate summaries between the sub-reports.

The main page has a group by location, and the 3 sub reports sit in the group footer.

The main report has a start date and end date parameter, which passed to the sub reports to get the right data.
Unfortunately, each subreport uses the date parameters differently.


SR1 - {Initial_filing_Date} < {?Start_DATE} and
{CLOSING_DATE} >= {?Start Date}

SR 2 - {Initial_filing_Date} > {?Start_DATE} and
{Initial_filing_Date} < {?END_DATE}

SR 3 - {CLOSING_DATE} > {?Start_DATE} and
{CLOSING_DATE} < {?END_DATE}

As you can see, SR 2 uses the {Initial_filing_Date} Field
SR 3 uses the {CLOSING_DATE} Field,
and SR 1 uses both date fields.

So how can I put a grouping in the main report (either above or below the Location grouping) to group the data by calendar month if a longer period of time is selected (eg 01 July - 31 Dec 09)

The user also wants to have the option of NOT splitting the report by month, so as to have a total for the whole date range selected.


Please ask if you need further information.

Would love some help as I am totally stumped!

Had an idea about using the "Select Case" formula somehow, but not sure if that would work, or how.

Thanks in advance

PS - If I dont respond to questions straight off, its because I'm doing training all next week.

regards

BB

*** Count your blessings not your problems******

 

[Madawc]

Group by month as well as location. Put the subreports in the month group footer.

This will increase processing time, of course. Each occurance will be a separate selection of data. But processing time is usually cheaper than human time.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[BeeBear]

Thanks but I dont know HOW to make the group in the main report work, considering that all 3 Sub reports look at the START DATE and END DATE parameters differently:

Sub report 1 uses INIT_FILING_DATE <= START DATE PARAMETER and CLOSING_DATE > END DATE PARAMETER

AND

Sub report 2 uses INIT_FILING_DATE between START DATE PARAMETER and END DATE PARAMETER

and

Sub report 3 uses CLOSING_DATE between START DATE PARAMETER and END DATE PARAMETER


So which date field do I use as the date group in the main report? Or is there some sort of non-database-field I can use to group by month?

If this cant be done, then I have 2 options - dont do the month grouping, or re-write the report to get rid of the subreports and somehow do it all in the one main report.

Thanks



regards

BB

*** Count your blessings not your problems******

 

[Madawc]

If there is a rule for records that defines the relevant date, you could create a formula field that selects the correct data. The main report could then group using that formual field.

Otherwise I don't think it's possible in Crystal.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Link on the location field and also on each parameter field--be sure to link the date parameters by using the dropdown in the lower left to select {?Start_Date} instead of the default {?pm-?start_date}. Repeat for end date. Set up the record selection formula within each subreport to reflect the criteria you mention above.

I am unclear on what the user wants with a group on month. What would be included in each month? What date field do you have to work with in the main report?

-LB