|
by Charles Clendenen, ISP (Consultant@FSOCS.com)
The JPAS Personnel report no longer  produces a nicely formatted Adobe .pdf document, because the report generation for large organizations was taking hours to complete (or so the story goes). The report generator now produces a personnel report in .csv (comma separated values) format. This is excellent in many ways, but it is also an annoyance if you just want a simple report to view. Formatting the downloaded report can take several minutes, because the report includes many columns that you do not want (and the column headers are often too wide). Throwing all of these away takes time. What to do?
Let a macro do the heavy lifting for you. Macros are like small computer programs that perform repetitive tasks for you. They can be very powerful, but the upside is that you don't have to do any programming to create a macro. In fact, you do not have to be very technically savvy to create and use a macro. Modern software has simplified this process for you. Provided below are instructions for cleaning up the Personnel report in Microsoft Excel. (I have tried creating the same macro in OpenOffice Calc, but it does not work for me.) Creating this macro takes only a few seconds more than cleaning up the report one time, but the macro can be used over and over.
First, log into JPAS and generate the Personnel_by_Eligibility_and_Access.csv report and save it to your hard drive. Then, open up Excel and follow the instructions below. The instructions assume that you have already run a report at some time and cleaned it up before. If you have not, you might want to step through that process a time or two before recording the macro.
Excel Instructions
These instructions are geared towards Excel 2007, but they should work for just about any version.
First, locate the Macro Recording function in your software. In Excel 2007 it is found under the Developer tab. No Developer tab on your Excel? Then enable it. Click the Office Button at upper left. In the dialog box that opens up click on Excel Options (just to the left of Exit Excel at bottom right). Check the box show Developer tab in the Ribbon, and then click OK.
Open the Personnel_by_Eligiblity_and_Access.csv file you saved, and then click the Developer tab. At upper left you will see a button labeled Record Macro. Click it and name your macro PersClean (or Ralph, or whatever you like).
Excel will now record every click and key stroke. Click the Home tab, and start cleaning up your worksheet. For example, I do not want Column A, SMO Relationship Indicator. To get rid of it, click the A at the top to highlight the entire column, and then click the Delete button at upper right. Poof! That column is gone. The next column is titled "Organization UIC". That is too wide. Shorten it to "Org" or "UIC". I change " Formatted SSN" and "Formatted Name" to "SSN" and "Name" respectively. As a column name, 'Eligibility" is too wide, so I change it to "Elig". "Eligibility Date" becomes "Elig Date". The dates in this column are also in an unusable format with a time stamp, so I click the F at the top to highlight the entire column, right-click inside the highlighted column and select Format Cells... from the context-sensitive menu that pops up. I select Date from the list under the Numbers tab, and I choose the 3/14/01 format from the type at right to get the shortest version of the date. Repeat your re-formatting effort for all columns. Last, highlight all remaining columns and click the Format button to the right of Delete at upper right , and choose AutoFit Column Width. Stop recording the Macro.
Now, carefully (!) highlight the sheet and delete all contents. You should have a blank spreadsheet. Save As (not just Save!) PersClean.xls or PersClean.xlsx as a macro-enabled Excel workbook. You now have a spreadsheet with your very own macro embedded in it. I would recommend that you now close Excel. Make a backup copy of your macro when you have it just the way you want it.
The next time you get ready to download your report, open up Excel first. IMPORTANT: Macros are very powerful, and bad people write macros to cause damage, so Excel will issue a Security Warning every time you open a spreadsheet with an embedded macro. Your spreadsheet is, of course, harmless. You created it yourself, so tell Excel to enable this content. With Excel open and your macro enabled, you can download your Personnel report, run your macro, and in 1-2 seconds (and no additional effort), you will have a formatted report.
Charles Clendenen, ISP, is a member of the NCMS Lone Star Chapter and facilitates our Austin Area FSO Brown Bags.
Comments or feedback on this article? Email ajsconsulting@earthlink.net. | 
If you have always wanted to share your expertise on a security related topic and believe you can concisely state your points in a way that is understandable and enlightening to both newbie and experienced FSOs, please forward me your topic and a brief description of how you wish to address it and your article may be included in my next newsletter.
The importance of working with your facility's Contracts and Human Resources departments must be understood by both you and management. How do you train management, Contracts, and Human Resources to realize it is also in their best interest to work with you and your Security department?
