Multivalue Time Parameters

[terrym777]

I am using Crystal XI and I need to sort my report by user selected time parameters. I know that you cannot group by parameter selects so I am trying to create a formula to group or sort by. I am having trouble trying to extract these into a display field which might subsequently help me to sort by it.
I have played around with the first line trying to convert ?Time or by changing stringvar to timevar but I can't seem to get it to work. The formula below produces the following error:
A string is required here. If I change it to Cstr(?Time) then I get "A number, currency amount, boolean, date, time, date-time or string is required here". I have used this formula in the past for String parameters but I can't seem to get it to work for Time parameters. Any help would be appreciated. Thanks.

stringvar array params := {?Time};
numbervar i;
numbervar j := ubound(params);
stringvar display;

For i := 1 to j do
(
display := (display + params) & ","
);
Left(display,len(display)-1);

 

[lbass]

If you need to sort by the selected times, then you should be sorting by the field, not the parameter. You would limit the field either in the selection formula to those times:

{table.time} = {?Time}

...or in a conditional formula like this:

if {table.time} = {?Time} then
{table.time}

...where records that don't fall into the parameter array would be sorted or grouped together.

The formula you are showing would be used for displaying the parameter selection only:

numbervar i;
numbervar j := ubound({?Time});
stringvar display;

For i := 1 to j do
(
display := display + totext({?Time},"hh:mm:ss") & ","
);
Left(display,len(display)-1);

-LB

 

[terrym777]

The problem is that the time field will never equal the parameter. Let me explain further. This is a teachers schedule where one day they may work from 9am to 1pm and another day from Noon to 5pm. The multiple value parameter lists every hour on the half hour from 6am to 6pm. The user can select multiple times for the report. For example, they want to see all teachers who are working at 10am and who are working at 3pm for any given day during a week. My selection process is working fine where I have to determine if the time(s) selected are in the range for a teachers daily schedule.

The problem is that they want to see it grouped by the parameter times. For example, 10am and 3pm, using my above example. The only way I can see this working is to create a group formula for each half hour time frame. Another question I have is if a teacher's schedule is 9-6, and the paremeter selects are 10am and 3pm, can the record show up in both groups? FYI, each detail line shows the entire weeks schedule for the teacher. Only one of the days scheduled time needs to fit the criteria for the parameter group.

As for the formula, I know it is for display only. I might want to display it in the report header. I also thought that if I could extract each parameter, I might be able to create a formula that I could use to group by. But what I was trying to say is that I can't get the formula to work with it being a Time type parameter and I need it to be a Time type for my record select to work.

As usual, thanks for your help lbass. I used to be a basic programmer for 20 years and finding this switch to Crystal a bit challenging.

 

[lbass]

Please explain the purpose of grouping--if it is only in order to summarize the number of teachers per point in time, you could be doing this using running totals in the report footer. One record can appear only in one group.

Please also show samples of the actual data you are working with, and a sample of the results you want to see.

-LB

 

[terrym777]

This is what they would like the report to look like:

Weekly Staff Schedule Report
6/3/2012 - 6/7/2012
Parameter Times selected - For example, 6:00AM, 11:00AM

Group by School Site
Group by selected Parameter Times (picked from a list of times)
(Was trying to create a formula to group by)

School 1
6:00AM
Mon Tues Wed Thurs Fri
Teacher #1 9-1 9-1 6-6 9-1
Teacher #2 12-3 6-3 9-6 11:30-4
Teacher #3 6-10 6-10 6-10

11:00AM
Teacher #1 9-1 9-1 6-6 9-1
Teacher #2 12-3 6-3 9-6 11:30-4

School 2
6:00AM
Teacher #4 6-3 9:30-1 8-4
Teacher #5 6-9 6-9
11:00AM
Teacher #4 6-3 9:30-1 8-4
Teacher #6 9-3 9-3 9-3 9-3 9-3


Here is a partial portion of my record select:
{STAFF_SCHEDULE.SITE_ID} = ToNumber({?Site})) and
({@MonTime} or {@TueTime} or {@WedTime} or {@ThuTime} or {@FriTime})

Here is what the formula for @MonTime looks like, the others being similar for each day of the week:
Numbervar x;
Booleanvar z := false;
For x := 1 to Ubound({?Time})
Do
(If {STAFF_SCHEDULE.MON_START} <> '' Then
If {?Time}[x] >= Ctime({STAFF_SCHEDULE.MON_START}) and
{?Time}[x] <= Ctime({STAFF_SCHEDULE.MON_END})
Then z := true;
);
z

The report selects the appropriate records for multiple time parameters selected, I just can't seem to figure out how to group them by the times selected.

Hope this clarifies the problem better. Thanks.

 

[terrym777]

We are thinking of having, for example, 5 separate time prompts and having subreports for each time that they select. Does this sound doable?

 

[lbass]

Try this:

For the container report, insert the table that includes all schools.

Also, create a date and times table in Excel. Add a times column F1, and a dates column F2. Copy the dates so that the dates repeat for each complete set of hours. Save the report as AllDateTimes. Then go the database expert->create new connection->Access/Excel->select Excel->browse to find the new table, and add this as your second table in the container report. Do not link the two tables (disregard the warning). This will cause all dates and times to repeat for each school. Add a record selection formula like this:

{Sheet1_F2} in {?StartDate} to {?EndDate} and
{Sheet1_F1} = {?Time}

...where {?Time} is a multivalue time parameter.

Insert groups on school and on {Sheet1_F1}.

Then add the sub in the time group footer, and link it on school, date, and also link {?Time} to the same parameter (which you would create within the sub) in the sub, using the dropdown in the lower left to select the actual parameter, not the default {?pm-?Time}. Then go into the sub record selection formula and add your current selection formula.

You would also need to limit the dates in the sub--you could create the same parameters in the sub as in the main and link as you did for times.

-LB

 

[terrym777]

Thanks for your response! I will look into this approach.