Group Calls into One-Officer and Two-or-More-Officers Categories by Ho 1

[Rooski]

I have looked extensively through the Crystal forums, but could find nothing that relates to the situation below.

I am using Crystal 10 against an Oracle database.

The report I am trying to create will look, by way of example, like this:


Hour # of # of
of One-Officer Two-Or-More-Officer
Day Calls Calls

01 115 300
02 98 240
etc.

For each officer that was sent to the call (incident), the file contains the Incident ID, Car ID, Officer ID, and the Time Of Day sent. Hence, a given incident may have one or more officers associated with it.

I can set up the grouping formulas to create the hour of day information. What I need to do (preferably in a cross-tab), and don’t know how to do, is read through all the records and categorize the incident as either a one-officer incident or a two-or-more-officers incident. Can this be done in a one-step operation in a single Crystal report?

On the surface, it appears to be a two-step operation. One report will create (and export) a file that will group all incidents as either a one-officer call or a two-officer call. Then a second report will read the file from the first report and organize the data in a crosstab. And I'm not sure if that is even possible in Crystal.

Thanks, Rooski

 

[lbass]

If you write a command, you can do it in one query. When you open a new report->your datasource->choose "Add command" and then enter a command that looks something like this:

select "table"."timeofday", "table"."carID","table"."incidentID", (select count(A."officerID") from "table" A
where A."incidentID" = "table"."incidentID") "cntofficers"
from "owner"."table" "table"

If you're not sure how to write it, take a look at database->"Show Sql Query" in a report you have already finished, and note the punctuation and syntax.

Then in the main report you can insert a crosstab that uses your clustering formula for time of day as the row, {command.cntofficers} as the column, and count of Incident ID as the summary field.

-LB

 

[Rooski]

LB,

My SQL skills are limited at best and I didn't even know the Command feature existed in Crystal. But I followed your instructions and, after some tinkering and verification testing, managed to create a working version of your suggested SQL solution. That is a very valuable piece of code and one I will catalog. I was then able to incorporate the crosstab, create a couple of additional formulas, and get the report in the desired format.

Thanks very much and I will make sure a star is attached to this thread.

Regards,

Rooski