The Bureau of Justice Statistics (BJS) is responsible for an ever-growing volume of crime data, which must be made accessible to the public. To provide quick, access to all these data, we developed a "drill-down" style web application using ColdFusion that generates data tables from an oracle database as they are requested. This web application is called "data online" and allows web users to construct and download tables of crime and justice statistics for the U.S., States, and local agencies.

• Prior to this project, much of the data was trapped in paper documents, pdf files and other formats. This severely limited access.
  
  
• Due to their static format, the data were restricted in how they could be viewed. For these data to be as useful as possible, however, they needed to be viewable in a variety of different ways. This is particularly important in crime trend analysis.
  
  
• Data online's data warehouse currently contains about 200 megabytes of data and expands with each new year of crime, which means we are guaranteed to stay busy for a while to come. However, with such a large scale project, scalability and ease of maintenance are crucial.

How many data tables are we talking about? Well, data online contains three data categories (Crime, Homicide, and Law Enforcement Management and Administrative Statistics[LEMAS]) covering over 6,000 local agencies and 50 states. Within these categories you can choose from a variety of table types (for ex: by a single state or single year). You might then be prompted to define the data in your table by choosing from over 6,000 different agencies, 20 crime variables, and a 15 year date range. To make a long story short, it's possible to generate more than a million different data tables. So, we needed to provide users efficient access to all this data, and make it easier to maintain and update. In addition, all these data tables would need to conform fully with Section 508 accessibility standards.

The Answer

To solve all these problems, we chose ColdFusion with an Oracle database backend as our solution. For those unfamiliar, ColdFusion is a tag-based programming language used to generate html dynamically based on conditional logic and database queries.

• By combining the data from various sources and putting it into an Oracle database, it can be easily linked with other data and exported in multiple formats.
  
  
• With ColdFusion we can create customized templates that automatically display our data in a variety of user-specified formats.
  
  
• Since the layout is separated from the data, we save time and effort on maintenance and scalability of the application.
  
  
• By offering .CSV (comma separated values) files, users can download a copy of the data tables they generate.
  
  
• By creating a core set of templates that can generate all these millions of possible tables, we do our coding and 508 tagging work once and we're done saving tremendous amounts of time.
  

II. Coding the tables to conform with Section 508 guidelines.

Tagging our data tables for 508 conformance involved consideration of many different table formats. Some of them were simple and straightforward, while some of them (see Figure 3 below) have complex column headings 3 layers deep. In addition, the tables in data online are populated from the database in one of two ways: 1) tables whose rows and columns are defined by user selections and 2) pre-set tables that are filled in with data. First we'll take a look at the user-defined tables.

User-Defined Tables

We'll start by using data online to generate a table and then look at how it was formed by the application. For example, let's say we want crime rates for the state of Virginia and the entire nation between 1980 and 1995.

In the example above, the variable groups we've selected will define the columns in our tables, while the starting and ending years will define the rows. Each one of the variable groups above relates to a specific template. The code that creates the table from the database uses conditional statements to determine which of these templates to include in the table based on the user's selection. Since we put all of our accessibility code within these templates, any data table generated by the user will be tagged to conform with section 508 automatically.

For this example, we can have up to 3 different column headings associated with each data cell (Figure 3). This precludes the use of the simple SCOPE="row"/SCOPE="col" method. To tag such a complex data table, we need to use the ID/HEADERS method, which allows you to specify all the columns that describe each data cell. Each pertinent table header cell will be tagged with a unique "ID" attribute. In Figure 4, we see two variable groups which will be included in the table based on conditional logic (highlighted in blue) with ID attributes included (highlighted in dark red).

The data cells of each table row will receive a "HEADERS" attribute. The HEADERS attribute will contain the unique IDs for all the header cells that apply. In addition, the first data cell of each row will contain a SCOPE="ROW" tag to define the rows of the table explicitly. In Figure 5, we see the data row cells that correspond to the header IDs in Figure 3. The conditional logic is highlighted in blue, while the HEADERS attributes are highlighted in dark red.

The result is a complex, customized data table automatically tagged to conform with Section 508 guidelines. For this particular page, we coded 25 cells once, but got a table of hundreds of automatically tagged cells. By having the output of our Section 508 code delivered dynamically based on calls to the database, any necessary edits can be made to a few lines of code. Compare this to going in and re-tagging hundreds of static data tables by hand. In testing, the tagged tables of data online were read successfully by a blind user (Mr. Pat Sheehan) of the JAWS screen reader.

Pre-Defined Tables

The other method of data table generation involves specifying a pre-set table and populating it with data based on the database query defined by the user. In the local agency profile section of LEMAS, for example, a user is prompted to select a local agency. The specified data is then called from the database and inserted into a summary profile that contains tables that have been hard-coded ahead of time. For example, let's say we want to know the education requirements for employment in the San Diego, CA police department.

Like the "Employment" table shown in Figure 6 above, most of the tables shown in the LEMAS local profile are very simple in structure. In this case, the SCOPE="row"/ SCOPE="col" method is the best option. Using this method, a SCOPE="col" attribute is placed within each table header cell that contains descriptive information, and a SCOPE="row" attribute is placed in each <TD> cell of the table data rows. Figure 7 shows a table from the LEMAS local profile template with the section 508 tags circled in red.

The LEMAS local profile also contains pre-set tables with data rows that are determined by the database query. For example, the crime table in local profile has a pre-determined single-layer header row, but the data rows are defined by a call to the database to retrieve the most recent five years of data. In this case, a SCOPE="row" attribute is put in a ColdFusion conditional statement and will print out for each data row returned. A code sample below shows the crime table from the LEMAS local profile template with the section 508 tags circled in red (Figure 8).

By putting our accessibility tags within a database populated template, the LEMAS local profile template can now generate 700 different data tables and will be updated automatically as the database is updated.


III. Conclusion

In this last example, we see the two elements at the heart of data online's efficiency, scalability and accessibility:

1) Whenever applicable, templates, conditional logic and user-defined database queries determine the structure and content of the data tables. This maximizes access to the data and eases the process of data updates and maintenance of the application.

2) The 508 tags relative to all possible table scenarios are put within our conditional statements to be printed out as the tables are generated, not as an afterthought, to guarantee full accessibility to the data. This also provides us with the flexibility to quickly update all the accessibility attributes in our tables when new standards arrive, and as everyone involved is well aware, the accessibility guidelines are far from written in stone at this point in time.

The flexibility allowed by the practices above mean that when the latest year's worth of crime data arrives, we simply update our database and we're done. Our millions of data tables, created from only 36 templates, are going to be tagged and ready to go. Quite a few days of toil were invested in getting these templates perfected, but we don't care about that anymore, because we're done, and accessible.