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:
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
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.
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:
Code:
@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}
parameter, the values to choose from are
1 HOUR
4 HOURS
8 HOURS
12 HOURS
24 HOURS
Code:
@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
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.