Home /VBA Driven Customized Salary Report

VBA Driven Customized Salary Report

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.

About the author

admin

Othniel Denis is currently Principal of Excellent Ones Consulting LLC. He brings with him a background in finance, training, and information management systems. Prior to opening his own firm, Othniel spent the last 13 years in finance as an Analyst at organizations like New York University, Nassau County Government and Brookhaven National Laboratory to name a few...

Read More