Which Reporting Tool Is Right for You?

Which Reporting Tool Is Right for You?

Have you found yourself wondering where you can go to get information on extramural applications and/or awards from IMPAC II/CRISP? Do you need information on one particular application/grant? Do you need the abstract for one or more grants?

Your options for retrieving information from IMPAC I and CRISP were limited and straightforward. You could logon to WYLBUR or TSO (the CIT mainframe) and, using the Grants Management Information Systems menu, access such systems as DRGINFO, S-CRISP, and Snapshot. If these systems did not meet your needs, you could always go to your IC IT staff and ask them to write a program and produce a report for you.

The data retrieval process for IMPAC II is not as straightforward or limited. This article will point you in, what we hope will be, the right direction to get the information that you need using tools that are available to all ICs. All of the data retrieval applications/systems/methods listed below require that you have an IMPAC II userid and password that may be obtained, if you don't already have them, from your IC's IMPAC II Coordinator (http://impacii.nih.gov/support/techrep_list.htm). In some cases, a particular IMPAC II role (privileges) may be needed.

All IMPAC II Modules/Applications contain the ability to produce at least one report. From most of the modules/applications, you can choose from many reports. While this article will touch on the currently available tools to retrieve IMPAC II data, it will not detail the reports available from the majority of the modules/applications that are used in the course of conducting NIH business. The modules/applications not covered are Committee Management, Grants Management, Peer Review, Trainee Activities, Receipt and Referral, and SITS.

MODULES AND TOOLS

QuickView
CRISP Plus
CRISP on the WEB
IC Operations (ICO)
Elementary IMPAC II Query Worksheet
Electronic Council Book (ECB)
IC Query & Reporting Tool (IQR)
Toolman's Index page.
MS Query
PowerViews
SQL*Plus
Query Builder

QuickView is a user-friendly query-only application that will give you access to the most frequently used pieces of information about a grant/application. You enter search criteria that may return one or more applications/grants on a "hit list". After you get your "hit list", you can view the information (Overview, PI/Staff, Organization, and Budget) on a grant-by-grant basis. You can also view/print a "Snapshot" report on a grant-by-grant basis. The "Snapshot" is a nicely formatted, easy-to-read report akin to the "Snapshot" from the DRGINFO system. It shows:

Information Type	Information Items
Project	grant number, project start and end dates, APPL_ID, primary PCC, latest encumbrance date, number of future years, document number, IMPAC II status (awarded, etc.), RFA/PA number, project title, and former grant number
Review Detail	IRG code, priority score, percentile, IRG flex code, group code, group extension, special consideration, reference code, SRA designator, SRA flex code, IRG action and council date
Administrative Codes	human subjects, animal subjects, minority, gender, tracking exception, and phase 3 clinical trial
Multi-IC/PCC	IC, responsibility (primary, secondary), PCC(s)
PI Information	PI name, home address, e-mail address, phone number and extension, and FAX number
Institution Information	name, IPF code, EIN, submitting department, institution address, e-mail address, phone number and extension and FAX number
Pre-award Budget Detail	type (requested, council, etc.), period number, fiscal year, direct cost, indirect cost, and total cost
Award Budget	period number, fiscal year, budget start and end dates, direct cost indirect cost, fee amount, unobligated balance amount, non-federal share amount, and total cost
CANs	period number, fiscal year, CAN(s), direct cost indirect cost, fee amount, unobligated balance amount, non-federal share amount, and total cost
Current Fiscal Year Items	account code, line item description, amount and item count

While your "hit list" may contain many applications/grants, you can only view/print the "Snapshot" for one grant at a time. This is a quick and easy way to get information one grant at a time. QuickView can be accessed using either the client/server or WEB versions. The WEB version can be accessed from the IMPAC II WEB page at http://impacii.nih.gov/webapps/.

CRISP Plus, an enhanced version of CRISP, is only available to NIH staff due to its capability to access and return confidential information (such as applications, priority scores, percentiles, etc.). If you have access privileges to use QuickView, then you can use CRISP Plus. Since a CRISP Plus search can return applications as well as awards, it will only search records in the "Online Transaction Processing" (OLTP) database, and the OLTP only contains records for the past 5-6 years. The WEB version of CRISP PLUS can be accessed from the IMPAC II WEB page at http://crisp.cit.nih.gov. It also has a client/server version.

CRISP Plus allows you to query IMPAC II using keyword searching against grant abstracts, summary statements and/or titles, as well as to query using Thesaurus term(s) on awarded grants (terms are not assigned to applications). It also allows you to query on such items as principal investigator name, institution, grant number, status, PCC, RFA/PA number, etc. These criteria may be combined with the keyword search or used as the only search criteria. If you are doing a "term" search and your cursor is sitting in one of the blocks labeled "Topic", then you have the ability to view the current year's CRISP Thesaurus by clicking on "Thesaurus" under "View" on the left side of the screen.

One good feature of CRISP Plus is the ability to save your query specifications for future use and retrieve previously created query specifications. You are able to specify that your saved query specification should be available to only your IC or to all ICs.

A limitation of the application is that if your "hit list" exceeds 175 records, you are not able to navigate to detail screens nor are you allowed to click on the "Last Record" icon. There is a strong possibility that you may not be able to search on more than 75 keywords at a time in one query. To search on more than 75 keywords at the same time, you should consider using the IC Query and Reporting Tool (IQR) (see separate paragraph on this application).

There are six pre-programmed reports that may be requested directly from CRISP Plus. They are:

Abstr/Terms	This abstract report shows basic grant information, project terms, and the abstract.
Snapshot	This report is actually the QuickView Snapshot report. It shows basic grant information, review details, administrative codes, PI information, and budget details.
Hit List	This report shows the query parameters used to derive the hit list plus the grant number, council date, PCC, PI name, institution name, project title, award period start date, and award specialist.
Totals by IPF	This report shows the IPF number, institution name, dollars awarded, and total number of projects by IPF.
Totals by IRG	This report shows the IRG, dollars awarded, and total number of projects by IRG.
Totals by IC	This report shows the IC, dollars awarded, and total number of projects by IC.

CRISP on the WEB is the application that the public uses to access abstracts and limited administrative information on current and historical NIH awards. Availability of information, including the abstracts, on current grant awards may be delayed several months after award. This is due to the time that it takes for each award to be manually coded with the Thesaurus terms and entered into the system. If you are interested in historical award data, you may select one or more years (1986-1998). For current award data, there is no ability to select the year(s). This application may be accessed using a WEB browser at the URL http://www-commons.cit.nih.gov/crisp/.

The IC Operations (ICO) application has both client/server and WEB versions. It is used primarily by the ICs for assigning incoming grant applications to program officials and program areas, assigning and maintaining Program Class Codes (PCC), entering Advisory Council actions, and maintaining grant-related data. It is a great application for retrieving information on a grant-by-grant basis, such as program official name and program notes, that is not available or readily retrievable through QuickView or any other application. Budget Officers would find three of the pre-programmed reports dealing with obligations particularly useful. Other available reports include:

Three pre-programmed reports showing SRG meeting information including the SRG schedule and meeting location that would be useful to program officials,
A Program Officer's worksheet report showing all of the sign-off notes,
A workload report (really a nicely formatted "hit list" report),
A PCC definition report, and
The QuickView Snapshot report.

A limitation of the application is that if your "hit list" exceeds 175 records, you are not able to navigate to the detail screens nor are you allowed to click on the "Last Record" icon.

The Elementary IMPAC II Query Worksheet is a WEB application developed by Jackie Sanders, NIMH, which is only available to NIH staff. It employs a very user-friendly method of entering your search criteria and picking the information that you want back from IMPAC II. In addition to being able to get a report, you can download your query results into a delimited file for import into a spreadsheet (a .csv file) or for use with a word processor (a .doc file) for correspondence or to produce labels. You may query for both current and historical information. The application has step-by-step instructions on the screen, with examples where appropriate, that guide you through the process. Some nice features of this tool that provide some flexibility in retrieving and reporting data include:

Sorting: You can pick from items to sort your report in either ascending or descending order and do several levels of sort. Certain sort items can be used as control breaks with the ability to specify whether you want details only, titles and details, titles and details and totals, or totals only.
Report Layout: You are given the ability to specify how your report will look to a certain extent. For example, you can have the report number the records, hide all header information except the title, provide space for notes, specify your own report title, specify how you want the records separated (three choices), and limit the number of "hits".
Saving Report Specification: When you find a report from this application that you like and will need on a recurring basis, you can save the specification for future use.

The down side to this application is that to get one piece of information, you may have to select a category that gives several more pieces of information that you do not need or want. For example, to get the total awarded direct cost, you must select "Budget" which will give you requested and future dollars, etc. along with the awarded direct cost. Another down side is that it is slow to return the results of your query. When you request that data be downloaded, it uses standard templates. There is no correlation between what you asked to see on the report and the pieces of information that are contained in the downloaded file, but there is a correlation between the selected application/grant records and the sort order.

The staff version of the Electronic Council Book (ECB) is a WEB application that is very easy to use and only available to NIH staff. You can specify, by selection, search criteria for multiple council rounds from 01/98 through current and/or multiple ICs. Features include:

The search criteria can be one or more of the following: principal investigator, review group, special emphasis panel, program class code, activity code(s), primary only, dual only, primary and duals, score ranges, RFA/PA number, special concerns such as human subjects, phase 3 clinical trials, AIDS, foreign grants, direct costs "greater than or equal to" or "less than or equal to", summary statement release dates, institution name, IPF code, application number, search summary statement text, search project titles, search for no summary statement, and search for not scored.
Two forms of the "hit list" are available-standard and resume. The standard hit list contains the application number, project title, principal investigator, IRG, program class code, priority score, and percentile. The resume hit list contains the same information as the standard hit list plus the resume section of the summary statement. Both hit lists may be printed. If the summary statement has been released, from the "hit list" you can click on the application/grant number and see the summary statement in its entirety or click on the word "email" and send a message to the program officer for that particular application/grant.
The hit list results may be download as an ASCII delimited file which can be imported into another software package such as a spreadsheet or word processing package. If you select the "download" option, there are currently 64 pieces of information that you can have downloaded for each application/grant that meets your search criteria.

The Council version of the ECB, which is only available to Council members (provided they have an IMPAC II userid and password), is a WEB application without the download capability. Council members can only see applications going to the current council round and only for their IC.

The IC Query & Reporting Tool (IQR) is a WEB application that functions as an advanced mechanism to query the IMPAC II Information Reporting Database (IRDB). Where CRISP Plus will only allow you to query on up to 75 keywords, the IQR will allow you to query on hundreds of terms. CRISP Plus use words for searching while the IQR uses the CRISP terms (numbers) for searching. To use this tool with the least amount of frustration, you should have an elementary understanding of the Standard Query Language (SQL). The tool provides you with the ability to copy and paste from sample queries available from within the application and either run them "as is" or make the appropriate changes and then run them. Once you have a query that works for you, the tool allows you to save the query for future use by your IC or all ICs. A nice feature of this application is the ability to click on one button and have the application check the proposed query to insure that all of the terms are valid. The developers of this application tried to make it as user-friendly as possible considering that knowledge of SQL and basic programming is really needed to construct a successful search. From a successful search, you can get one of three reports-Hit List, Abstract/Terms, and Budget-in either .pdf or ASCII text format. The ASCII text format is suitable for importing into a spreadsheet.

It's the Toolman's Index Page!: This web page can be accessed at http://impacii.nih.gov/tools. From this page, which is available to NIH staff only, you can run a number of nifty and informative queries that do not require an IMPAC II userid and password. They are:

IPF Name Search - Find an IPF number when you have a full or partial grantee name. The query returns the IPF code, full institution name, city, state, and ORI certification for all institutions matching the search string. Click on the IPF number and you get an IPF "Snapshot" report in table format showing congressional district number, address, city, state, zip code, country code/name, county code/name, phone number, NGA e-mail address, ORI cert. Mail date, ORI cert. Exp. Date, ORI cert. Group code, ORI cert. Stat. Code, SMSA code, medical school flag, multi-campus flag, multi-campus system ID, AA institution flag, woman- owned flag, ownership code/description, org. type code/description, etc.
IPF Geo Search - Similar to number 1 above but allows you to search by city, state, zip or address of an organization. The query will return a list of grantee institutions based on a geographical search with IPF Number, Institution Name and Location, as well as ORI Certification Status. Click on the IPF number and your get an IPF "Snapshot" report in table format showing congressional district number, address, city, state, zip code, country code/name, county code/name, phone number, NGA e-mail address, ORI cert. Mail date, ORI cert. Exp. Date, ORI cert. Group code, ORI cert. Stat. Code, SMSA code, medical school flag, multi-campus flag, multi-campus system ID, AA institution flag, woman- owned flag, ownership code/description, org. type code/description, etc. You can enter a partial address or city name and still get a match. This query will not return anything if you have more than 200 matches to your request.
IPF Snap - Returns "snapshot" type information about a grantee organization. The organization's IPF number is needed in order to run this query. You can use one of the above two searches to obtain the IPF number. Click on it to get an IPF "Snapshot" report in table format showing congressional district number, address, city, state, zip code, country code/name, county code/name, phone number, NGA e-mail address, ORI cert. Mail date, ORI cert. Exp. Date, ORI cert. Group code, ORI cert. Stat. Code, SMSA code, medical school flag, multi-campus flag, multi-campus system ID, AA institution flag, woman- owned flag, ownership code/description, org. type code/description, etc.
IPF Active Grant Search - This query will return a list of active grants based on a grantee institution's IPF number. You must enter a complete 7-digit IPF number, no wild card searches are allowed. For this query, the definition of an active grant is one in which today's date is between the budget period start and end dates of the award. If you do not have the IPF number of the institution, you may click on the "IPF Number Search Query" button from this screen and it will take you to the IPF Name Search screen (Number 1 above). The results of the active grant search gives you the grant number, organization name, principal investigator's last name, fiscal year, program class code, latest DFM date, total awarded dollars, and budget start and end dates.
E-mail Enabled Grantee List - Returns a list of all institutions that are e-mail enabled for notice of grant awards. This query may take a minute or so to run. You get back a list showing the IPF number, institution name, institution's city and state and zip code, and the e-mail address for notice of grant awards only.
EIN Search by Name - Find the EIN number when you have a full or partial grantee name. From the query, you get a table showing the EIN number, organization name, valid (yes/blank), and FDP (yes/blank). If there is a "yes" in the valid column, then the EIN can be used for grants; otherwise, it cannot be used for grants.
EIN Search by Number - Find a name associated with an EIN when you enter all or part of it. From the query, you get a table showing the EIN, organization name, valid (yes/blank), and FDP (yes/blank). If there is a "yes" in the valid column, then the EIN can be used for grants; otherwise, it cannot be used for grants.
FDP List - This query gives you a list of all organizations flagged in IMPAC II as being FDP (Federal Demonstration Partnership). The list shows the EIN, organization name, and FDP equals "yes". At the top of this list is a link that will take you to the FDP Website so that you may obtain an official list.
Assignments - Enter a grant number and see who is assigned to that grant. This query will give you a list of persons assigned or connected to a particular set of grants in table format. The table shows the grant number, program class code (PCC), the role (Grants Management Officer (GMO), Grants Management Specialist (GS), Institute Program Official (PO) Principal Investigator (PI)), and the person's name, phone number, and e-mail address.
Human Subject Bar List - A list of FY 99 and FY 00 records by IC which still have a human subjects code in the 40s (bars). The query returns a table showing the grant number, fiscal year, grantee (organization) name, and the human subjects code.
Signed (but not released) WIP Search - This query allows you to search the IMPAC II database for grant records that have been worked up and signed by your Grants Management Office but for some reason have not yet been released. You enter your IC's two-character abbreviation or pick it from a drop-down list. A table is returned which shows the grant's APPL_ID, grant number, principal investigator's last name, fiscal year, signed by last name, signed date, initial DFM date, and latest DFM date.
Old WIP Search - This query allows you to search the IMPAC II database for old unreleased Work-In-Progress records that were created in IMPAC II GM Module but have never been released. You enter your IC's two-character abbreviation or pick it from a drop-down list. A table is returned which shows the grant's APPL_ID, grant number, principal investigator's last name, fiscal year, created by last name, created date, program class code, and latest DFM date.
Unawarded Administrative Supplement Search - This query searches for administrative supplements that were created in the IMPAC II GM Module and have not been awarded. The table that is returned from this query shows the APPL_ID, grant number, principal investigator last name, fiscal year, program class code, created by last name, created by date, and WIP. This is a good tool to use for end-of-year cleanup.
IMPAC II IMPP Table/Column Search - Search the IMPAC II IMPP schema for column and table names. This query is mostly for programmers/developers and not for the average user (because the average user probably wouldn't have a clue as to what to enter for the query or a clue about what is returned). If you enter the name of a table, you will get back a list of all the columns in that table. If you enter a column name, you will get back a list of all of the tables that contain that column.
IMPAC II IRDB Table/Column Search - Search the IMPAC II IRDB schema for column and table names. This query is mostly for programmers/developers and not for the average user (because the average user probably wouldn't have a clue about what to enter for the query or a clue about what is returned). If you enter the name of a table, you will get back a list of all the columns in that table. If you enter a column name, you will get back a list of all of the tables that contain that column.
IRDB Schema Changes - This query is mostly for programmers/developers and not for the average user (because the average user probably wouldn't have a clue as to what the information returned means). This query returns a table showing: ID number, TAR number, object type, planned date, implemented date, status, change type, object name, column name (if applicable), and comments.
Transfer Request Log Search - This is a query that is mostly for IC data managers and not for the average user. All changes to IMPAC II data that are bridged back to IMPAC I are put in the IMPAC II transfer request table. After the transaction has been sent nightly to IMPAC I, a copy of the transaction is stored in the IMPACII_TRANSFER_RQST_LOGS_T table. The query returns a table showing the APPL_ID (which you needed to do the search in the first place), grant number, request type code and description, data/other info, and date/user ID.

MS Query provides the more advanced user with a method to retrieve data from either the IMPAC II online or reporting databases through MS Excel and MS Access. While it is a graphical application used to retrieve and organize data, you must know how and where the data is stored in IMPAC II. In other words, you must know how IMPAC II has the data organized and in which tables. If you are an average user and all of the information that you will need is contained in one of the PowerViews (see paragraph below), then MS Query can be a very useful tool. If you access MS Query from MS Excel, then you can easily bring the data into Excel where you can use all of its features and capabilities, including Pivot Tables, to do data analysis and charting. Unfortunately, Excel's ability to produce a nicely formatted report is very limited. If you access MS Query from MS Access then you can "publish" the data with MS Word or "analyze" the data with MS Excel (in other words, you can send the data to either one of those applications), utilizing that particular application's features and capabilities. You can also bring the data into a local Access database. One of the really nice things about using MS Query through Access is that Access has the capability to allow you to create nicely formatted reports.

PowerViews are specialized "views" of the more commonly used pieces of IMPAC II data. They combine data from numerous IMPAC II tables into pre-defined groupings. For example, information related to the budget would be found in the PV_BUDGET PowerView. With the PowerViews, the average user does not need to know as much about the location (table and column name) of common pieces of information. You can get more detailed information about the PowerViews at http://impacii.nih.gov/doc/pdf/pv_v30.pdf.

SQL*Plus is an Oracle tool for people with some programming experience and those who know Oracle's version of Structured Query Language (SQL). SQL is a command language that is used for communicating with a database server from any tool or application. SQL*Plus is an Oracle tool that recognizes and submits Oracle's SQL statements to the Oracle database server for execution. It contains its own command language and accepts ad hoc entries of SQL and SQL*Plus commands from the command prompt. This tool (plus80w.exe) can be found in the orawin95\bin folder on your IC's server or wherever your IC has the server side of the IMPAC II application files stored.

Query Builder is an Oracle graphical tool that can be used to retrieve data from either the IMPAC II online or reporting databases. To use this tool effectively, you must know how and where (which table/column) the IMPAC II data is located. With this tool, you "build" your query by choosing your search criteria one column, with its value, at a time. You also select the columns containing the information that you would like the query to return. Query Builder allows you to do some simple formatting for reports, save your query for future use, and/or export the data in a choice of several formats. The exportable formats include Lotus (WKS), Microsoft (SYLK), data interchange format (DIF), comma delimited (CSV), tab delimited (TXT), and fixed file format (PRN).

If none of the applications/systems/methods of retrieving information from IMPAC II and/or CRISP will satisfy your requirements, you can still go to your IC's IT staff and ask them to get it for you.

For additional information about most of the above applications/systems/methods, please contact the IMPAC II helpdesk at 301-294-5788 or helpdesk@od.nih.gov.