SSRS Report Development

Project Overview

While serving as Data Services Manager at Cogsdale, I led a project for the Sewer and Water Board of New Orleans, a major city with a population of approximately 390,000. I spearheaded the development of an automated SSRS report to handle “Sanitation Overpayment” calculations, a task previously conducted manually. This project required extensive collaboration with stakeholders and subject matter experts to gather and analyze existing reports and processes. The final automated solution saved significant operational time and improved accuracy, providing the state government with a reliable and efficient tool for managing sanitation overpayments.

Project Scope

The project scope included designing and developing a complex stored procedure to automate the sanitation overpayment calculations. This involved data extraction and aggregation from multiple tables and views, handling various transaction types, and ensuring data integrity throughout the process. Key activities included stakeholder meetings, interviews with subject matter experts, data analysis, and iterative testing. The solution replaced a time-consuming manual process, significantly reducing the effort required and enhancing the reliability of the calculations for state government reporting.

Key Responsibilities

Stakeholder and Consultant Collaboration: Worked closely with stakeholders and application consultants to identify all necessary data elements required for the sanitation overpayment report.

Development of SQL Stored Procedures: Utilized MS-SQL Server Management Studio to develop a sophisticated stored procedure that automated the aggregation and calculation of sanitation overpayment data. This process involved the creation and management of temporary tables, complex joins, conditional logic, and multiple layers of data aggregation.

Automation via SSRS: Designed and implemented an SSRS report that automatically pulled data using the stored procedure, significantly reducing the time required for preparation and calculation from several hours to mere minutes.

Reporting and Data Validation: Ensured the report met the precise needs for accuracy and detail as required by the third-party government entity, including necessary validations to maintain data integrity. The solution incorporated extensive data integrity checks and validations to ensure accuracy and completeness.

Outcome

The automation of the Sanitation Overpayment report has not only saved numerous hours per week for employees but has also increased data accuracy and reliability. The project streamlined the process, enabling the client to manage financial records more efficiently and transparently.

Tools and Technologies

SSRS (Report Development and Deployment)

SmartList (Gather validating data)

Jira (Issue Tracking)

SQL Server Management Studio (Custom Stored Procedure, Temporary Tables, Functions)