# Presentation: 153664

## I2I 2000NEI Grants extension system

- Platform:
- Microsoft SQL Server 7 + Visual Basic 6
- Terry Williams, Chief
- Information Technology Management Branch, NEI
- 		Carolyn Bealle: I2I user support
- 		Paul Goldberg: NEI network support
- 		David Scheim, Developer and I2I support
- (_[davids@naxs.net](mailto:davids@naxs.net)_, 540 552-8014)

## Contents

- Slides 3-6: System overview
- 7: Attributes of the 11 I2I modules
- 8-11: Reporting system features
- 12-65: Screen snapshots showing basic functions of 9 I2I modules
- 66-72: SCORE coding module
- 73-111: Reporting module

## Client/Server Platform

- Software
-  Server: Microsoft SQL Server 7.0
- Client: Visual Basic 6
- NEI network
- Server (EPS): dual Pentium processor, 1 gig RAM
- Clients (Bldg. 31, EPS):  PCs with Windows 98, 2K, NT
- For demonstration: client and server on Sony VAIO notebook
- I2I runs various functions at about 50-80% of NEI platform speed

## Database Architecture

- 200 Tables and 150 Stored procedures in five databases
- 12 tables in the grants database hold main data
- Other tables for lookup, abstracts, report catalog, system support
- Semi-normalized relational database structure for performance, usability
- All data modifications thru stored procedures
- Data content and source
- Active, pending, and historical data
- IMPAC fields plus 50 NEI fields and NEI Scientific coding

## IMPAC source and transfer schedule

- Weekly data transfer from IMPAC I
- SQL Server 7 provides easy to use, high-speed bridge to Oracle (IMPAC II)
- Daily transfer of major fields from IMPAC II via SQL Server 7
- (in development for I2I – in production under same platform at NIAID)
- Evolving bridge to IMPAC II will replace IMPAC I transfer
- Instant upload of selected fields (PCC, HSA) to IMPAC II (in development)

## Administration and maintenance

- Virtually no DBMS system administration required
- No corrupt data in system lifetime
- Adding/changing users and rights automatic thru I2I
- Client install: system files copied thru wizard
**NO CHANGES** to SQL server 7, VB 6, client PCs since initial installation in 1999
- No client changes even on PCs upgraded to Win 98 or 2K
- 1 Mb .exe on network—updates transparent to users
- Total operation and maintenance support:
- < 100 hours over two years, not including enhancements and minor programmatic-driven modifications

## Attributes of the 11 I2I modules

- Seamless integration with Windows
- Sub-second response for most operations
- No limit on size of result sets
- Instant scrolling of even very large result sets
- All system modules allow screens to be resized, maximized or minimized
- Integrated help system displays module-specific help
- Snapshots available in most modules by double-clicking summary records
- Save or undo forced after any data change – exit not possible otherwise

## Reporting – Catalog features

- Integrated, keyword-searchable catalog
- Dual public and private catalogs allow user to copy, modify a public report
- Split screen auxiliary catalog may be hidden (default) or opened
- Keyword/phrase is searched against title and comment box in the catalog
- 600 (different) NEI reports cataloged in I2I 2000
- 3,935 (different) NEI legacy reports cataloged in I2I predecessor (1981)

## Reporting - Formats

- Report formats are independently cataloged and selectable for any report
- The NEI has created 50 cataloged report formats
- Report formats are created within I2I
- Formats allow sums, counts, other functions by six levels of breaks
- Formats allow easy generation of multi-line reports and multi-line headers
- User may easily create a private copy of a format and customize it
- A SQL section allows very complex capabilities to be added to a report format

## Reporting – Selection specification

- Point-and-click generation of report selection
- Selection specifications of unlimited levels of Boolean joins are allowed
- AND, OR, or complement (A and not B) may be used at any level
- Tree structure allows easy handling of simple or complex selection spec.
- Visual selection using any field of any cataloged table or a shortcut tab
- Table joins are cataloged – links are automatically made

## Reporting – Other features

- Special report tab allows point-and-click addition of scientific codes to selection
- Clipboard allows copying of selection tree sections or format sections
- Output choices: printed report, print preview, document, or spreadsheet
- Summary option allows snapshot for any selected record
- Parameters may be specified as short-cuts, for date-dependent fields, or for prompt at run
- “Labels” allow tagging of sub-criteria within selection
- Labels may be included in break specifications and headings
- Output of most reports is generated within 5-10 seconds, rarely more than 20 seconds

## I2I open	 

- I2I splash screen

## Snap open	 

- Snapshot module

## Snap open maximized, Smith	 

- Selection by initial string of PI (“Smith”)
- (Snapshot screen resized – displays for all modules can be maximized or resized)

## Snap Smith result 

- Summary screen for “Smith”

## Snap smith scroll down	 

- Standard windows scrolling (no delay)

## Snap “s”	 

- Queries with very large result sets (e.g. all PIs beginning with “s”) respond nicely

## Selection by HSA, active, R, type 5	 

- Selection by HSA, mechanism R.., type 1, awarded, FY 1999, sort by grant number.  Note that either current (default) or history grants may be specified

## Results	 

- Summary screen for selected grants

## Detail tab	 

- Double-clicking brings up detail information

## Award tab	 

- The Award information tab

## Req/reco. tab	 

- Requested and recommended awards

## Coding tab	 

- NEI coding (entered via coding module)

## Pi coding	 

- Other PI info and coding

## T/f	 

- Trainee and fellowship coding

## abstract	 

- Abstract

## SCORE tab 

- Scientific coding

## Scrolling	 

- Scrolling via navigation bars or summary screen (this grant has additional coding)

## Admin update

- The update button allows administrators only to update any NEI data field, current or history.  Save or undo is forced prior to exit

## Query options

- The query screen allows several selection options, including phrase in title or institution, or any SQL selection for power users

## Exit SNAP 

- Exit SNAP module to splash screen

## HSA coding open	 

- The HSA and SCORE coding modules use the same query screen and summary display as the SNAP module

## PI field drop down	 

- To code, click the PI and the dropdown arrow.  Double clicking will bring up the snapshot.

## Coding screen	 

- HSA coding screen

## Sample coding done	 

- NEI staff may enter or change any field to which they have rights – undo or save is forced.

## Help link 

- Module-specific help link for each module

## Help displayed	 

- Help for HSA coding

## Council letter start	 

- To generate Council letters, start by checking display Council letter info. in the query screen of HSA coding.  “Current pay plan grants” is a convenient selection criterion that would typically be used.

## Query result	 

- Select grant to code or any grant to generate all applicable letters for the Council.

## Council letter coding screen	 

- Detail screen allows both coding of fields used in Council letters and generation of letters (upper right).

## Generate one or all letters	 

- After selecting letter type, specify either generate one letter (for grant displayed) or generate ALL letters (for applicable grants to current Council).

## Message after generating all ltrs	 

- All (no pay) letters were generated.  Generate letters, etc. upon exit is automatically checked (can be unchecked).

- “Add ad. hoc. par.” could be checked to add a marker for a special paragraph in the letter for the grant displayed.

## Redo and undo	 

- Menu functions allow undoing council letter coding or redoing letter generation for successive previous Council letter batches for the user logged into the system.

## Exit to letter generation 

- Upon system exit, prompt appears to generate letters, envelopes and/or a data checklist for selected grants.

## Message before generation	 

- Message tells user about documents to be generated.

## Processing continues	 

- Word documents are generated as processing continues

## Word document with letters	 

- The Word document containing the generated letters

## Batch update module	 

- Runs mainframe programs, FTP file transfers, SQL stored procedures, C program, other processes to synchronize NEI grants database to IMPAC I data.
- Queries were not reliably available in 1999 from IMPAC II for this purpose (can be so upgraded).
- Only new or changed IMPAC I records are used.
- Data is synchronized, new fields (e.g. “Dr. Smith” for salutation) and tables (e.g. with imputed type 5’s) are generated.
- Data is downloaded to legacy mainframe file at end to support 3,935 cataloged legacy reports that are occasionally run.

## Batch update module	 

- Grants database is backed up before and after update, NEI database (not changed) after update.
- FTP commands use waits, retries if needed.
- Extensive checking is performed after each process – update terminates with error message if problem
- Counts, dates written by mainframe programs are checked
- SQL procedures check data consistency
- Upmsg table logs all update events; checks and messages show update progress.
- User may selectively “check” steps or specific processes within steps to skip during processing.

## Update screen	 

- Initial screen for batch update

## Abstract update	 

- The abstract update refreshes the abstract database using a mainframe program, FTP, database bulk copies, and stored procedures.  It will be updated to link to IMPAC II.

## System editor start	 

- System editor allows edit of system tables by an I2I user with administrative privileges

## Table edit	 

- Table ve548, ophthalmology related codes, has been selected – records can be added, deleted, modified.

## upmsg	 

- If the upmsg table is selected (update messages), all messages beginning with the start of the most recent update run are automatically selected.

## Users table	 

- The users table controls rights to data and which I2I modules a user may run.  All database administration functions are automatically performed when users are added or changed in this table and the menu item “modify permissions” is selected.

## End FY 

- The End FY routine adds a new FY’s data to the history database, checking for duplicates and transferring data from several tables.  It then extracts the grantfy database from the past FY’s data.

## intramural	 

- The intramural system allows Z01’s to be added and modified.  It also allows Z01’s from one FY to be automatically copied to the next with the grant number incremented, typically done to begin processing for a new FY.

## Intramural populated	 

- Menu commands support various system functions.  When data is saved, replication is performed to the history and FY databases.

## Pay plan module	 

- Extramural program director begins by selecting a program and mechanism group.
- Grants are initially ordered by percentile rank.
- The menu command “sort and renumber as 5, 10,  15 ...” sets pay ranks by 5’s using the initial ordering.
- Program director can then renumber the pay ranks to desired pay order.
- Menu commands then allow coding grants at and above a certain rank as pay, below as no-pay, and then coding the rest as holds.

## Pay plan initial screen	 

- Initial screen for pay rank module.  User selects a program and mechanism group to process.

## Pay plan with reorder menu	 

- Pay ranks are then typically set 5, 10, 15 ... using a menu command.

## Pay plan w. pay/hold code menu	 

- After pay ranks are finalized, menu commands are used to code pays, holds, and no-pays.

## Pay plan – snap display	 

- Snapshots can be displayed by double-clicking on any summary line.

## Help system query screen	 

- The help system allows system administrators to dynamically add help entries and users to search for them by key words or using tree navigation.  System admin. functions shown don’t appear on ordinary user’s screens.

## Help system main screen	 

- The help module is used for both user support and system documentation.

## SCORE coding open	 

- SCORE coding begins with the same query and summary screens as HSA coding and Snapshots.

## SCORE pick Smith	 

- Let’s pick on David Smith to demonstrate SCORE coding.  SCORE = “Scientific Coding of Research Emphasis.”  SCORE coding is typically performed just once after the grant is awarded.  All trans-NIH and other scientific-based reports can then be easily generated when needed.

## Find prevention term	 

- Note this grant already has some SCORE coding displayed on the right.  Let’s find terms containing “prevention” in the SCORE terms to flag this grant to appear on prevention report (using the “Find” arrow).

## Prevention found	 

- Prevention terms appear as a child and grandchildren of axis 5, “Trans-NIH and reporting.”

## Prevention coded	 

- Double-click “prevention, direct” to code it (appears on the right).

## Other coding on SCORE	 

- Other coding on the respective tab is typically entered at the same time as are SCORE codes.

## Terms maintenance	 

- This screen supports SCORE term maintenance.  Tree restructuring is done using drag-and-drop.  Internal SCORE codes do not change even if terms are moved or modified.

## Report open	 

- The report module opens to the catalog (600 reports cataloged in I2I 2000, 4,000 reports in the mainframe I2I predecessor).

## JRW	 

- Let’s pick John’s reports (as an administrator, I can do this), and search for something he did on a request from Mike Coogan using the Find (->) button.  Report 156 is the one we want.

## Open Fuch’s report	 

- Double-clicking on the report in the catalog opens it up.

## Run	 

- We pick the run tab and click print preview to run the report

## Print preview	 

- Report appears in 6 seconds – first page is shown here.  It can be printed or saved to a document or spreadsheet.

## Start copy public report	 

- Now, I will copy and modify a public report.  I begin by unchecking “Hide auxiliary directory.”

## Perform copy of ACTIVE 1	 

- I pick the ACTIVE public directory below, scroll to my last report on top, and click the menu choice “copy selected auxil. (bottom) after selected top.”

## Copy confirmation message	 

- Confirmation message appears for the copy function specified.

## Modify title of new report	 

- Copied report appears at end of catalog.  I modify the title, adding, “for MDO”

## Open and add MDO sel.	 

- I open the report, go to the selection tab, and specify MDO for the HSA, then click the “ADD” button.

## Sel. added	 

- SQL for HSA=‘MDO’ is added to the Selection specification tree.

## Modified report is run	 

- We pick the run tab and click print preview to run the report

## Print preview	 

- Report appears – first page shown.

## Format tab	 

- Now let’s modify the report to also show the human subjects code (E44).  We go to the format tab and first click “Copy to” a private format (a non-admin user is not allowed to modify a public format).

## Click modify after copy	 

- After format is copied, we click “modify.”

## Change format title	 

- We change the format title, adding “with human subjects.”

## Select project title in tree	 

- We open the detail section, “new line,” in the format tree and select the project title.

## Change length from 50 to 43	 

- We change its length from 50 to 43 to make room for the human subjects code.

## Add E44 to format	 

- We scroll to the human subjects field on the right and double click to add it as the last child of “new line.”  Drag and drop would be used to change the order if desired.

## Change field header.	 

- We change the field header to “human|subj.” – this splits it over two lines in the report header.

## Change length and offset	 

- We change the field length to 5 to accommodate the header and the space from the prior field to 2.

## Print preview	 

- Report appears with added human subjects code – first page shown.

## New report	 

- Let’s now create a new SCORE report on macular degeneration.  To begin, we click on the catalog window, select new from the menu, and change the report title.

## SCORE setup	 

- We go to the selection tab and click “SCORE setup” – this adds selection for awarded and “EY” primary.

## Add mac. Deg. terms	 

- We find the macular degeneration terms by keyword and double-click them to add to the selection tree.

## Select format	 

- We select an appropriate public format in the format tab.

## Run report	 

- We select the Past FY database source and click Print preview.

## Report last page	 

- Report appears – last page shown.

## Complex rpt with labels	 

- This is a complex report using “labels” – designations for subgroups within the selection criteria that can appear as breaks on the report.  John Whittaker created this report (including its format).

## Label dupl. action	 

- There are three options to handle records that are selected under more than one label – “Concatenate” is the default.

## Rpt – last page	 

- Last page of the multi-line report, showing the “Optic Neuritis” category and grand count and total.

## Show summary button	 

- The “Show summary” output action displays an unformatted summary of selected records.

## Summary output	 

- Double clicking any record displays the snapshot.  This can be used to validate a complex report.

## Snapshot from output	 

- Snapshot for the first selected grant

## Show SQL button	 

- The “Show SQL” button displays the internal SQL code I2I generates to run the report.

## SQL shown	 

- ... if you really wanted to know

## Top ten report using SQL	 

- Here is a report the uses the SQL section of the report format to perform special processing.  SQL Code may be inserted at three points during report processing to handle complex reporting requirements

## SQL coding	 

- SQL code is used to order by the grouped sum of total awarded for each institution.

## Report output	 

- First page of the report generated