There is no way any vendor can predict every request for information that might come across every crime analyst’s desk. So many times the information we need is not available in standard reports in CAD or RMS. When this happens, we can use the tools we have available on hand to create our own reports.
To do this, we need to have access to CAD and RMS databases and some knowledge of how that data is put together. Below are five key considerations for creating accurate reports.
1. Choosing the Right Data Source
You have a general idea what your report should look like, maybe even a picture in your head. Now you must figure out where you will find your information. Where does the information you seek exist? Is it in your CAD system? Do you need a report based on RMS information? Is it in a Microsoft Access database? Identifying your data source is the first step toward having the information you need.
For instance, I may know that my report needs to list every burglary that happened this quarter. It needs to show: report number, report date, penal code classification, reporting district, location, and property taken.
Now that I have identified what I need exactly, I need to know where I can get this information. My Information Systems or IT department has set up an Access database for me that stores RMS information. It is updated every morning at 5:00am. This will be my data source.
Crystal Reports is able to connect to databases (ODBC and SQL), Queries and Dictionaries. Oftentimes, Microsoft Access and Excel will also connect. If your Information Systems, Information Technology or Technology Section will not let you connect directly to your CAD or RMS, they may be willing to set up a data dump on another server or create a Dictionary for you. Your IS/IT/TS should be willing to create an option that will work for you.
2. Choosing the Right Reporting Tool
I have already mentioned Crystal Reports, which is a very versatile choice for most reports. Some police departments, dispatch centers or regional agencies may use Humingbird or Cognos. Usually, the type of tool is dependent on what licenses the organization has at hand and what can be easily installed in time.
It is often possible to bypass dedicated reporting tools for more commonly installed Microsft Access or Excel. Choice of tool is very important when thinking about the audience and how you will distribute your report. For example, there is widespread use and basic knowledge of Excel, whereas people in general are less comfortable with Access. If you need to create are report that is interactive for the user, Excel is typically a good choice. Crystal Reports can be exported in a variety of formats, including PDF and XLS, but it can also be viewed in a free Crystal Reports Viewer that let’s the user drill down and interact with the report in the Crystal Reports format.
3. Understanding the Table Relationships
Even if you have a connection to your CAD or RMS system, how can you tell where to find your data? You must find a way to understand how the tables are set up. Your IS/IT/TS department should be able to provide you with descriptive information about where to find the data you need.
You must learn how the tables are related to one another. For instance, each case may have multiple subjects. There would be a case table listing the cases and a subject table listing subjects with a case number identifier. The tables would be related by case number so that you would be allowed to view the subjects associated with each case.
In my case, I know that the report number, report date, penal code, reporting district and location are in the Main table, but the property taken is in the Property table. The Main table and the Property table are linked together in a one-to-many relationship by their common Report Number field. This means that each single report may have many items listed in the Property table.
When you bring the tables into Crystal Reports or Microsoft Access, you will need to create a Join for them based on report number. There are several Join Types to choose from: Equal or Inner Join, Left Outer Join, Right Outer Join and Full Outer Join. You can read about Join Types in this article here.
4. Choosing the Right Summaries
Sometimes your report is a simple list report showing all events, and other times you need to include totals. When you total or summarize your data, you can run the risk of getting unexpected results that could unintentionally mislead you.
Count is important if you want to know the number of events, but count can mean multiple things depending on the linking you have in your report and what field you are counting. For example, if you choose a field that always has a value, like incident number or report number, you will count 1 per each record, but if you choose a field where missing values are possible for some records, you may miscount inadvertently. Also, whenever you have a link in place, especially with a one-to-many relationship, if you use Count of incident number, you will get a full count of records across both tables and not a count of incidents. If you want a count of incidents or reports, it’s best to switch to a Distinct Count in Crystal Reports.
Count and distinct count are managed differently in the reporting tools to it is important to test thoroughly to understand what you are counting in the tool you are using and to make sure that the results make sense to you.
If you take my Crystal Reports for Law Enforcement class, you will walk through many examples of how these types differ from each other and change depending on the groups you have created in your report and where you physically place the summary.
5. Checking the Results
Examining the report carefully when you finish is critical to the accuracy. The records you see and the summaries displayed should make sense to you based on your knowledge of both the data and the way the department operates. If anything looks “off” in any way, it only takes a few minutes to check some records in RMS or CAD to be sure what you are seeing is worth believing.
Sending out a report that hasn’t been checked is dangerous because people will use these numbers to make operational decisions and plan budgets for the future. So getting the wrong information from reports could seriously hurt the organization. Also, sending out reports without checking accuracy can damage the reputation of the Crime Analysis Unit. To succeed, you need Administration, Investigations and Patrol to trust in you and the information you provide.
A small amount of time is all that it takes to ensure that reports are accurate and ready for action.
