APL Report Writer Guide

There are over 500 general reports available on the APL system. This section describes the process of running these reports, ensuring reports are using the correct values, and utilizing tools to assist in running reports.

Running Reports on Demand

All reports ask for two key items:

• The account selection criteria
• The date range

Depending on the nature of the report, additional information may be required.

An activity report displays all trades sorted by account.

Example: Activity Report

1. At the Which Function: ? prompt, type ACTIVITY. The following prompt will appear:

   BEGINNING DATE (MM/DD/YY) - HIT ENTER FOR 09/11/17 :

2. Type the beginning date and press Enter. The following prompt will appear:

   ENDING DATE (MM/DD/YY) - HIT ENTER FOR 09/11/17 :

3. Type the ending date and press Enter. The following prompt will appear:

   1396 ACCOUNTS CURRENTLY ON FILE SEARCH FOR WHAT?

4. Type the search criteria and press Enter. The following prompt will appear:

   NEXT OPERATION (HIT RETURN WHEN DONE, OR TYPE HELP):

5. The activity file ACTIV.LRP is created. Type BROWSE at the next prompt to open the activity report

   The following is a list of available categories of reports:

• Activity
• Auditing
• Cash
• Commission
• Fixed Income
• Foreign
• Gains and Losses
• Group Reporting
• Holdings
• Income
• Industry Breakdowns
• Manager
• Miscellaneous
• Performance
• PostScript
• Pricing
• Ranking, Rating and Weighting
• Reconciliation
• Trading Restriction
• Transaction
• Turnover
• Unsupervised
• Windows

Advanced Search Utilities \SELECTAC and \SELECTSEC

Account and security advanced searches enable users to select and retain an account grouping or security grouping for use in a variety of functions.

The selection utilities \SELECTAC and \SELECTSEC enable users to select the accounts or securities to use with functions and reports. Run the same series of steps repeatedly without having to type them manually.

\SELECTAC

\SELECTAC is a utility function that enables users to select accounts based on detailed search criteria. Use this utility at any prompt that requires selection of a SNAM of an account.

Also use \SELECTAC at the WHICH FUNCTION: ? prompt, to view a list of accounts, along with the account master fields you select.

Follow these steps to use \SELECTAC:

1. At any prompt asking for an account SNAM, type \SELECTAC and press Enter. The following screen will appear:

   

2. Using the TAB key, move from field to field within the screen and type the requested data

   As with other screen functions, pressing F1 enables users to see the list of options for the field you are in, and pressing F9 enables you to cycle through those options. The system identifies the choice in the first few characters and press TAB.

   Field	Description
   RR	The numerical status code of the selected accounts. To select more than one RR code, make an entry in the Additional Search Criteria field. Leave the RR: field set at 0 if you do not want to select by RR code. 0–98  : active accounts 99 : closed accounts
   ADM, MGR, or FDTYPE	Administrator, manger or fund type codes. Type the desired code in each corresponding field. Press F1 to see a list of the codes existing in your account master file.
   Common Range Selection	The high end and low end for each item. The ranges are inclusive, therefore if you specify accounts between 0% and 50% invested in equities, the accounts you receive will have no equity holdings. Specify a range from 0.01% to 50% to receive accounts with at least some equity.
   Ratings, Yield, and Duration	These fields select accounts based on market-value weighted averages. The information is updated nightly for all windows accounts, and by committed trades in the trading system. Press F1 for the list of ratings used by S &P and Moody’s. These fields are available only to clients using the fixed- income module.
   Percent in Cash, Percent in Fixed, and Percent in Equity	These fields reflect the actual current percentage in each asset class. The fields are updated nightly, and are affected by committed trades in the trading system.
   Target Fixed and Target Equity	These fields are read from the DESFIX and DESPCT fields in the account master file.
   Additional Search Criteria	This field is used to specify any selection that is not available on the screen based on any account master field. Input one or several lines of additional criteria. Each criteria must begin on a separate line. Multiple criteria should be joined with “AND” or “OR” at the beginning of each subsequent line.

3. Enter the selection criteria and press F7 find accounts. The number of accounts found will appear in the upper right corner of the screen. If the number does not seem correct, review the criteria and change if necessary

4. When selected accounts appear, press F10 to continue. The following screen will appear:

   

5. To select additional account master fields to view for each cash account, use the arrow keys to move to the field and press Enter to select each field. Then, press F10 to process your selection. The following screen will appear:

   

6. When the system lists the selected accounts, it always displays the SNAM and ACT NUMBER. Press F9 to view the additional fields. The following screen will appear:

   

7. Press F8 for additional options for using the selected accounts. The following screen will appear:

   +----------------------------------------------+

   |Please Select an Option|

   |----------------------------------------------|

   |Include All Accounts|

   |Exclude All Accounts|

   |Include All Accounts Matching a String Pattern|

   |Exclude All Accounts Matching a String Pattern|

   |Sort By Field|

   +----------------------------------------------+

8. Use the arrow keys to select an option

• Select Include All Accounts, an asterisk mark will appear in front of the SNAM
• Select Exclude All Accounts, the asterisk mark does not appear in front of the SNAM, and none of the accounts are selected
• Select Include All Accounts Matching a String Pattern or Exclude All Accounts Matching a String Pattern, a screen will appear, prompting the user for the string. Type the string by which the accounts should be selected or deselected
• Select Sort By Field, a screen will appear, prompting you for the fields to sort by. The fields available for sorting are SNAM, ACT NUMBER, ACCOUNT NAME, YIELD, DUR, MOODY’S and S&P

9. Press F10 to use the selected accounts. The following screen will appear:

   +----------------------------------+

   |23 Accounts Selected|

   |Do You Want to Use this Selection?|

   |----------------------------------|

   +----------------------------------+

10. Using the arrow keys, select NO not to use the selection, or YES to use the selection, and press Enter

• Select NO, and return to the Account Selection screen. Select some accounts in order to exit the function and return to the prompts for the report or function
• Select YES, and return to the prompts for the report or function

This account selection can be used for as long as users are logged in or until users enter a new account selection criteria at the SEARCH FOR WHAT ? prompt, either manually or using \SELECTAC again.

\SELECTSEC

The \SELECTSEC utility function enables users to select multiple securities based on criteria that they specify. Use \SELECTSEC anywhere a SECURITY, TICK, CUSIP or SACUS is called for.

Use \SELECTSEC at the WHICH FUNCTION: ? prompt, to view a list of securities based on the specified criteria.

Follow these steps to use \SELECTSEC:

1. At the SEARCH FOR WHAT ? prompt, type \SELECTSEC and press Enter. The following screen will appear:

   

   All selection items are set to include every security in the private databases (the SEC and HOT files) in addition to all the securities in the public databases (BW:SECSA, BW:SECOPT and BW:SECMISC). Ideally, narrow that selection by filling in the fields on the screen. Any field that is irrelevant to a search may be left unchanged.

2. Use the TAB key to move to the fields and type the requested data

   As with other screens, F1 displays the options for the field, and F9 allows users to cycle through them. The system identifies a choice by typing the first few characters and pressing TAB.

   Search Which Files

   Pick the databases to search.

   • All SMF searches all five databases: HOT, SEC, BW:SECSA, BW:SECOPT and BW:SECMISC
   • Private SMF searches your SEC and HOT files only
   • Public SMF searches the BW:SECSA, BW:SECOPT and BW:SECMISC files only

   Select Securities

   Specify owned or unowned securities.

   • Either Owned or Unowned selects all securities
   • Owned selects only those securities held in any of your accounts
   • Unowned selects only those securities not held in any of your accounts

   Issue Types

   Specify the types of securities.

   • All Securities selects all securities
   • Fixed Income Securities selects only those securities categorized as fixed income securities according to issue type (ISSTY)
   • Equity Securities selects only those securities catagorized as equity securities accorind to issue type (ISSTY)

   Industry Code

   • Specify the industry classification of the securities. This finds the private industry table (EDINDNM) and enables users to select any one of those industries. Press F1 to view the table to reference the numerical code for the desired industry

   Common Range Selections

   • The high end and low end for each item. The ranges are inclusive, therefore if specifying a coupon rate between 5.000% and 6.000%, a search returns the 5.000% and 6.000% bonds as well as everything in between. To receive only bonds in between, you would use a range of 5.001 to 5.999

   Rating, Yield and Duration

   • The Yield and Duration fields are available only to clients using the new fixed-income module. These fields are read from the YTM and DURAT fields on the security master file
   • The Rating ranges can be accessed by pressing F1. Type the appropriate range

   Additional Search Criteria

   • This field is used to specify any selection that is not available on the screen based on any security master field. Input one or several lines of additional criteria. Each criteria must begin on a separate line. Multiple criteria should be joined with “AND” or “OR” at the beginning of each subsequent line
3. Enter the selection criteria and press F7. The number of securities found will appear in the upper right corner of the screen

4. Press F10 to continue. The following screen will appear:

   

5. To select additional fields, use the arrow keys to move to the field and press Enter to select. After selecting all the fields to view, press F10 to continue. The following screen will appear:

   

6. When the system lists the selected accounts, it displays the SACUS, CUSIP, and TICK. Press F9 to view the additional fields. The following screen will appear:

   

7. Press F8 for additional options. The following screen will appear:

   +------------------------------------------------+

   |Please Select an Option|

   |------------------------------------------------|

   |Include All Securities|

   |Exclude All Securities|

   |Include All Securities Matching a String Pattern|

   |Exclude All Securities Matching a String Pattern|

   |Sort By Field|

   +------------------------------------------------+

8. Use the arrow keys to select an option

• Select Include All Securities, an asterisk mark will appear in front of the SACUS
• Select Exclude All Securities, the asterisk mark does not appear in front of the SACUS, and none of the securities are selected
• Select Include All Securities Matching a String Pattern OR Exclude All Securities Matching a String Pattern, a screen will appear, prompting users to select the string. Type the string by which the securities should be selected or deselected
• Select Sort By Field, a screen will appear, prompting users for the fields to sort by. The fields available are those that appear with this screen and any additional selected fields

9. Press F10 to use the selected accounts. The following screen will appear:

   +----------------------------------+

   |89 Securities Selected|

   |Do You Want to Use this Selection?|

   |----------------------------------|

   |        NO       |

   |           YES                    |

   +----------------------------------+

10. Using the arrow keys, select NO if do not want to use the selection, or YES if you want to use the selection and press Enter

• Select NO and return to the security selection screen. You need to select some securities in order to exit the function and return the prompts for the report or function you were running
• Select YES, and return to the prompts for the report or function

This account selection can be used while logged in, or until a user enters a new security selection criteria at the SEARCH FOR WHAT ? prompt, either manually or by using \SELECTSEC again.

Macro utilities \LASTACTS and \LASTSEC

The macro utilities \LASTACTS and \LASTSEC enable users to use the results of an account or security selection without re-entering the criteria each time they run a function or report.

• \LASTACTS

  For example, if running a report for MGR EQ TS AND OPEDT GT 12/31/96 and then run another report or function, using the same account selection, the \LASTACTS macro utility can be used. Instead of entering the account selection criteria again at the SEARCH FOR WHAT? prompt, simply type \LASTACTS. The accounts that were found during the original selection are used until the user defines new account selection criteria at the SEARCH FOR WHAT? prompt.

• \LASTSEC

  The same logic applies to the \LASTSEC macro utility. If running a report for ISSTY EQ 28 OR IBM AND CKFR and then decided to run another report or function. Using the same security selection, the \LASTSEC macro utility can be used. Instead of entering the security selection criteria again at the SEARCH FOR WHAT ? or ENTER SACUS, CUSIP, TICKER OR #POOL prompts, simply type \LASTSEC. The securities that were found during the original selection is used until you define new security selection criteria at the SEARCH FOR WHAT ? or ENTER SACUS, CUSIP, TICKER OR #POOL prompts.

Creating Customized Reports

The APL Report Writer enables users to create report templates. Users can define the look of a report, and the data items that should appear in the report. A user can edit and save a template that describes format and contents of a report. The template is then run against accounts, securities, etc. This section covers the following topics:

• What is a template
• Template Editor
• Accessing Template Editor
• Creating a new template
• Opening a template
• Running a template
• Saving changes to an existing template
• Deleting a template
• Exiting the Report Writer

What is a template?

A template is a description of a columnar, subtotal report. It describes the selection and formatting of the data, how the report runs, and what special options are enabled for the report. The basic elements of a simple template (and therefore the elements of any report) are as follows:

• The data in the report (holdings, transactions, etc.)
• How the data is subtotaled
• How to sort the data
• What columns appear in the report
• What are the report titles

An editor is available to create and maintain templates.

Template Editor

It is an interactive system that gives a visual representation of the template, and set various options and fields through menu bar at the top of the each screen.

The interface of template editor consists of the following menus:

• File
• Options
• Columns
• Headers
• Sub-totals

Note: To move around the menu bar and their individual options, use the TAB or arrow keys to highlight a selection, and then press Enter.

File

Contains the following options:

• New: Creates a new template. Once New is selected, go to the Options menu and begin making your selections for the new report template
• Open: Opens an existing report template to run a report or to make changes in the template. To open an existing template, select Open. In the Report Template Retrieval dialog box, input the name of the template and press <F7> to view a list of existing report templates. A list will appear. Scroll through the list to find the desired template, select the desired template and press <Enter>
• Run: Execute a report based on the opened template
• Save as: Saves a newly created template or to make a copy of an existing template under a new file name
• Delete: Removes an existing template
• eXit: Exits Template Editor and return to the APL system

Options

Contains the following options:

• set Report data: Choose the type of report to be created:
  • Holdings: Positions currently held by account
  • Transactions: Security transactions from EDPORT by account
  • General Ledger: Cash transactions from EDGL by account
  • Securities: Security information/characteristics from EDSEC (HOT and SEC files) by individual security
  • Account Database: Account information/characteristics from EDAC by individual account
  • Broker Database: Broker information/characteristics from INFODEX by individual broker
• dis-allow XREF option: Displays an additional dialog box to select specific securities in reports that list security information. For example, if user need a transaction or a general ledger report, which shows transactions for specific securities, then allow the XREF option
• delete Detail: Deleting detail will produce a report that shows only totals
• select download Target: “EXCEL SPREADSHEET” is the only available option—if chosen, a file will be created that can be uploaded into MS Excel (.csv file) through the FTP
• show 1st screen/2nd screen/Printed report/Download report: Displays the layout of the columns and headers selected for screens, printed report, and downloaded report
• show Options: Lists the options selected for the template

Columns

Contains the following options:

• 1st screen
• 2nd screen
• Printed report
• Download report

Lists data fields from each database. Select the required fields. Each field will represent a column in the report.

Note: The fields available on 1st and 2nd screen of the report are different.

To choose fields for 1st screen, select 1st screen. The Screen one fields dialog box will appear. Input the field names for each column and press <F10> to save.

Note: If the screen is two screens wide, it is recommended that an identifying field such as SNAM or TICK be repeated in both screens to make it easier to know which account or security is being viewed when going from screen to screen.

Press <F1> to list available fields. A dialog box will appear listing the available fields. The user can directly choose the fields from this list by scrolling up or down to each desired field and pressing <Enter>. Press <F4> to search for a specific field.

Headers

Edits the column headers and includes a title at the top of the report. This is an optional step. User can save a template without adding a header.

To edit a column header:

1. Choose Screen column headers or Printed report column headers:
2. Choose the fields to edit. Scroll up or down to each desired field and press <Enter> to select
3. Press <F10> to continue. The Column Header Editor dialog box will appear
4. Choose the placement of the column header (left, right, center) and to edit the column header
5. Press <F8> to edit the next column header (if user have chosen more than one to edit); or press <F10> to save
6. Input a title that will appear above the columns on the screen of the report
7. Press <F10> to save

Sub-totals

Displays fields by which the report will be sub-totaled and sorted. This is an optional step. User can save a template without adding a sub-total.

To choose a field for sub-totaling:

1. Choose Sub-total fields
2. Input the name of the sub-total field in the Sub-total fields dialog box
3. Press <F10> to save

   Note: Press <F1> to view list of the available fields.

4. To choose fields for sorting, choose the sorting level
5. Input the name of the sort field

   Note: The user may choose up to four sorting levels. Sorting works in reverse order. This means that the 4th level sort will appear as the primary sort and the 1st level sort will appear as the lowest level sort.

6. Press <F10> to save

Accessing Template Editor

There are two ways to access the Template Editor:

• Using REPORTER function
• Using WIND^REPORTER function

To select a Universe:

1. Type WINDON at At the Which Function: ? prompt. The "WHICH DATA UNIVERSE WOULD YOU LIKE" screen will appear.
2. Press F4. The "Search for what:" field will appear

   

3. Enter the name of the Universe and press ENTER. Select the Universe and press ENTER. The Universe is selected. Type WIND^REPORTER at the WHICH FUNCTION: ? prompt to run report on accounts of the selected Universe

Creating a new template

Perform the following steps to create a new template:

1. At the Which Function: ? prompt, type REPORTER or **REPORTER and press Enter. The template editor will appear:

   

2. From the File menu press Enter to view the template options

   

3. Press Enter on New. The generic default template will appear:

   

4. Select the Options menu and press ENTER to select the data. The options will appear:

   

5. To choose the type of report, select set Report data, and press Enter. The six report data options appear:

   

6. From this list of menu items, select the type of report to create:

   Holdings: This category of report summarizes securities holdings. The following options are available:

   • One account displays the detailed holdings for one account
   • One summarized account displays the summarized holdings for one account
   • Many accounts displays the detailed holdings for many accounts
   • Firm wide summary displays summarized holdings across a group of accounts
   • Many summarized accounts displays summarized holdings within a group of accounts

   Transactions: This category of report selects or summarizes transactions based on the specified criteria. The following options are available:

   • One account displays transactions for one account
   • Many accounts displays transactions for many accounts

   General Ledger: This category of report summarizes general ledger entries. The following options are available:

   • One account displays general ledger transactions for one account
   • Many accounts displays general ledger transactions for many accounts

   Securities: This category of report draws information from the HOT and SEC files within the security master database.

   Account Database: This category of report draws information from the account master database.

   Broker Database: This category of report draws data from the broker database in Infodex.

   Note: Selecting a type of report is a mandatory step.

   Following steps are optional as required:

   1. To run the report for multiple securities, from the Options menu, select dis-allow XREF option and press Enter. This should be set to Yes to run the report for selected securities. If the option is set to No, the report runs for all securities
   2. From the Options menu, select the delete Detail options and press Enter to either include or delete to show the amount of detail to appear in the report. If this is set to Yes, all detail lines will be removed, leaving only the totals
   3. From the Options menu, choose select download target and press Enter. Currently, only an MS-Excel spreadsheet is allowed. If chosen, a downloadable report will be created automatically using the download fields each time the report is run. If the user has DDE capability, it will be automatically downloaded to the PC and imported into an MS-Excel spreadsheet
7. Select the Columns menu and press Enter. The Columns menu options appear:

   

8. Select the screen or report for which column fields should be defined and press Enter. The Screen one fields dialog box will appear: