Custom reports are a great tool and can be used for many different situations. In this case we are going to look at how to find to those customers who have been benefited in the previous year but have not yet been benefited in this year. This is a great report for marketing and outreach. Lets take a look at how we created it from scratch.

· Under the Analytics tab, select Payment assistance custom reports.  Click on “Show column chooser” to gain access to the field filters for your custom report 


 

  


 

· In the left hand box titled “Available Fields” Check Mark all criteria you want to see. For this specific report you typically will want the following “Fields” : ssn, first name, last name , mailing address, mailing city, mailing zipcode, mailing county, service address, service city, service zipcode, service county, application status, program year, account number, application id and denial reason. ”. Next you should check mark the payment assistance programs and the years you want to compare. 


*Social Security Numbers are primary criteria for most reports*

 


· After check marking the fields, click the “Add>” Button (NOT Add All). You will see the fields you had checked are now moved into a box to the right called “selected fields”. Do the same for those programs you selected and they should also show up now, in their own“selected fields”, on the right. 

 




  

· Now that you have your fields and programs you can click on the “X” in the upper right corner to close the “report criteria” box. Nothing new happens yet until you click the “run report” button. After running the report you should be able to see all of the information you were looking for. Before going any further it would be wise to save this custom report in ServTraq, by clicking on the “save report” button on the ribbon above. This is just in case anything times out, or doesn’t export properly during the next steps. 

  





· Now we will export the list to excel by clicking on the “Export List” button, found on the ribbon above. Then select export to xls. It will take a moment to export, but once it does, immediately enable editing and save the spreadsheet before going any further.

 

· Due to the way it is exported, the numbers may not be recognized as actual numbers. You can convert all fields with numbers or pick and choose. I would recommend at least converting the numbers that are in the SSN column. To do so, double click on the first cell with a SSN in it, then click out of it. Now a green triangle appears in the upper left corner of that first cell. Now Select or highlight the ENTIRE column of SSN(do not simply click on the column header. You must manually highlight them all) return to the top of the spreadsheet and click on the little box that appears to the side of the fist SSN entry. Select “convert to numbers” this may take a minute. “




 

· Now we can click in the most upper left corner of the spread sheet to highlight the entire spreadsheet. Now that its highlighted click on “sort and filter” in the upper right corner of the home tab, then select “custom sort”.


 


· The first “sort by” item should be SSN, choose “smallest to largest” in the “order” section. Next click the “Add Level” button enter last name, “Add Level” again for the first name ( both sorted A to Z). The Final Criteria should be “ Program Year” this should be sorted “Z to A”



 

· Next, select the entire “SSN” column ( by clicking above it, on the column header), then click on “Conditional Formatting” inside the home tab. Select “highlight cell rules” then select “duplicate values.” You can check to make sure that you indeed have highlighted duplicate customers based on ssn. This helps us to see those customers who have applied for both years.  We will be getting rid of these highlighted customers shortly since we only want those who benefited last year but not this year.  

 



 

· Now, click on the uppermost corner again to highlight the entire worksheet, then go to the data tab and click on the “Remove Duplicates”button. A menu will pop up with values for excel to check through for duplicates. Click “unselect all” then find the SSN box, check it and then click ok.

 



 

 

· Next click on the column for Program Year, click on the data tab, click on “Filter”. A downward facing arrow will appear in the Program Year column. Click on it. A menu will come up. Select “sort Z to A” .




 

· Your list will now have all of the 2016 only, applicants on top half of the spread sheet. On the bottom half of the spread sheet it will have the 2015 only, applicants.

 

· Click on the first row of the 2016 program year entries, use the mouse to scroll all the way to the bottom of the 2016 entries. Hold down shift and click the last 2016 entry. You should now have all the 2016’s highlighted, right click and select delete.

 

· We should now be left with just the 2015 contracts that did not have applications turned in for 2016. On the list you have their mailing address and other useful information so you can do your best to reach out to these people and give them the opportunity to be benefited.