Create Group Based On StartDate and EndDate 1

[MadCatmk2]

Hi all

Crystal 9.0
Access 97 Database

Hope someone has come across this idea and may be able to shed some light on this.
I have a report that is ran between selected date ranges. What i would like to be able to do is to create a group that returns the months that are covered within the date range. I.e. if the StartDate is 1/1/04 and EndDate is 30/4/04 i would like there to be groups on the report showing January, February, March, and April. I eventually want to show the dates just incase a whole month is not encompassed in the range but this would get me started if anyone has any ideas.

Hope this makes sense.

Thanks in advance

 

[glalsop]

Go to Insert -> Group, and select {YourDateField} as the field to group by.

In the 'The section will be printed:' area, select 'for each month'.

To get the group header to look correct, you will probably also want to select 'Customize Group Name Field', and use a formula such as:

monthname({YourDateField}) + totext(year({YourDateField}),0)

or something similar.

This is in Crystal 8.5, I imagine 9 is very similar.

-Gary

 

[MadCatmk2]

Thanks for the quick response. I apologise for what might be a stupid question, but its late in whats been a long day :O) but here goes:

I see what your saying with your suggestion. What would {your date field} be? At present, i have two date formulas, {StartDate} and {EndDate). I'm not sure of how to set up a formula with these two dates as a range that i can run a group command on.

Thanks for the help so far

 

[glalsop]

{YourDateField} is the name of the field in the table that you want to select records from, in the format {TableName.FieldName}.

So use the same field that you are using in the record selection based on its being between {?StartDate} and {?EndDate}.

-Gary

 

[MadCatmk2]

I was wondering if thats what you meant. As it is, i have to tell you that its not quite that easy.

The record selection can pick records (in this case referrals) that are opened prior to the first date (The reason for this is that they remain open as we move into the date range) and get closed either during the chosen date range or after this. Due to this i get groups that may be for referrals that are years old. All i want is the months encompassed by the date range, from that i will perform the necessary counts of those referrals created prior to the dates.

Does this make sense? its confusing me at present, i do know what i am trying to say but can't quite explain it.

Thanks again

 

[glalsop]

So how do you determine when they were closed without a CloseDate field in your table?


-Gary

 

[glalsop]

The reason that I ask is because if you don't have a CloseDate field, then you are most certainly going to need to get your data squared away within Access before you try to report on it.

If you do have a CloseDate field, then why can't you just use this field instead of OpenDate?

-Gary

 

[Ngolem]

It really depends on your report length....do you cross from one year to the next...or more than one year???

Also what do you want to do if some months don't have data. This is really a tough thing to handle...and it can be handled to some extent by faking group headers for missing months....but based on the info shown here I CANNOT GUESS WHAT YOU WANT.

As far as the grouping goes I would group on this formula

//@GroupDate

StringVar result := "";

if month({Table.date}) = 10 then
result := totext(year({Table.date}),0,"","") + "A"
else if month({Table.date}) = 11 then
result := totext(year({Table.date}),0,"","") + "B"
else if month({Table.date}) = 12 then
result := totext(year({Table.date}),0,"","") + "C"
else
result := totext(year({Table.date}),0,"","") +
totext(month({Table.date}),0,"","");

result;

That shud sort your data properly....if that is all you want.



this adds the year to the grouping so that the months will be ordered across a split

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

If you want to display all records open during the parameter period, you could create a record selection formula like:

{table.opendate} <= {?enddate} and
(isnull({table.closeddate}) or
{table.closeddate} >= {?startdate})

Then you could create a formula {@inperiod}:

if {table.opendate} < {?startdate} then
date(0999,09,09) else
{table.opendate}

Then group on {@inperiod} and choose "Print for each month" and choose customize group name->use a formula for group name and enter:

if {@inperiod} = date(0999,09,09) then "Opened Before Selected Period" else totext({@inperiod},"MMM yyyy")

This will give you a separate group just for those which opened before the parameter period, which will make it easier to do summaries.

-LB

 

[MadCatmk2]

Thanks everyone for you're suggestions, i'll give them a go and see if they give me what i'm looking for failing that its back to the drawing board and i'll try to explain the problem better.

Glasop: In answer to your question, there is a Discharge_Date field in the table.

Thanks again.

 

[MadCatmk2]

I am going to try an explain the layout of the report again and see if this makes more sense.

{patient_referral} is the name of the table that all information in the report is coming from. I have a patient_number, referral_number, date_of_referral, discharge_date, caseload_holder and several other fields which aren't important at this stage.

I have an existing report where the user selects a caseload_holder (integer) and both a {@startdate} and an {@enddate}. From this the report shows the caseload details for the specified holder. There are 4 main counts that are displayed, these are:

Caseload at start: This counts the patients which already have referrals that are open at the time of the {@startdate}

New Referrals: This counts the patients who have had new referrals created for them between the {@startdate} and {@enddate}

Discharges: This counts the patients who have been discharged between the {@startdate} and {@enddate}

Caseload at end: Finally there is a count of the patients that are active as of the {@enddate}

This report works fine. What i want to do is create a report that breaks down the period between {@startdate} and {@enddate} which may well span years into monthly blocks. In each of the monthly blocks i want to show the 4 counts that are outlined above.

I only want the months that are selected by the user as apposed to using the referral date, which could be months, even years before the {@startdate}

Does this make my predicament any easier to understand?

Any further assistance would be much appreciated.

 

[danjausten]

Hi,

Am I right in saying that you want to display every month between the given dates regardless of whether there is data or not?

Dan

 

[MadCatmk2]

I do indeed wish to do this.

 

[Sandman83]

Hi Gary,

It sounds like your original solution is exactly what I am looking for. I do have one problem though. When I select the date field, I only get "in ascending order", "in descending order", "in specified order", and "in original order" as options right below it. If that is not the section that needs to be changed to "for each month" then the "The section will be printed...." section does not give any options. Also the data is not stored as a date field, it is stored as a number field and am not sure if that is the issue, and if so, if there is a work around for it. I am using Ver. 8.0.1.

Thanks for any/all help.

Tim.

 

[glalsop]

Yeah, you can't group periodically like that directly on a number field, but can probably work around it by creating a formula and converting the field to a date using the cdate() function, then grouping on the formula result.

Good luck

-Gary

 

[Sandman83]

Thanks Gary, worked great!

 

[synapsevampire]

You're probably best served to create a Query in Access which converts the number to a date type, and then base the report on the query.

You can also use a SQL Expression if you're using ODBC.

Otherwise create the formula in Crystal, but that is the least efficient means.

-k