As part of space management and policy enforcement, usage data from the electronic locks is gathered and analyzed. If you haven't already done so, begin by Gathering Lock Audit Data. You After the Electronic Locks Coordinator has Gathered lock audit data, you will take the data and extract it to create a Pivot Table using Microsoft Excel. How to do this is outlined below.
...
- Open a new Microsoft Excel sheet
- Click on the “Data” tab
- Select “New Query” then “From File” and “From Folder”. (Note: This image does not reflect the current file path.)
- Click Browse.
- Select This PC < Shared Network Space LEAD < Shared <Library Space<Shared<Library Spaces Planning & Mgmt< Rooms < Mgmt<Rooms< Research Room Audits < Audits< XXXX Audit Data< Most Recent file
- Click OK, then OK again.
- Another box will appear – just click Load at the bottom.
...
- In the pop-up window, select "Table 0" for the door numbers and date/times accessed to appear. Click OK.
- Right click on the Date column. In the drop down menu, select Transform and click “Date Only”. This will remove the time stamps, which will potentially skew the data counts.
- In the Event/Name column, click on the down arrow for the menu.
- In the pop-up window, select “Load More” at the bottom of the window to see all the event name options.
- You will now have a much longer list. Individually select only the research rooms. We do not analyze data from any other entry. Then click "OK".
- Delete the Function and Source Name columns by right clicking and selecting “Remove”. The only columns you need to keep are Door, Date, and Event/Name.
- Once you are finished making your edits, click on Close and Load in the upper left hand corner. This will take some time to load so be patient.
- In the upper tool bar, under “Table Tools” click Design. Highlight the three remaining columns and click "Remove Duplicates" under "Table Tools".
- There will be a pop-up window. Make sure all three columns are selected (Door, Date, Event/Name). Also leave "My data has headers" selected. Click OK. Excel will tell you how many duplicates were removed. Click OK again.
- Click the downward arrow in the Event/Name column and one last time check to make sure only Grad Commons research rooms are selected. Unselect anything that is not a Grad Commons research room. Click OK.
...
Your Pivot table should look roughly like this:
You can collapse or expand rows by clicking on the + and - boxes next to the labels. If you collapse a field (say January for example), it will collapse January for ALL the rows (but not February). This can be helpful when you want to look at specific data or reduce the number of rows you need to scroll through.
Save your work as "Year-Month-Date Usage" in the Audit Analysis Reports Folder.