In order to tackle this problem I needed a way to produce customized reports quickly. I need close to 100 tailored reports by person. This would take an enormous amount of time and be error riddled if done manually. The steps I took were as follows:
Format Data
The first step in this process was to format the data in a way that would yield the desired results. I extracted salary data from a proprietary system (Note: the names and identifying information has been changed to protect the innocent) and formatted it in a transactional way.
Build Pivot Table
The next step was to build a master Pivot Template. This pivot table would function as the interface for the customized reports. When a different manager is selected in the filter, the Pivot would update. Below is the sample format. I built a pivot table and formatted it in a way that would be a bit more appealing to the eyes. The filters in cell B4 would drive the changes in the Pivot Table. In this scenario, I am pulling the project manager name into the title of the entire table using a simple link formula (i.e. = B4). When the project manager is changed the title will update as well.
In the view below I’ve hidden rows 4 – 6. They are not necessary to be viewed by the end user of the report.
Write Code
The last stage of this project was to figure out a way to change the Pivot Table programmatically with minimal human intervention. I needed a code to do two things (1) move the project manager filter (2) save a PDF copy of that report for distribution later. The following code does just that.
Sub Salary_By_Manager()
x = 0 ‘Set the variable x to 0
Do Until x = 100 ‘ Repeat the steps that follow until x equals 100
Sheets(“Managers”).Select ‘Select Managers Worksheet
Range(“D3”).Offset(x).Select ‘start at cell D3 collect Manager name, Step 1
Manager = ActiveCell.Text ‘Get Manager Name
Sheets(“Pivot Table”).Select ‘ Select the sheet titled Pivot Table
‘ The Code below updates the filter based on the contents of the variable Manager
ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Project Manager”). _
ClearAllFilters
ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Project Manager”). _
CurrentPage = Manager
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=”T:\” & Manager & ” Dept Salaries.pdf” ‘ Save as a PDF document in T drive
x = x + 1 ‘ counts number of times the process is run
Loop ‘ Goes the beginning of the Do until statement until condition met
End Sub ‘ End the program
Review Reports
It is always a good idea to review the final output. There may be something erroneous in the logic of the Pivot Table or structure of the underlying data. In this particular project I noticed that a few of people were paid by multiple managers. This would cause there payments to be listed as splits under two or more managers. I was ok with this representation however; this would need to be noted when shared with the manager.
In addition, the adjustments column represents changes for the preceding months. This is an aggregate number that could apply for any month between September and May. This is a potential issue. The underlying structure of the data lumps the adjustments into the time period they were made rather than the one it applies to. This may lead to confusion. It would be very difficult to manually going through each payee to break-out the adjustments. I choose to articulate this when shared with the manager and watch the results in real-time. In addition adjustments to salary information occur on practically a daily basis. The underlying data would need to be refreshed constantly before distributi
Edit Code and/or Data
I edited the underlying data to ensure all adjustments appear in the adjustments column. When the original data was received it was grouped by the month.
Final Reports
The final report was piloted to the community with positive results. General reports are helpful for overall direction of a group. However, the greater the customization the more value it has to the user.
