As year end quickly approaches, I wanted to share with my followers a very helpful #GPLifeHack that turns your year end wage file data into a Smartlist for easy auditing and review!
There is an ‘edit’ report for W2’s but it’s in long form and difficult to review – plus it is not easily exported to Excel.  What exports easily to Excel?  SMARTLISTS!!! YEAH THEY DO!
Let’s make this year-end process easier by creating a Smartlist for year end W2 data! 

If you are not familiar with how the year end wage file works – it is populated when the Payroll year end routine is run.  (Found at: HR/Payroll/Routines/Year-End Closing)
This routine can be run at any time and MANY times a year, providing you with data to spot check early on, rather than waiting until the crunch time at year end.  With that being said, be sure to run the year end routine one final time after your last payroll so that the year end wage file has ALL your payroll data for the year.
IMPORTANT:  It is a best practice to make the changes identified by these routine reviews using payroll functionality – adjustments etc – rather than the edit W2 window.  Any changes made to the ‘edit W2’ window get replaced every time you run the year end wage file for the current year.  So, using the Edit W2 window is only recommended if your will not rerun your year end wage file for the year.
So, let’s build a smartlist so we can view this data and get ready for our year end – we will build the smartlist based on a SQL View – no worries, I have a link here for the view so you don’t have to write it yourself.
Click here for the SQL View:  Year_End_Wage_Data_Payroll – there are some other views similar to this out there – I have mine set to include address information as well as W2 ‘amount’ fields.
Run this against the company database using SSMS (Management Studio).   Check with your IT department or Database Administrator for assistance.
Next, within Smartlist, highlight the Payroll folder and click ‘New’.  This will launch the Smartlist Designer – there many folks out there who have Smartlist Builder by E-One – the process is slightly different for the ‘Builder’ tool but if you own builder, you probably already know how to do this – if not – message me and I can gladly walk you though that.

Once Smartlist Designer opens – give your new smartlist a name – I chose ‘Year_End_Wage_Data’ – make sure your Product and series are populated correctly.

We will need to select the View you just created on the company database – scroll down to the bottom of the database view scrolling window (left side) until you see ‘Views’ – expand that and select ‘Company’ – this will expand and give a list of all views on the SQL server for the company you are logged in to.  Be patient – this might take a few minutes to load up for you.

Once it does, find the view we just created ‘Year_End_Wage_Data_Payroll’ and mark the checkbox.  This will put all the fields over into the selected fields window.
That’s it!  You can do more if you want – drop fields, etc, but for now, let’s just hit OK in the top left of the window.

This will give you a new Smartlist option under the Payroll Series – and just like that, you now have a Smartlist that you can use year over year!  This exports to #Excel easily and you can apply ‘search’ criteria to filter out for the year you are looking for (In Smartlist OR Excel)!

As I mentioned earlier, this will ONLY show data captured by the year end routine – so if you haven’t run it once for the year, it will show no records.
Thanks for Reading!
Shawn