Selected Tasks Involving PTS

1. ORNL/LTR-2011/418 Selected Tasks Involving PTS Greg Gruzalski, PTS Administrator Steve Parham, PTS Developer Oak Ridge National Laboratory September 2011

2. Introduction and outline Greg Gruzalski has been the PTS administrator since the system's inception. He is retiring the end of September 2011, and this informal report was written to serve as a recipe for his successor (who, as of this writing, has not been announced) for performing the most common tasks carried out by the PTS administrator. There are two that should be done daily (handling unconfirmed affiliations and unconfirmed journals), one that should be done as needed (modifying or adding an organization's workflow preferences), and a fourth that should be done monthly(generating compliance information). This report is divided into five sections, as indicated below. I. PTS Batch Job Report II. Unconfirmed Affiliations III. Unconfirmed Journals IV. Org Preferences V. Generating ComplianceInformation Selected Tasks Involving PTS

3. I. PTS Batch Job Report Each morning, the PTS batch job sends out an email with subject line PTS Batch Job Report.An example of this reportis shown to the right. The PTS administrator* should read this report every morning and act accordingly. Most of its statements are self-explanatory, but if help is needed to understand them, please contact Steve Parham, the PTS developer.** Two statements in the report require some sort of action; they are circled in red and labeled 1 and 2. Sections II and III summarize how these action items are handled. The statement circled in blue appears to be one that requires action, but the administrator has chosen to assign the same international standard serial number (ISSN) to the two journals shown. * In the rest of the report, "PTS administrator" is shorted to "administrator." ** Similarly, "PTS developer" is shorted to "developer." 1 2 Selected Tasks Involving PTS

4. II. Unconfirmed Affiliations Statements similar to the two circled in red on p. 2 appear in the batch report almost every day. In this section, we show how unconfirmed affiliation records are handled. In the following section, we show how unconfirmed journals are handled. Background Whenever a user wants to add an author to a PTS record, he must first search for that author, unless the author is already listed in the field Coauthors from other publications. We require the search because we want each author-affiliation pair to appear in the system only once.* The user is given an opportunity to add an author-affiliation pair only if it isn’t found in the search. Similarly, PTS requires the user to search for an affiliation before adding it, and the user is given an opportunity to add an affiliation only if it isn’t found in the search. When an affiliation is added, it is added as unconfirmed.An unconfirmed affiliation does not appear on the PTS list of affiliations until the administrator confirms it. We follow this procedure because we want to minimize duplicates. For example, we wouldn't want “Los Alamos National Laboratory,” “Los Alamos Nat Lab,” “Los Alamos Nat'l Lab,” and “Los Alamos National Lab” all included as separate affiliations, and so, before confirming an affiliation, the administrator checks to see whether it is already in the system. We now show how the administrator proceeds. * Although we don't want duplicate author-affiliation pairs in the system, the same person may appear more than once. For example, suppose that John Doe at the University of Tennessee and John Doe at Los Alamos National Laboratory are the same person (say John was in graduate school at UT and later went to LANL). In such a situation, the same person could appear in two separate author-affiliation pairs. Selected Tasks Involving PTS

5. II. Unconfirmed Affiliations (cont.) To create a list of unconfirmed affiliations, the administrator uses the affiliation tool. He begins this process by clicking the link given in the batch job report, which takes him to this URL: http://portal.ornl.gov/sites/apps/PTS/Lists/Affiliations/AllItems.aspx The administrator then chooses the option Show Unconfirmed(see p. 6) and clicks Find. A list of unconfirmed affiliations then appears, such as the one shown on p. 7. The first unconfirmed affiliation listed is ArcelorMittal. Clicking on the ArcelorMittal link took the administrator to the webpage shown on p. 8. To find out whether this affiliation was in the system, the administrator chose (click to select replacement affiliation), which caused a pop-up window to appear. The pop-up window was used to search for ArcelorMittal. When this was done, the message shown on p. 9 appeared, which told the administrator that ArcelorMittal was not on the list of confirmed affiliations. The administrator therefore clickedCancel, chose the Confirm check box(see p. 10), and hit OK, which brought him to the list of unconfirmed affiliations shown on p. 11. A comparison of the lists shown on pp. 7 and 11 indicates that this procedure removed the first listing of ArcelorMittal from the list of unconfirmed affiliations. The procedure changed the first listing of ArcelorMittal from unconfirmed to confirmed. Clicking on ArcelorMittal again (it's still the first unconfirmed affiliation listed) and going through the same procedure described above took the administrator to the webpage shown on p. 12 (which should be compared with what is shown on p. 9). The administrator then used the affiliation tool to replace the unconfirmed listing of ArcelorMittal with its confirmed listing. This was done by clicking on ArcelorMittal (see p. 13), clicking OK, clicking OK again, and clicking OK a third time (see p. 14). The administrator was then taken to the list of unconfirmed affiliations shown on p. 15, which no longer includes ArcelorMittal. Selected Tasks Involving PTS

6. II. Unconfirmed Affiliations (cont.) The string of RPIs listed on p. 15 were each handled as the second listing of ArcelorMittal was handled: Click the RPI link, choose (click to select replacement affiliation), search for Rensselaer Polytechnic Institute, select it, and click OK three times. Note that in this case the search was made for "Rensselaer Polytechnic Institute" and not for "RPI." If the administrator didn't know what RPI stood for, he would have looked it up on the web. The Carnegie Institution of Washington, PIIM, NREL, and Penn State were similarly replaced by affiliations already in the system. Every once in a while, the administrator will see entries showing ORNL. Two are shown in the example on p. 15: "Environmental Science Division, Oak ridge national laboratory" and "ORNL." If a person truly is at ORNL, he/she is listed in SAP and, consequently, he/she is in the PTS system. Hence, there is no reason to “add” such a person to the system. Nonetheless, people will try to do so. To handle ORNL unconfirmed affiliations, the administrator first finds the corresponding ORNL author and then replaces him/her with the author's employee listing. Finding the corresponding ORNL author can be a little tricky. You can’t just search for records that have authors with the affiliation “ORNL,” for if you did, you would find tens of thousands of records. The administrator recommends this three-step process: First, use the affiliation tool to change the added affiliation “ORNL” to something such as “ORNL123.” Second, use the main PTS program to search for records that have the author affiliation “ORNL123” (only one will be found). Third, use the main PTS program to edit the record and replace the added author with the corresponding ORNL employee listing. Finally, if the added "ORNL" author is not an ORNL employee, the administrator will not be able to identify an employee listing; in this situation, he simply replaces the unconfirmed version of ORNL with the confirmed one. The administrator should continue to go through the list of unconfirmed affiliations until finished (see p. 16). The procedure described above may appear long, but it actually goes very fast. Managing the unconfirmed affiliations with the affiliation tool takes only a few minutes a day. Text continues on p. 17. Selected Tasks Involving PTS

7. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

8. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

9. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

10. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

11. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

12. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

13. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

14. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

15. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

16. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

17. II. Unconfirmed Affiliations (cont.) Selected Tasks Involving PTS

18. III. Unconfirmed Journals Unconfirmed journals are handled very similarly to how unconfirmed affiliations are handled. The main differences are that the administrator • Uses the journal tool instead of the affiliation tool, • Must confirm that the journal is indeed a journal (i.e., that it has an ISSN), • Must confirm the ISSN, and • Must confirm the abbreviation. Currently, PTS abbreviations are stored without the period punctuation marks. For example, the abbreviationfor Physical Review Letters isPhys Rev Lett, not Phys. Rev. Lett. To create a list of unconfirmed journals, the administrator • Clicks the link in the batch job report, which takes him to this URL:http://portal.ornl.gov/sites/apps/PTS/Lists/JournalAdmin/AllItems.aspx, and • Chooses the option Show Unconfirmed and clicks Find. The resulting list is shown on p. 19. The administrator was concerned with only the four journals circled in red. The last three "journals" are bogus. However, they were entered into the system long ago, and it isn’t worth the hassle to deal with them properly. Once he had the list of unconfirmed journals, the administrator clicked on the first one listed, which took him to the webpage shown on p. 20. He then chose (click to select replacement journal) to find out whether the journal was in the PTS system. If he had found it, he would have replaced the unconfirmed journal with the confirmed one already in the system. In this case, however, he didn't find it. Selected Tasks Involving PTS

19. III. Unconfirmed Journals The next step (#2 on p. 17) was to verify that the added journal was indeed a journal. The administrator usually uses Journal Seek to perform this task. Journal Seek can be found at this URL: http://journalseek.net/ (see p. 21). At this site, searches can be made either by journal name or by ISSN. In the example shown on p. 21, the administrator searched on journal name, and Journal Seek found two journals (p. 22). The administrator clicked on the link corresponding to the journal he wanted and he was taken to a page that had the information he needed (p. 23). To avoid errors, he copied the journal abbreviation and ISSN from the webpage and pasted them into the journal tool. Note: If more than one ISSN is given (as in the example shown on p. 23), only ISSN (printed) is entered into the journal tool. As with the unconfirmed affiliations, the administrator should continue to go through the list of unconfirmed journals until finished. Note that, at this point, the list still contains the names of the three bogus "journals" (see p. 24). Confirming journals can be far more time consuming than confirming affiliations. Journal Seek doesn't include all journals, and sometimes the administrator needs to be quite resourceful to find the required information. Text continues on p.25. Selected Tasks Involving PTS

20. III. Unconfirmed Journals (cont.) Selected Tasks Involving PTS

21. III. Unconfirmed Journals (cont.) Selected Tasks Involving PTS

22. III. Unconfirmed Journals (cont.) Selected Tasks Involving PTS

23. III. Unconfirmed Journals (cont.) Selected Tasks Involving PTS

24. III. Unconfirmed Journals (cont.) Selected Tasks Involving PTS

25. III. Unconfirmed Journals (cont.) Selected Tasks Involving PTS

26. III. Unconfirmed Journals (cont.) As stated on p. 18, when a journal is added to PTS, only one ISSN is assigned to it. If the journal has more than one ISSN (say it has one for its printed version and another for its online version), only the printed version is stored in PTS. PtsWosCompare is the program that identifies which ORNL papers in the Web of Science (WoS) are in PTS. It doesn't rely on journal names, but rather it uses ISSNs. Consequently, the developer must modify PtsWosCompare to account for the procedure described in the preceding paragraph (where we assign only one ISSN to a journal. This happens in two steps. First, the administrator provides the developer with the needed information, which he does by email. An example of such an email is shown to the right. Second,the developer modifies PtsWosCompare so that both the printed and the electronic versions of a journal are captured. Selected Tasks Involving PTS

27. IV. Org Preferences Each organization (as defined within SAP) may have its own workflow. For journal articles, the Biosciences Division uses the workflow indicated on p. 30 while the Reactor and Nuclear Systems Division uses the workflow shown on p. 31. PTS must be told what the various workflows are, and the developer and administrator share this responsibility. The administrator finds out what the division wants and converts that into database commands. The developer then copies, pastes, and runs the commands. For example, to set up the workflows for the Reactor and Nuclear Systems Division, the administrator would send the developer an email that includes the database commands indicated on p. 32. The first command deletes all of the division's preferences; the next group of commands sets up the workflow (indicated on p. 31) for journal articles; and the notation at the end indicates that similar groups of commands must be included for all 15 communications types. The database commands are generated in Excel. The Excel file comprises 11 columns. The first 10 are exported from Sql Server by the developer: OrganizationCd, Organization_Desc, CommunicationTypeCd, ShortDesc, WorkFlowStep, FieldLabel, RequiredFlag, NoStopsWorkFlowFlag, DefaultToBadge, and FinalApproverFlag. These columns include information about the workflows for all of the responsible organizations. A description of each field is given on p. 27. When an organization's workflow needs to be changed or added, the administrator edits the first 11 columns of the Excel file. The database commands are automatically generated by the Excel commands entered into column 11. By following this procedure, the administrator needs to make changes to the first 10 columns only, which are easy to understand and quite intuitive. There are only two Excel commands, and these are shown on p. 28. Selected Tasks Involving PTS

28. IV. Org Preferences (cont.) Descriptions of fields in the Org Preferences Excel file: OrganizationCd: The Organization ID of an organization that has preferences. Such organizations are labeled "Responsible Organization" in the PTS application. Organization_Desc: The description of the organization indicated by the OrganizationCd. For example, OrganizationCd 50000012 has the description "Physics Division." CommunicationTypeCd: The code for a publication type. For example, "JA", which stands for journal article. ShortDesc: The short description for a publication type. For example, "Journal Article." WorkFlowStep: A step in the workflow that corresponds to one or more reviews. Multiple reviews in one workflow step may be performed in parallel. Workflow steps must be completed sequentially. FieldLabel: The name of the review step displayed in PTS. For example, "Export Control Check." RequiredFlag:Indicates whether the review step is required. NoStopsWorkFlowFlag: Indicates whether a rejection by a reviewer stops the workflow. DefaultToBadge: A badge number that is populated automatically. This can be overridden by the user. FinalApproverFlag: Indicates whether the review step isthe official division-level approval. FinalApproverFlag is "Y" for the last step of all workflows except those associated with LDRD reports. For LDRD reports, the LDRD manager reviews the report after it is approved at the division level. Selected Tasks Involving PTS

29. IV. Org Preferences (cont.) In cell K1, the administrator enters ="delete from OrganizationPreference where organizationCd = "&A2&";" In cell K2, the administrator enters =IF(COUNTBLANK(A2:A2)=0,"insert into OrganizationPreference values ('"&A2&"', '"&C2&"', '"&E2&"', '"&F2&"', '"&G2&"', '"&H2&"', "&I2&", '"&J2&"');","" In cell K3, the administrator enters =IF(COUNTBLANK(A3:A3)=0,"insert into OrganizationPreference values ('"&A3&"', '"&C3&"', '"&E3&"', '"&F3&"', '"&G3&"', '"&H3&"', "&I3&", '"&J3&"');","") And so on. Here K1 represents the cell in column K that is in the row preceding the first row containing information about the workflows for the organization of interest. Clearly, some understanding of Excel is useful to execute these commands successfully. An example of what one sees in cells K1, K2, K3, etc. is shown on p. 29. Selected Tasks Involving PTS

30. IV. Org Preferences (cont.) In cell K1, one sees delete from OrganizationPreference where organizationCd = 50000004; In cell K2, one sees insert into OrganizationPreference values ('50000004', 'BA', '1', 'Supervisor', 'Y', 'Y', NULL, 'N'); In cell K3, one sees insert into OrganizationPreference values ('50000004', 'BA', '2', 'Technical Reviewer', 'Y', 'N', NULL, 'N'); And so on. An example of an org-preferences Excel file can be found here: https://portal.ornl.gov/sites/apps/pts/PtsDocs/Pub%2032912_Org_Prefs.xlsx The administrator copies the database commands from column K of the Excel file and pastes them into an email that he sends to the developer. The text continues on p. 33. Selected Tasks Involving PTS

31. Supervisor Supervisor Technical Review Technical Review Technical Review Division Approver Here and on p. 31, the red boxes indicate required reviewers and the blue boxes indicate optional ones. Not indicated are which reviewers have the authority to stop the workflow. The following commands would set up the above workflow (for journals articles in the Biosciences Division). insert into OrganizationPreference values ('50001379', 'JA', '1', 'Supervisor', 'Y', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50001379', 'JA', '2', 'Technical Reviewer', 'Y', 'N', NULL, 'N'); insert into OrganizationPreference values ('50001379', 'JA', '2', 'Technical Reviewer', 'N', 'N', NULL, 'N'); insert into OrganizationPreference values ('50001379', 'JA', '2', 'Technical Reviewer', 'N', 'N', NULL, 'N'); insert into OrganizationPreference values ('50001379', 'JA', '3', 'Supervisor', 'Y', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50001379', 'JA', '4', 'Division Approver', 'Y', 'Y', NULL, 'Y'); Selected Tasks Involving PTS

32. Supervisor Patent Review Export Control Check Technical Information Officer Technical Editor Technical Review Technical Review Technical Review Technical Review Technical Review Administrative Check Supervisor Program Manager Division Approver Selected Tasks Involving PTS

33. IV. Org Preferences (cont.) delete from OrganizationPreference where organizationCd = 50159781; insert into OrganizationPreference values ('50159781', 'JA', '1', 'Supervisor', 'Y', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '2', 'Patent Review', 'N', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '2', 'Export Control Check', 'N', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '2', 'Technical Information Officer', 'N', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '3', 'Technical Reviewer', 'Y', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '3', 'Technical Reviewer', 'Y', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '3', 'Technical Reviewer', 'N', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '3', 'Technical Reviewer', 'N', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '3', 'Technical Reviewer', 'N', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '4', 'Technical Editor', 'Y', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '5', 'Administrative Check', 'Y', 'N', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '6', 'Supervisor', 'Y', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '7', 'Program Manager', 'Y', 'Y', NULL, 'N'); insert into OrganizationPreference values ('50159781', 'JA', '8', 'Division Approver', 'Y', 'Y', NULL, 'Y'); . . . For more information, please contact Steve Parham. (similar commands for each of the other 14 communication types) Selected Tasks Involving PTS

34. V. Generating Compliance Information The process of generating the compliance information begins with the administrator* creating a list of ORNL publications found in the Web of Science (WoS). This list is generating from the WoS advanced search indicated on p. 38. The search command is reproduced here: ((OG="Oak Ridge Natl Lab" OR SG="Oak Ridge Natl Lab" OR OG="Oakridge Natl Labs" OR SG="Oakridge Natl Labs" OR OG=ORNL or SG=ORNL) and (py=2008-2011)) OR (((OG="Spallat Neutron Source" or SG="Spallat Neutron Source" OR OG=SNS OR SG=SNS OR OG=CNMS OR SG=CNMS OR OG=nanoscience or SG=nanoscience OR OG=nanophase or SG=nanophase) AND AD=TN) and (py=2008-2011 )) For the search performed on September 19, 2011 (p. 38), the webpage shown on p. 39 appeared. Note that the search found 6,061 ORNL publications. By clicking the link 6,061, the administrator was taken to the page shown on p. 40. The lower portion of this page is shown on p. 41, where the parameters used to download the records can be seen. Only 500 records may be downloaded at one time. Therefore, for the example we are following, 13 downloads were required to obtain the 6,061 records. The downloaded files are tab-delimited text files. They were combined using BBEdit, and the resulting file was read using Excel. It is important to make sure that Excel reads the data as text. If this isn't done, Excel will do strange things such as change 1-2 to Jan 2 or 0009-1964 to Sep-64. To read data into Excel as text, follow the instructions on the top of the next page (p. 34). * This task doesn't require any special role, but the PTS administrator has been the person who has done this. Selected Tasks Involving PTS

35. V. Generating Compliance Information (cont.) • Open Excel, choose Open File, and select the text file that includes the data. • When the Text Import Wizard appears, specify that the file is "Delimited." The "Start import at row" should not be changed from 1. Click Next. • Specify that Tab is the delimiter and then hit Next. • In Data Preview, note that each column label is "General." Change these labels to "Text" by selecting all of the columns and then selecting the Text radio button. Click Finish. In addition to what is outlined above, three of the WoS records had to be "fixed" by hand. WOS:000276480600001 spilled into extra cells, WOS:000267868500001 also spilled into extra cells, and C1 was blank for WOS:000265513900014. The data read into Excel need to be monitored to find errors such as these. The first two errors were found by using the command =COUNTIF(AZ2:AZ7000,"*wos*") The result of this command should be the number of records found in the WoS search. If that's not the case, something is wrong. Similarly, COUNTBLANK(B2:B6062), COUNTBLANK(F2:F6062) ,and COUNTBLANK(U2:U6062) should all be zero. By using commands such as these, the administrator discovered that C1 was blank for the record identified above. Another useful command is =SUMPRODUCT( (AZ2:AZ7000 <>"")/COUNTIF(AZ2:AZ7000, AZ2:AZ7000&"") ) which gives the number of unique values from AZ2 to AZ6096. An example of the resulting Excel file can be found at the following URL. It contains the 6,061 records identified in the above-described searchas well as the above-mentioned fixes. https://portal.ornl.gov/sites/apps/pts/PtsDocs/Pub%2032912_WosData.xls Selected Tasks Involving PTS

36. V. Generating Compliance Information (cont.) The administrator sends the Excel file containing the WoS data to the developer, who imports the data into Sql Server and executes PtsWosCompare. The gist of what is done by PtsWosCompare and the developer is summarized on pp. 42 and 43. Once PtsWosCompare has been run, the developer exports selected results to an Excel file and sends it to the administrator. In this report, we will refer to that file as "OutputFromPtsWosCompare." (The file this report uses for an example is WoS_2011_InSep19_OutSep19.xlsx. The filename tells us that the WoS search was made on 9/19/2011 and that PtsWosCompare was run on 9/19/2011.) The administrator uses Pivot Tables to analyze the data in OutputFromPtsWosCompare; two examples of Pivot Tables are shown on pp. 44 and 45. The data shown on p. 44 (both the compliance numbers and the total number of ORNL publications) are being tracked by the Office of Integrated Performance Management. We define compliance as follows: Compliance = NPTS / NWoS where NWoS is the number of ORNL papers identified in WoS and NPTS is the number of those NWoS papers found in PTS. For example, the data on p. 44 indicate that we identified 1489 ORNL papers in the WoS that were published in 2008 (NWoS). Of those 1489 papers, 1018 were found in PTS (NPTS). Therefore, for the 2008 publications, the compliance as of 9/19/2011 was NPTS / NWoS = 1018/1489 ≅ 68.4%. Ideally, every one of the 1489 papers should have been in PTS, which would have resulted in a compliance of 100%. On pp. 46 and 47, data from p. 44 are compared with similar data going back to November 2010. These summary data are stored in the file found at this URL: https://portal.ornl.gov/sites/apps/pts/PtsDocs/Pub%2032912_Summary.xlsx Selected Tasks Involving PTS

37. V. Generating Compliance Information (cont.) In addition to matching WoS and PTS records, PtsWosCompare attempts to assign an ORNL organization to every ORNL record identified in the WoS. These data are included in OutputFromPtsWosCompare and are summarized in the Pivot Table shown on p. 45. This Pivot Table was used by the administrator to generate, for each division, a spreadsheet listing that division's WoS papers that were not found in PTS. The division spreadsheets were generated using these instructions: • Copy the spreadsheet from the Excel file provided by the developer (e.g., from OutputFromPtsWosCompare). Delete unwanted rows by keeping only those with WosDocTypeInclude = "Included," SortOrderDescription = "WoS record not found in PTS," DivInRespOrgList = "Yes," and WosPubYear = "2008," "2009," "2010," or "2011." We name the spreadsheet "For Division Spreadsheets" and include it in OutputFromPtsWosCompare. • In the spreadsheet For Division Spreadsheets, include only the fields to be included in the division spreadsheets. Presently, we include these: WosTitle, WosVolume, WosIssue, WosBegPage, WosArticleNumber, WosPublicationName, Wos_PT_BJS, WosConfTitle, WosConfDate, WosConfLoc, WosAuthors, WosAuthorsAddress, WosCitationCount, WosSubjectCategory, WosDocType, WoSPubYear, DivisionDesc, and DirectorateDesc. • Create a new Excel file, which here we refer to as "Template," and use the above field names as column headings. Format the column widths as desired. Selected Tasks Involving PTS

38. V. Generating Compliance Information (cont.) • Filter the spreadsheet For Division Spreadsheets on DivisionDesc and select a division. • Copy all of the cells (except those in the first row) and paste them into Template. Format the row height to whatever it is (say, 0.21") and format the cells to "wrap text." • Use "save as" to save the file, naming it whatever the DivisionDesc is. For example:Biosciences Division.xlsx • Reopen Template and repeat steps 4–6 until finished. For the example we have been following, the above procedure produces 27 Excel files (corresponding to the 27 organizations listed on p. 45). The administrator put these files into a folder, compressed the folder, and emailed the compressed folder to the Office of Integrated Performance Management. An example of a division spreadsheet can be found here: https://portal.ornl.gov/sites/apps/pts/PtsDocs/Pub%2032912_PhysicsDivisionSpreadsheet.xlsx An example of the OutputFromPtsWosCompare file, including the two Pivot Tables and the spreadsheet For Division Spreadsheets, can be found here: https://portal.ornl.gov/sites/apps/pts/PtsDocs/Pub%2032912_OutputFromPtsWosCompareAnalyzed.xlsx Selected Tasks Involving PTS

39. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

40. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

41. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

42. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

43. V. Generating Compliance Information (cont.) Summary of what is done by PtsWosCompare and the developer The administer sends the developer the spreadsheet of WoS data (see pp. 33–35), and the developer imports the data into Sql Server. The PtsWosCompare program is then executed to compare the WoS data to that in PTS. The program makes a series of passes through the WoS data. On the first pass, a WoS record and a PTS record are considered a match only if all of the following attributes match: All Authors listed Journal ISSN Publication Year Volume Number Beginning Page Issue Number Title (first 20 characters only) On subsequent passes, fewer and fewer attributes are required to match. For example, on the 10th pass, the program checks for the following five attributes: First ORNL author in WoS Journal ISSN Publication Year Beginning Page Title (first 20 characters only) Selected Tasks Involving PTS

44. V. Generating Compliance Information (cont.) Summary of what is done by PtsWosCompare and the developer (cont.) Each subsequent pass becomes less accurate as the program requires fewer matches. At one time we were making a large number of passes. By analyzing the output, we determined that only the first 42 passes had a high probability of accurately matching records between the two data sources. Records matched in these 42 passes are considered "Compliant" and an accurate match. Once the program has finished making passes to find matches, it attempts to assign an ORNL organization to the records that were not matched. There are four different methods used to relate the WoS data to an ORNL organization. After the program has run, the developer exports a spreadsheet from Sql Server and sends it to the administrator. The file created on 9/19/2011 was called WoS_2011_InSep19_OutSep19.xlsx The notation means that we downloaded the data from the WoS on 9/19/2011 ("InSep19") and that the results were generated by PtsWosCompare on 9/19/2011 ("OutSep19"). Note: There are detailed instructions for both the import and the export as part of the PtsWosCompareproject that is stored with the program code in Team Foundation Server. Selected Tasks Involving PTS

45. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

46. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

47. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS

48. V. Generating Compliance Information (cont.) Selected Tasks Involving PTS