Virginia ACL Users Group Training Advanced Scripting and

Virginia ACL Users Group Training: Advanced Scripting and Dialog Boxes Melissa J. Russell, Manager Deloitte & Touche LLP December 1, 2009

Agenda/Objectives • Advanced Scripting Features – Variables • Assignment • System -Generated • Substitution • Dialog Boxes – – Variable Assignment Construction Objects Project Item List & Parameters • Practical Use – Lessons Learned Summary , Q & A 2 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Advanced Scripting Features Variables • Variables are temporary values stored in memory for the duration of an ACL session. Variables are either user-defined or system-generated. • Values of variables are retained from table to table, record to record, and command to command, but can be modified from within a script. • Example of syntax within a script (ASSIGN keyword is optional): ASSIGN Amount = 5400. 00 ASSIGN Acct_No = “ 1000 -05” • (numeric variable) (character variable) Variables can be used in many ways within ACL: • as system variables automatically generated by commands • in calculations, filters, commands and computed fields • as “containers” for the names of ACL objects, such as tables • 3 Variables can also be assigned by a user through the use of dialog boxes. Copyright © 2009 Deloitte Development LLC. All rights reserved.

Advanced Scripting Features Variables – System-Generated System-generated variables are automatically created by ACL whenever certain commands are executed, either manually or from within a script. These variables have special reserved names not available for userdefined variables, but can be used within a script. For example, OPEN TABLE 1 COUNT EXTRACT RECORD TO TABLE 2 IF COUNT 1 > 100 Some useful system-generated variables: Reserved Name Description WRITE 1 # records written Extract COUNT 1 # records counted Count, Statistics TOTAL 1 total of numeric field Total, Statistics MIN 1, MAX 1 4 Generated by minimum, maximum value of field Statistics Copyright © 2009 Deloitte Development LLC. All rights reserved.

Advanced Scripting Features Variable Substitution • Any variable, whether created manually or by an ACL command, can be “substituted” within a script; at run-time, the value of the variable is substituted for the name of the variable. • Variable substitution is invoked by enclosing the variable name in percent signs (“%”). Everything between the beginning and ending % signs is replaced by the value of the variable, including the % signs themselves: ASSIGN input_table = “Dallas_Sales” (value of variable is a table name) OPEN %input_table% (opens table Dallas_Sales. FIL) • Variables to be substituted can be concatenated: ASSIGN city = “Dallas” ASSIGN acct = “Sales” input_table = city + “_” + acct OPEN %input_table% 5 (opens table Dallas_Sales. FIL) Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes Using Dialog Boxes to Assign Variables • Through the use of the DIALOG command, then end-user has the ability to select variable values. • The variable is created when the user selects an option prompted by the dialog box and finalizes choice by using the OK/Cancel Buttons. The “OK” and “Cancel” buttons are integral to the dialog function. If “Cancel” is chosen, the script will terminate. • Variables generated by the dialog boxes can quickly define a wide range of parameters set by the end user. 6 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes Dialog Command Use to create custom dialog boxes that allow users to enter a wide range of information to be used in scripts. Types of controls available in a dialog box • • • Text boxes Edit boxes Check boxes Radio buttons Drop-down lists Project item lists 7 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes How to create a dialog box Select Build New Dialog icon 8 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes ACL Script generated by dialogue box builder DIALOG (DIALOG TITLE "User Dialog" WIDTH 423 HEIGHT 180 ) (BUTTONSET TITLE "&OK; &Cancel" AT 312 84 DEFAULT 1 ) (TEXT TITLE "JOURNAL ENTRY TESTING" AT 24 16 ) (TEXT TITLE "Please select testing procedure to be run: " AT 24 40 ) (RADIOBUTTON TITLE "Posting User ID Summary; Journal Entry Stratification; Duplicate Amount Testing; Exit Program" TO "RADIO 1" AT 24 72 DEFAULT 4 ) 9 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes Dialog Box Objects • Text Boxes – Use to display text (prompt) in the dialog box • • • Enter prompt in the label area Settable size and position Edit Boxes – Use to allow user to enter text in the dialogue box • • • User-entered text is captured as a variable for use in script Default value can be specified Check Boxes – Use to allow user to enter bimodal choice in the dialogue box • • 10 User response is captured as a logical (true or false) variable value Initial state (checked or unchecked) may be specified Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes Dialog Box Objects (con’t) • • Radio Buttons – Use to allow user to make one selection from set of valid values • User selection is captured as a numeric variable whose value corresponds to the order of the buttons (e. g. , = 3 if the third button is selected, etc. ) • Buttons are numbered from top-to-bottom or left-to-right Drop-Down List – Use to allow user to make one selection from set of valid values • • • List items may themselves utilize variable substitution Project Item List – Same as Drop-Down List but the list items are the names of objects within the ACL project, such as tables, numeric fields, character fields, scripts and variables • 11 User selection is captured as a character variable whose value corresponds to the choices defined in the dialogue command Which type(s) of project-item names populate the list is defined in the dialogue command Copyright © 2009 Deloitte Development LLC. All rights reserved.

Dialog Boxes Project Item List Parameters Field/Variable Items ACL Objects List contains: Argument: Character Fields C Views (and Reports) r Character Variables c Scripts b Tables f Indexes i Workspaces w Numeric Fields N Numeric Variables n Logical Fields L Logical Variables l Date Fields D Date Variables d 12 Example …ITEM TITLE "CNf" TO "ITEM_LIST". . . lists character fields, numeric fields, and table names; selected value contained in character variable ITEM_LIST Copyright © 2009 Deloitte Development LLC. All rights reserved.

Practical Use Example – Audit support Developed ACL scripts to support audit of a large financial services client: Client provided monthly line-item cost basis reconciliations by product type to the audit team Imported the underlying transactional data into ACL Scripts allowed user to navigate accounting periods, product types, and line-items to assign variables These variables were to used to filter the underlying data for the specified line-item detail. Users can sample from or export detail for further review Input and sampling populations were profiled using ACL summary and histogram functions 13 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Practical Use Lessons Learned • ACL statistics functions do not store positive, negative, and zero counts as separate variables. These must be computed separately. • 14 Use folders to conceal project scripts and/or data files. One ‘control panel’ script that launches the series of subsequent scripts will help end users avoid confusion and execute the correct scripts consistently Copyright © 2009 Deloitte Development LLC. All rights reserved.

Practical Use Other Lessons Learned • Work with your end users often! • Learn exactly what your users are going to find most useful before coding – gather lots of feedback • Projects can become quite large and difficult to maintain – determine early what should be scripted and what should be left as a manual query • Save logs! This will assist with debugging the scripts. 15 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Summary • Variables are a useful means to store and refer to values input by users or generated by ACL statistical commands • Dialog boxes facilitate the assignment of variables by providing a graphical interface to input variables and parameters • Developing scripts for users is useful when carefully planned and organized 16 Copyright © 2009 Deloitte Development LLC. All rights reserved.

QUESTIONS? 17 Copyright © 2009 Deloitte Development LLC. All rights reserved.

Disclaimer This presentation contains general information only and Deloitte is not, by means of this presentation, rendering accounting, business, financial, investment, legal, tax, or other professional advice or services. This presentation is not a substitute for such professional advice or services, nor should it be used as a basis for any decision or action that may affect your business. Before making any decision or taking any action that may affect your business, you should consult a qualified professional advisor. Deloitte, its affiliates, and related entities shall not be responsible for any loss sustained by any person who relies on this presentation About Deloitte refers to one or more of Deloitte Touche Tohmatsu, a Swiss Verein, and its network of member firms, each of which is a legally separate and independent entity. Please see www. deloitte. com/about for a detailed description of the legal structure of Deloitte Touche Tohmatsu and its member firms. Please see www. deloitte. com/us/about for a detailed description of the legal structure of Deloitte LLP and its subsidiaries. Copyright © 2009 Deloitte Development LLC. All rights reserved.