Chapter 7. Writing Your Own Reports

Many study reports are re-executed at regular intervals throughout the trial. Such routine reports can be created by putting all of the computational steps (data retrieval, analysis and report creation) into a UNIX shell script. This script can then be documented and installed in the study reports directory. Once this is done, and tested, it can then be executed by anyone with access to the reports tool, the study, and privileges for the particular report. Of course some analyses must be performed by a skilled statistician each time they are done, but most of the summary reports created for trial and database management purposes are routine tables and graphs. In such cases setting up a shell script, which includes all of the steps needed to create the report, can relieve programmers and data analysts from those tasks which are simple, repetitive and time consuming and allow them to concentrate on those analyses which require more attention and skill.

DFdiscover Study Files describes the location and format of all study files including: the data files created from the study CRFs, the quality control data base, the journal files used to record every transaction written to the study database, and all study configuration files. Any of these files might be needed as input to a study report program.

DFdiscover includes several shell level programs which are described in Shell Level Programs. These programs can be used to export and reformat data, and read study configuration parameters. Any number of other programming tools might also be incorporated into shell scripts to create study specific report programs. These might include other DFdiscover programs (e.g. DFsas), UNIX commands (e.g. awk, grep, sed, sort), third party packages (e.g. SAS), report generators (e.g. perl) or even low-level programming languages.

With these tools, programmers can extract the data they need from the study database, and then analyze and summarize it using whatever programming tools they are familiar with or think most suitable to the task at hand.

The following shows a simple example of a UNIX shell script that reports subject entry by site for the past 9 months, as well as the total to date, for an example study.

The basic outline of this program is documented with comments (which begin with the # symbol). This hopefully is enough to give you an idea of the level of effort required to produce simple study specific reports. For those unfamiliar with UNIX shell programming, this may whet your appetite for an exploration of the many useful tools built into the UNIX operating system. You can accomplish a great deal without invoking big systems like SAS, and it's really not that hard, honest!

#!/bin/sh
# RANDOMIZATIONS BY SITE FOR PAST 9 MONTHS AND TOTAL TO DATE
# $1 is study number.
# yy = current year
# mm = current month
# hx = number of months to be printed
yy=`date +"%y"`
mm=`date +"%m"`
hx=9
# Export all primary randomization records (plate 1) received to date
# and use field 13 as the visit date. Site number is assumed to be pid/1000
/opt/dfdiscover/bin/DFexport.rpc -s primary $1 1 - | nawk -F\| '
BEGIN { YY="'$yy'"+0; m=MM="'$mm'"+0; HX="'$hx'"+0
    # determine which months are to be printed
    if(m >= HX) {
       for(i=HX;i>=1;i--) {k[i]=YY*100+m;--m}
    } else {
       jn = HX - MM
       jm = 13 - jn
       jy = YY - 1
       for(i=1 ;i<=jn;i++) {k[i]=jy*100+jm; ++jm}
       for(jm=1;i<=HX;i++) {k[i]=YY*100+jm; ++jm}
    }
}
{ # Read each exported record and count cases by month and center
    mon=substr($13,4,2); yr=substr($13,7,2)
    center=int($7/1000); yymm= yr*100 + mon
    n[yymm,center] +=1
    mtot[yymm] +=1
    ctot[center] +=1
}
END{ # Print Report
    printf"RECENT AND TOTAL RANDOMIZATIONS BY SITE\n"
    printf"SITE "
    for(i=1;i<=HX;i++) printf"%6d",k[i]
    printf" TOTAL\n\n"
    for(i=1;i<=200;i++) { # center number range is 1~200
        if( ctot[i]>0 ) {
           printf"%5d: ",i
           for(j=1;j<=HX;j++)
               if (n[k[j],i] > 0) printf"%6d",n[k[j],i];
               else printf "      ";
           printf" :%6d\n", ctot[i]
           SUM += ctot[i]
       }
    }
    printf"\nTOTAL: "
    for(j=1;j<=HX;j++)
        if (mtot[k[j]] > 0) printf "%6d",mtot[k[j]];
        else printf "      ";
    printf" : %5d\n", SUM
}'

When this UNIX shell script is installed in the study reports directory, and given execute permissions for the permitted members of the study group, it can be executed.

The documentation for study specific reports must be kept in a separate file from the documentation for all standard DFdiscover reports. Specifically, it must be kept in the .info file in the study reports directory.

Report titles appear in the scrolling list of reports in the order in which they are defined in the file. Any reports not defined in this file will appear in alphabetical order at the end of the list of reports that have been defined.

The general layout of the file is repeating blocks, of the following structure, where each block contains the documentation for one report:

.BEGIN report_name       (1)
.TITLE report_title      (2)
.OPTION first_option     (3)
.OPTION next_option      (4)
documentation text       (5)
.END                     (6)