? about graph of start / stop time of event 1

[bzss7x]

First of all, here's a data sample:

EventID StartTime StopTime
1 7:00 AM 5:00 PM
2 6:00 AM 4:00 PM
3 9:00 AM 6:00 PM
4 7:30 AM 2:00 PM
5 8:00 AM 3:30 PM
...

OK, What I need is a graph in the report footer that has Time for an X axis, and a count for a Y axis. The graph should summarize all events into one data set.

Example: Using the data above, 6:00 AM would have one occurence, while 9:00 AM would have 5 occurences.

I thought of creating a shared variable for every 15 minute interval and trying to tally each occurence with formulas, then graph the sums. Does this make sense? Anyone have a better idea?

 

[IdoMillet]

Create a table "TIME_DIMENSION" that contains one record for each time period.

Join to the EVENT table using the condition of:

TIME_DIMENSION.Time >= Event.StartTime AND TIME_DIMENSION.TIME <= Event.EndTime

Now create your chart and use the TIME_DIMENSION.Time as the "On Change of" field.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bzss7x]

As the beer guys say - Brilliant!

That is really thinking outside of the box. The only issue that I had was doing greater than and less than joins between the same two tables. Crystal only wants one type of join there. I just put in one join and it seems to be giving me good results. I'm in the process of verifying the results now.

Thanks again for the good advice.

 

[IdoMillet]

Why not use 2 joins? I don't see how you can do this with one join (there are 2 different columns involved).

If the DBMS or connection method don't allow non-equal joins, you could leave this a a free join (no join at all) and place the condition in the record selection formula (it would be slower, but it would work).

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bzss7x]

The join problem came when one join is greater than or equal to (gte), then the other join becomes gte. So when I joined Event.StartTime to TimeDimension.Time as a gte join, then the join from Event.StopTime to TimeDimension.Time becomes a gte join. What I ended up doing was putting two instances of the TimeDimension table in the report creating two separate joins. It works fine.

I really appreciate the help - your idea got me over the top. Thanks again.

 

[IdoMillet]

The approach you took (with 2 instances of the TimeDimension) would produce wrong results unless you also join those two instances on Time Period.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com