When performing a large analysis or continuous monitoring project, management may not have the time to go through all of the various reports generated by the data analyst.  Often times, management only wants a high level picture and will leave it up to others to parse through the details.

 

Often the data analyst will summarize the various reports, but using ACL, one can generate a report for management.  By using this summary report, one can create an ongoing monitoring process wherein management can trace trends.  In other words, suppose audit has made several proposals to management concerning it's payment processing system.  The proposals are intended to reduce the number of duplicate payments.  Using continuous monitoring, management has been able to observe a steady decrease in the number of potential duplicate payments.  However, when they run the report this week, the number of errors jumps back to the levels that management hasn't seen in four months.  By creating a simple report, management can quickly see the trend and note the aberation that occured during the latest batch.

 

The basic report is fairly simple to generate:

 

 

The script is fairly simple to utilize because it is just a few lines.

 

The first line is to open the table.  Generally, when I write scripts I will use variables or standardized outputs for file names.  Thus, when I saved the results of a project, the results might be saved as a file, in this case the filename is preserved in the variable v_table. 

Second, I count the number of lines in the table.

 

Third, I export the results to the "Monitoring Payment Analysis" file.  I do this with several key fields:

 

SUB("Report_%v_table%_%v_date1%",1,40) as "Report" --- This record captures the report name using pre-defined variables.  Again, the v_table that identifies the table that was loaded and the variable v_date1 which is my standard variable for today's date in ASCII form.  Notice how I use the SUBSTRING command to ensure that the report name is never more or less than 40 characters, this will ensure that the record is appended properly.

 

SUB("This Report identifies the number of duplicate payments",1,75) as "Report Desc" ---This record captures the report description.  Again, I use the SUBSTRING command to ensure that the report description is the same 75 characters regardless of what report is being saved.

 

STRING(count1,12) as "Count" --- This record captures the number of records in the report.  If the code was written to identify potential problems, then the number of records should be the value management wishes to see.  Sometimes additional filters will need to be applied to get to the value management wishes to see.

 

%v_record% is a variable that contains the values for "Initials Run_date Run_time" --- These three valuess are standards within many scripts.  They preserve the identy of who ran the script, the date the script was run, and the time it was run. 

 

IF RECNO()=1 --- Notice how we are not actually exporting any of the actual fields in the file that was opened, we are exporting variables which are being saved "as" a fieldname.  By using the IF RECNO() parameter, we ensure that only one record is generated per report.

 

TO "Monitoring payment Analysis" APPEND --- Finally, we are exporting it to a standardized file with the APPEND command to add the record at the bottom of the currently existing one.

 

In the most basic permutation of this script, I would include the lines whereever I export/extract a final result.  In advanced scripts, however, I will have a separate file, that is only executed after I am comfortable with the results, that will open every report and create the summary.