How to group by 4 hour intervals in crosstab 1

[tweetie7]

I’m using Crystal Reports 12.1 and the database is Oracle 11g. I have been requested to create a report based on a count of incidents (for which I am going to use a Crosstab). Data looks something like this:

Incident Number Type Priority Incident Date
000161-21102010 Type 1 1 21/10/2010 7:23:17 AM
000164-21102010 Type 2 3 21/10/2010 8:43:41 AM
000165-21102010 Type 2 3 21/10/2010 10:25:55 AM
000166-21102010 Type 3 3 21/10/2010 11:45:07 AM
000167-21102010 Type 3 3 21/10/2010 12:24:15 PM
000168-21102010 Type 1 3 21/10/2010 12:51:28 PM
000169-21102010 Type 2 3 21/10/2010 13:15:31 PM
000170-21102010 Type 3 3 21/10/2010 13:31:45 PM
000174-21102010 Type 4 1 21/10/2010 14:00:00 AM
000175-21102010 Type 4 3 21/10/2010 15:25:35 PM
000176-21102010 Type 4 3 21/10/2010 16:05:48 PM
000180-21102010 Type 2 3 21/10/2010 17:32:25 PM
000181-21102010 Type 1 3 21/10/2010 18:51:39 PM
000182-21102010 Type 4 3 21/10/2010 19:02:45 PM
000188-21102010 Type 1 1 21/10/2010 21:27:49 PM
000189-21102010 Type 2 3 21/10/2010 22:16:50 PM
000190-21102010 Type 1 3 21/10/2010 23:55:04 PM

The user wants to be able to select the column value and row value for the crosstab at runtime so I have created parameters for these and the formulas, eg:

@Column_Group_Level

If {?Column Group} = 'TYPE' then {INCIDENT.INCIDENT_TYPE}
else if {?Column Group} = 'PRIORITY' then {INCIDENT.PRIORITY}
.
.
.
else if {?Column Group} = 'TIME INTERVAL' then ToText({@Time_Interval})//'TIME INTERVAL'
else if {?Column Group} = 'DAY OF WEEK' then {@Incident_day}

If the Time Interval parameter is selected then a value also has to be selected in a TIME INTERVAL PERIOD
parameter, the values to choose from are

1 HOUR
4 HOURS
8 HOURS
12 HOURS
24 HOURS

@Time_Interval_Value

if {?Time Interval Period} = '1 HOUR' then 60
else if {?Time Interval Period} = '4 HOURS' then 240
else if {?Time Interval Period} = '8 HOURS' then 480
else if {?Time Interval Period} = '12 HOURS' then 720
else if {?Time Interval Period} = '24 HOURS' then 1440

And after this I am stuck – I can’t work out what I need to do (or even if it is possible) to group the count of incidents under the different time intervals, eg If they picked a Row Group Level of INCIDENT TYPE with a Column Group Level of 4HOURS it would look something like:

12.01AM 4:01 8:01 TO 12.01 16:01 20:01 TOTALS
TO 4:00 TO 8:00 12.00PM TO 16.00 TO 20:00TO 23:59
Type 1 1 1 1 2 5
Type 2 2 1 1 1 5
Type 3 1 2 3
Type 4 2 1 1 4
TOTALS 0 1 5 5 3 3 17

I think I could work it out if it was just the one time interval period (but help with this would be gratefully received), but I can’t work out how I could do it with different time interval period in one crosstab. Is the solution to create different crosstabs for each time interval period and suppress/show them depending on the value selected in the parameter?

Thanks in advance.

 

[Madawc]

Extract the hour: something like

DatePart ("h", {your.date1})

Use this to get the interval, dividing the parameter, unless it is 24 hours which should be done by 'day'.

@HourPart / Parameter

Then turn the result into a simple number by trunaction.

Truncate({@CalculateInterval}, 0) + 1

(These commands could also be nested, but it is easier to do it step by step and display the result on a test section in your report, to be sure all is well.)

Combine @MakeInterval with the date, which you can handle as ToText({your.date}, "yyyyMMdd")

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


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

 

[lbass]

This formula seems to work:

dateadd("n",int((hour({table.datetime})*60 + minute({table.datetime}))/{?Time Interval})*{?Time Interval},datetime(date({table.datetime}),time(0,0,0)))

I set up the {?Time Interval} parameter with a number for the minutes (30, 240, 480, etc.) and used the description field for the text (every 30 minutes, etc.)--so you can have the user see the description, but have the value available for the formula.

In the crosstab, after adding this as your column field, you should go to group options->print on change of minute (regardless of interval).

-LB

 

[tweetie7]

Thanks for the responses.

I used the formula provided by lbass and it is working fine.