The following is a not all-encompassing list of the work I have completed to date.
All reports developed are dynamic with parameter options.
Words within parentheses () highlight skills used.
Key Terms (Batch Scripting, EXCEL, VBA, T-SQL, SSMS, SSRS, Domain Knowledge, Business Logic, PowerPivot, Stored Procedures, UX, UI, Graphing, Sharepoint, DAX, Access, SQL Server Agent Jobs, ETL, Reporting, Validation, Automation, User Defined Function, Visual Studio, Version Control, Database Development, Team Foundation Server)
Company: One of the Big Four accountancy firms
P1. (Batch Scripting)
The development and introduction of a file merging/appending batch script which reduced the workload of file cleaning.
P2. (EXCEL, VBA)
The optimisation and automation of the data cleaning process in excel utilising numerous functions created with vba.
P3. (EXCEL, VBA)
The development and introduction of a record linkage function in excel for file comparison utilising vba.
P4. (T-SQL, SSMS)
SQL Server 2008 database developed to store and deduplicate security data e.g. common stock.
P5. (T-SQL, Domain Knowledge)
Accountancy formulas translated into T-SQL to automate the auditing process within a database.
P6. (EXCEL, VBA, Business Logic, Domain Knowledge)
Backwards engineered (Computer-aided audit tools) excel add-ins copying UI and functions which enabled the following:
- Data stratification.
- Sample extractions.
- Missing sequence identification.
- Statistical analysis.
P7. (EXCEL, VBA, Domain Knowledge, Business Logic)
Excel function developed to automate the creation of accounts by allowing an audit senior to tag the trial balance of a company. The summation of these accounts would then automatically create the profit and loss account. This function represented a massive saving on time while reducing labour requirements and improving quality and accuracy.
Company: Consulting work for one of the largest blue chip software companies in the world
P1. (Excel, T-SQL, Stored Procedures, DAX, PowerPivot)
Excel report created showing a quarter on quarter comparison of the top 25 business customers sliceable by geographically displaying data globally, by area and by regional operation centre. This report request by the treasurer of the company and was part of the financial deck for the company directors.
P2. (Excel, T-SQL, Dataset, Business Logic, PowerPivot)
Email validation report which categories customer email addresses and flags those categorized as potential risks.
Criteria of categorization:
- Email addresses that contain the company name or a subsidiary name while conforming to a valid email address format were excluded.
- Anything with gmail, yahoo, Hotmail was flag as private email.
- A check was performed to test if the company name (or the initials of a company name) were in the email address.
- Duplicate domains were assessed.
- Known email address exceptions, i.e. were the company name is not included in the email address, were accounted for.
The flagged emails would then be assessed manually
A report was prepared with the flagged email were progress could be tracked as employees investigated the addresses and updated the report.
P3. (SSRS, T-SQL)
An SSRS report flagging accounts receivable non perfect match clearances report.
P4. (T-SQL, EXCEL)
An ad hoc excel report showing the number of invoices per month sent to volume licensing partners. This was part of an analysis to determine how much money could be saved by consolidating the number of invoices sent globally.
P5. (SSRS, T-SQL, PowerPivot)
A report was created that was called the Forward aged history log which allowed the user to step back to the first day of previous months and forward age the accounts receivable records.
P6. (EXCEL, VBA)
Due to a quirk in a preexisting sharepoint application the invoice write off data it produced required reworking before into could be loaded into another application. The solution was to provide the end user with a vba macro that could establish which rows contained cells were multiple values existed in one cell could then split out those cells into distinct rows.
P7. (SSRS, Stored Procedures)
An SSRS report which determined uncleared Items by looking at items were the clearing date field is null. Parameters included period, area and regional operation centre.
P8. (EXCEL, T-SQL, Stored Procedures, Sharepoint, UX, UI, Graphing, PowerPivot, DAX)
An excel report showing contacts logged with debtors on a daily, weekly and monthly basis.
- Number of customers with open AR
- Number of customers contacted
- Number of manual comments logged
- Percentage of customers contacted
- Number of contacts made per month over the previous year
By way of a bar chart
- Account Reconciliation Action
- Formal Letter Sent
- Internal Call/Email
- Internal Notification Sent
- Outbound Call
- Visit to Customer
P10. (T-SQL, EXCEL, DAX, Sharepoint, SSIS, UX, UI, Business Logic, Domain Knowledge, PowerPivot, DAX)
The new SLA (Service-level agreement) report which was with regards to the collection of accounts receivable was created.
This was a large report serving the client company, dozens of managers and hundreds of employees globally. It was the main deliverable of a consolidation project to automate, standardise and globalise reports that had existed internationally. A pipeline was necessary to ETL the required data as millions of rows of data were pulled from several locations globally. This job needed to be run three times a day to stay in sync with the three different time zones the collection services operated across. This data then needed to be aggregated and stored for year on year and quarter on quarter comparison.
The SLA report had over 70 fields and due to its global scope it was necessary to keep it generic as possible allowing end users to further refine the report by their department, area, country, regional operation centre by providing 10 slicers.
P11. (Access, SQL, EXCEL)
The development of a section wide time management database developed in SQL used to record employee absence, whether that is through illness or planned holiday or training time. This database was developed as it was a prerequisite to developing a capacity report.
Company: Electricity supplier
P1. (SSIS, SQL Server Agent Jobs, ETL)
Reconciling the electricity supply board summary messages against the live individual messages received.
- Creating tables to house data
- Creating an SSIS job to reshape daily coded messages
- De-duplicating data
- Removing duplicates
- Highlighting missing files
Deliverable: an updated and accurate account of customer status history by way of reconciled data.
P2. (Stored Procedures, EXCEL, VBA, Reporting, UX, UI)
Updated and automated funnel reporting summary which deals with communication with customers:
- Contact rates
- Win rates once contacted
- Registration cancelations with old suppliers
The display of this report showed a graphical break down of the methods of contact i.e. door to door, retention team and landlord accounts over time.
Report formatting was introduced transforming the excel file from a bare bones spreadsheet to a sudo custom front end with the company logo and colours.
Automation was achieved by creating a stored procedure on the database providing the data. No external connections were created as part of the file as the connection and update are achieved using vba. This adds an extra layer of security as the vba access is locked.
P3. (SSIS, SQL Server Agent Jobs)
Creation of an SSIS ETL to pull data from one database to another assuring the destination database is kept up-to-date with various sources. The destination database can be supplied the same data contained in the ETL source database from other databases. Therefore a select of the ETL source database is needed to only pull what does not already exist in the destination database.
P4. (SQL Server Agent Jobs,Stored Procedures, ETL, EXCEL, Validation)
Third party vendors supply data in the form of excel files regarding installations that need to be incorporated into the reporting database for cross referencing as well as validation and reconciliation.
Before this could be achieved the excel file templates needed to be updated to include validation rules.
Once this was completed a job was created to pull the file from a predetermined destination into the database were logic checks and validations are performed before the data is loaded.
P5. (Stored Procedures, Reporting, SSRS)
Call and web lead reports created. Two regularly run SQL queries were turned into two stored procedures which are used by two SSRS Reports to track marketing leads from websites and direct calls from customers. These communications tie back to specific marketing campaigns.
P6. (Stored Procedures, SQL logic)
A stored procedure was modified to update the logic which checks whether electricity supple board message files already exist within the database. If a file does not the file is loaded else the file is rejected.
P7. (Stored Procedures, SSRS, Business Logic)
The creation of a stored procedure and report which tracks the success/failure of the retention team in winning back or losing a customer to a competitor. This report tracks the customer details like the customer name, whether they were won/lost and the competitor they were lost to.
P8. (SSRS, UX, UI)
Created the standard companywide report template to be used in all SSRS reports.
P10. (Stored Procedures, Reporting, SSRS)
Created a stored procedure and report to highlight new joins for the intention of sending them a welcome email.
P11. (SSMS, Automation)
Using the SQL SERVER 2008 R2 Reporting Services Configuration Manager to setup a ReportServer Database for use in sending SSRS reports via email automatically.
P12.(Stored Procedures, Reporting, SSRS)
Created a stored procedure and report to highlight customers who have received promotions, what these promotion were for and a summation of their monetary value.
P13.(Stored Procedures, Reporting, SSRS)
Updated a legacy report which shows the success rate of agents and contact methods with new metrics.
P14. (Stored Procedures, Reporting, SSRS)
Updated regularly run queries that generate call and sales data for a daily report and converted these queries into Stored Procedures for use in two SSRS Reports.
P15. (T-SQL, User Defined Function)
Create function to convert UTC to local GMT taking account of Daylight savings time.
P16. (T-SQL, User Defined Function)
Convert a stored procedure into a function assigning calls to the correct queue based call type and menu press.
P17. (T-SQL, Database Development, Stored Procedures, Business Logic)
Created the database, tables and stored procedures to capture live call data from the in2Tel system.
This relies on the in2Tel call management system sending call posts which need to be intercepted and stored in the database. Each call has 5 posts, when the fifth has been received the call has been completed.
This process uses a web service created in C# to intercept the posts and then call the Stored Procedures to populate the table.
P18. (Stored Procedures, Reporting, SSRS, UX, UI)
Leader board display for sales agents.
An SSRS report set to refresh every 5 minutes to be displayed on a large monitor visible to everyone in the sales department. The leader board displays the sales agent’s names, attributed number of live calls and sales as well as conversion rates.
When a user does not enter a date range by default the display shows todays range only. When a user enters a date range a period column is included in the report showing sales for every day in the date range. Allow the report to work historically.
P19. (SSRS, Deployment, Version Control, Database Development, Team Foundation Server: Visual Studio)
Created and updated existing tables and scripts to support the release of a mobile app which ties into the companies back end. Focus was on the communication aspect of the app, email etc.
P20. (Stored Procedures, SSRS, Reporting, Business Logic)
Create call transferred SQL logic and incorporated it into existing reports.
P21. (T-SQL, Reporting, Business Logic)
Identification of incorrect call transfers based on CallType arrival and final CallType destination.
P22. (T-SQL, Reporting, Business Logic, Automation)
High visibility project with a main focus on creating a workflow engine to be used for Revenue protection.
- The main deliverable of this project were to create a pipeline to crush large volumes of data to prepare a condensed data set.
- Defining the rules of the workflow engine.
- Creating the workflow engine itself.
- Automating actions such as emails, sms messages etc. to be triggered based on the analysis of the workflow engine.
P23. (T-SQL, Batch Scripting)
Created batch script to:
Combine dozens of insert SQL scripts with thousands of rows.
Split the combined script of values into insert batches.
P24. (T-SQL, EXCEL Formulas)
Created customer load profile table and populated it with data provided in excel files. Required the use of excel formulas to create dynamic insert statements.
P25. (T-SQL, Stored Procedures, Optimisation)
Optimised poorly written legacy stored procedure, execution time dropped from 30 minutes to 10 seconds
P26. (SSRS, Reporting, Stored Procedure, Business Logic)
Conversion of an excel report used by the CEO regarding customer acquisitions into an SSRS report.
P27. (Stored Procedure, CRM)
Created stored procedure that will allow users to push through promotions using the CRM were promotions have failed due to users providing incorrect data, i.e. mobile, email address.
P28. (Stored Procedure)
Created the Stored Procedures needed to return the following fields regarding customer top-up data:
- Date Created
- Top-up type e.g. (web, promotion, android/apple)
- Number of top-ups
- Top up amount
All of which grouped by day.
- Date of new registrants
- Number of new registrants
P29. (T-SQL, Database Development, Stored Procedures, Business Logic, UX, UI, Optimisation)
Converted the sales report, updated daily for the CEO and directors, from an excel file to an SSRS report upgrading the report to be a more encompassing report dynamic. The report now shows department data on a monthly basis which can be drilled down by week, day.
The sales report required a much more extensive backend to enable the population of numerous newly introduced section tables.
P30. (T-SQL, Database Development, Stored Procedures, Business Logic)
New Stored Procedure to allow front-end end users to update a customers email and phone number.
P31. (T-SQL, Database Development, Stored Procedures, Business Logic)
New Stored Procedures to create aggregated views of customer top up history and new registrants.
P32. (T-SQL, Database Development, Stored Procedures, Business Logic, UX, UI, VBA, EXCEL, PowerPivot)
New dynamic, self-serving report for CEO that shows new customer, customer loss, net gain and graphs progress for the year. Report creates a connection via vba to the database from excel and executes stored procedures.
P33. (T-SQL, SSRS, SSMS, Stored Procedures, Business Logic, UX, UI)
Completion Rate Report Specification: This report is designed to show, for specified campaign groupings, the current outcome of sales that occurred within a specified time frame.
Provides an agent level completion rate with dynamic conversion metrics.
P34. (T-SQL, SSRS, SSMS, Stored Procedures, Business Logic, UX, UI)
Reschedule Rate Report Specification: This report is designed to show the current status of every account that failed for an ‘In Progress’ reason for a given period time.
P35. (T-SQL, SSRS, SSMS, Stored Procedures, Business Logic, UX, UI)
Cancellation Rate Report Specification: This report is designed to show, for specified campaign groupings, the cancellation reasons that occurred within a specified time frame.