Hourly to Half Hourly 1

[Indybug]

Hello! I do not know anything about Crystal Reports but I am desperate for help. I just got the book "The Complete Ref Crystal Reports 2008" but there is no way I will be able to get thru it in a week and be able to fix this report for my boss. This report came from a reporting application that came with our dailer software. The software company is no help what so ever.
We have a report that totals all the calls that come into our call center on an hourly basis. The boss needs the report based on the half hour. Here is the current formula I'm working with:

Numbervar ThisTime;
if ({@NumOffered}) = 0 then
ThisTime := 0
else
ThisTime := Sum({History.QueueTime}, {@DateHour})/{@NumOffered};

// minutes a nd second lang daw sa ni boss
if ({?TimeFormat}) = 0 then
(
// NumberVar Hours := (ThisTime \ 3600);
// NumberVar Minutes := (ThisTime - (Hours * 3600)) \ 60 ;
// NumberVar Seconds := ThisTime - (Hours * 3600) - (Minutes * 60);
//
// ToText(Hours, "0") + ":" + ToText(Minutes,"00") + ":" + ToText(Seconds,"00")
//


NumberVar Minutes := ThisTime \ 60 ;
NumberVar Seconds := ThisTime - (Minutes * 60);

ToText(Minutes,"00") + ":" + ToText(Seconds,"00")
)
else
(
ToText(ThisTime / 3600, 2)
)

Is this something one of you experts can help me with? I sure hope so, because this is all Greek to me. Thank you!

 

[Madawc]

Check the grouping function. Depending on your version (always worth mentioning) you may find half-hour intervals there as an option.

Otherwise use formulas, Crystal commands rather than variables. Something like
[Code @HalfHourGroup]If DatePart("n", {your.time}) < 30
then ToText(DatePart("h", {your.time}) & ":00"
else ToText(DatePart("h", {your.time}) & ":30"
[/code]
The command uses "n" for minutes and "m" for months.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Indybug]

Thanks Madawc. It is CR 2008. I am wondering if I would best to try and make a whole new report instead of changing the existing one using the formula you provided.
The book I mentioned is 2 inches thick and the boss needs this by next week. I was hoping to change all the formulas like this one from hourly to half hourly intervals and leave everything else the same. (This is just one of several formulas in the report.)

 

[lbass]

The formula you are showing is converting a numeric summary to a string, which doesn't really have anything to do with how you group. We need to know the content of your {@DateHour} and {@NumOffered} formulas.

-LB

 

[Indybug]

LB - The {@DateHour} is:

DatePart("n",(History.CalldateTime))

and
The {@NumOffered} is:

Sum({@ManHour}, {@DateHour))

 

[Madawc]

Crystal will give you totals or counts for a group, without you needing to code them. This is outlined at FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Indybug]

LB - I think you have me on the right track - Thank You! I had been trying to figure out how the totals were calculated without a formula.
I think my problem now is the fact that I can not use DatePart for half hour increments. I can pull the correct Data now, but the Group Header prints hourly (5:00pm-6:00pm). Plus, I have several totals where the Header is blank.

 

[Indybug]

I have this SQL query to pull the data in half hourly increments, could this be something I could use in Crystal Reports?

select dateadd(minute, -1 * datediff(minute, 0, calldatetime) % 30, dateadd(minute, datediff(minute, 0, calldatetime), 0)) as time, count(*), projectid

from history

group by projectid, dateadd(minute, -1 * datediff(minute, 0, calldatetime) % 30, dateadd(minute, datediff(minute, 0, calldatetime), 0))

order by time, projectid

 

[lbass]

I can't really follow this. I would just group on a formula like this:

if minute(History.CalldateTime) <= 30 then
datetime(date(History.CalldateTime),time(hour(History.CalldateTime),0,0)) else
datetime(date(History.CalldateTime),time(hour(History.CalldateTime),30,0))

This would cluster records with minutes from 0 - 29 under one group and 30 - 59 under another.

-LB

 

[Indybug]

LB - thank you very much for all your help. I have learned so much in the past few days.
Because I am such a newbie I failed to provide the right information to solve this problem. I believe this is the problem:

@DateHour = DatePart("h",(History.CalldateTime))

and the Group By sorts by

@DateHour with the option, "Use A Formula As Group Name" formula:

switch ({@DateHour} = 0, "12:00 AM = 1:00 AM",
({@DateHour} = 1, "1:00 AM - 2:00 AM,
and on for all 24 hours

The DatePart "h" is what I need to change right?

 

[lbass]

You should replace your formula {@DateHour} with the formula I suggested. Then if you want to show the time range instead of the half-hour, you could use the following in your customize group name area:

totext(time({@mygroup}),"hh:mm tt") + " - "
totext(time(dateadd("n",29,{@mygroup})),"hh:mm tt")

-LB

 

[Indybug]

Thanks!

@DateHour and Group By Formula are changed, but I received an error on @mygroup, so I changed it to @DateHour.

Now I have an error that this part of the statement:

"totext(time(dateadd("n",29,{@datehour})),"hh:mm tt")"

does not appear to be part of the formula. I tired a coma, "and", "else" and a few others in between the two phrases with no luck.

Just when I think I am going to get this figured out, I am hit with another wall. How can something so simple be such challenge?!

 

[lbass]

Sorry, typo.

totext(time({@mygroup}),"hh:mm tt") + " - " [red]+[/red]
totext(time(dateadd("n",29,{@mygroup})),"hh:mm tt")


The following is what I meant by {@mygroup}:

if minute(History.CalldateTime) <= 30 then
datetime(date(History.CalldateTime),time(hour(History.CalldateTime),0,0)) else
datetime(date(History.CalldateTime),time(hour(History.CalldateTime),30,0))

Doesn't matter what it is named though, as long as you are using this.

-LB