Custom Reporting

The number of custom reports I’ve written over the years must be in the thousands. Sometimes the report lives forever in a repository like SSRS, sometimes it is a once-off data analysis, and sometimes the report expands into a full-fledged application.

A title and escrow company I worked with needed a few custom reports that were not produced by their transactional system. The system used Crystal Reports natively, so I developed a set of additional reports that met their needs. This particular project required a daily process that summarized the data so that running the reports did not impact performance of the system when they were trying to close transactions.

A trucking company I worked with had a special method of calculating revenue breakdowns by trip. I developed a module that allowed them to define distances between terminals and then we used those custom measurements to override the standard calculation of the logistics software.

Skills: Business analysis, database design and development, reporting
Tools: SQL Server, DB2, MySQL, SSRS, Crystal Reports

April 18, 2018 Comments Off on Custom Reporting

Compliance Data

Many companies now face legal requirements to report health insurance and/or benefit participation information. These compliance activities can consume a lot of valuable staff time. Way back in my McDonald’s days I worked on non-discrimination testing for the company’s 401k and profit sharing plans.

Over the past 5 years I have been able to assist my clients with data compilation for both non-discrimination testing and ACA compliance reporting. What could have been a tedious manual effort for staff has become a simple matter of data extraction and summarization with some tools I have built for them.

Skills: Understanding of compliance requirements, data compilation and summarization
Tools: QuickBooks, MS Office VBA

April 18, 2018 Comments Off on Compliance Data

Sales and Labor Projections

I have developed several projection tools with the rich operational data my clients have compiled.

1) Sales projection – Given recent sales trends relative to prior year, project sales for an upcoming week.

2) Labor projection – Given the sales projection from #1 and a series of labor band targets, project the labor hours and costs for an upcoming week. During the week, compare sales and labor to projection so managers can make adjustments if necessary.

3) Marketing campaign projection – Given recent loyalty member transactions, project the revenue generated from those members over the course of a scheduled promotion. Post-promotion analyze the success of the campaign compared to the projection.

Skills: Data analysis, database design and development, reporting
Tools: SQL Server, SQL Server Reporting Services

April 17, 2018 Comments Off on Sales and Labor Projections

Power BI Dashboards

The recent improvement of Microsoft’s Power BI service has made it a reachable tool for smaller businesses. For $10/user/month (as of mid-2018), this tool can provide small businesses with analytical capabilities previously reserved for companies with much deeper pockets.

I have developed and deployed Power BI dashboards for 2 companies. Both clients were interested in testing the tool in limited engagements before deciding if it should be rolled out to a wider audience. And both have derived significant benefits from their evaluations.

Skills: Business analysis, database design and development, data analytics, dashboard design
Tools: Power BI, SQL Server, MS Azure SQL Database

April 17, 2018 Comments Off on Power BI Dashboards

Industry Group Reporting

A trucking and logistics client was a member of an industry group that shared some operational data among the members. This data took several days for an employee to compile from various reports, queries, and other sources around the company. They were looking for a way to compile that information more quickly and efficiently.

Harkening back to my Excel programming days at McDonald’s, I built a automated the spreadsheet to compile the data at the click of a button. There were several data points that still needed to be manually figured and entered. But the result was many saved hours of work on a monthly basis.

Skills: MS Office automation, database queries
Tools: Excel, MS Office VBA, SQL

April 17, 2018 Comments Off on Industry Group Reporting

Daily Operational Reports

A retail client with 10+ locations required their store managers to run some sales reports from the POS daily and fax them into home office. The data from those faxes was then keyed into a spreadsheet which was then distributed to management. The POS vendor was polling the stores daily and summarizing the data in a warehouse that was available for reporting. The problem was that the reporting tool was very inflexible and couldn’t summarize the data in a useful format.

During conversations with the POS vendor I discovered that they could replicate the sales data to an internal SQL Server database. Once we initiated that process it was just a matter of building the operational reports using SQL Server Reporting Services. A simple scheduler program distributed the reports daily to everyone that needed them.

Skills: Business analysis, database design and development, reporting
Tools: SQL Server, SQL Server Reporting Services

April 17, 2018 Comments Off on Daily Operational Reports