Accurate Reporting for Crime Analysis: Five Key Considerations

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.   

Combining Mobile Tactics to Simplify Mobile Communications in Emergencies

Reprinted from Resdida.com

In the case of disaster, like the tornado damage in Harrisburg, IL, information is priceless and communication ranges from difficult to impossible. With landline wires down and cell reception poor, a mobile may not be able to send or receive a call, but can often send and receive a low-bandwidth text message. It can be a great help to have a mobile communications tool that you can setup quickly for sending and receiving key information from and to emergency services in the field and the community at large.

I grew up in tornado country and still have family there, so this particular use is close to my heart. SMS can be used to warn people prior to destruction and can help them find resources during the aftermath. I want to take a moment to walk through the aftermath with you and discuss how QR Codes can be integrated with SMS text messaging to enhance communications.

One of the largest struggles in SMS text messaging programs is with communicating the instructions that will help people subscribe and participate. It typically goes something like this: “Please text hburg to 80474 to subscribe.” What if you could replace these words with a simple picture? You can use an image, like a QR code, that tells the phone exactly what to do so that the user can get started faster. An example is here on the right.

There are several mobile apps across all of the various platforms that read these QR codes. The user simply opens the app, points the camera at the code and the app tells the phone to complete the requested task. If you have a QR Code reader application for your smart phone, scan the above image now. It will prepare the SMS message for you. You will then be able to follow the steps on the screen to send it and start the process.

When using my smart phone QR Reader on the above image, I am immediately brought to the screen below on the left. To execute the SMS text message, I click the create/edit button on the bottom right of the screen (looks like a box with a pencil).

When I do that, I am brought to my smart phone SMS text messaging application where the message has been prepared for me (see the above image on the right). Then, I simply press Send.

So the process is nearly automatic for the user, making everything much simpler. This simplicity is priceless during a crisis. The QR Code for subscribing can be printed in local papers, presented on posters in the community and available in shelters.

If you’ve done this yourself while reading, you will notice that you still need to opt-in to receive the texts by returning “Y”. Once subscribed, officials can keep you updated on any concerns or calls to action for the area.

The welcome message also asks you for more information about your status which will be used to target messages to specific groups when necessary. For instance if you indicate you are living in the shelter the types of information you will need may be different from folks living in their dwelling. Those whose dwellings were not damaged will need different information from those whose homes were hit by the storm directly.

Codes like these can also help people get directions to key locations, like staging areas, clinics, etc. Let’s say that you have a staging area for emergency care, food rations, etc and you would like to make sure that people can find you quickly and easily. You can use a SPARQ code to map your location on the user’s smart phone so that they can map their way to find you. 

You can try this now with your QR reader on your smart phone. It will open your mapping application and mark the location for you. Then, you can use your mapping application to get directions to the site.

During an emergency response, especially in disasters where fear, isolation, a lack of sanitation, poor nutrition and injuries are wide-spread and dangerous, communication can be a lifesaver. Collecting and sharing information can mean the difference between life and death and mobiles can provide great reach and make a large impact.

There are so many ways to make use of QR Codes to simplify and streamline your mobile program and it seems that new possibilities are constantly emerging. Resdida works to stay on top of all of these emerging technologies so that we can translate them into results for our clients. Working together, a strong mobile presence is easily within reach.