Case Study
Civil Contractor Performance Reporting System Through Power BI
Executive Summary
One of our clients in the UK deals in the Civil Engineering domain as one of the biggest
contractors that have operated in Scotland for nearly 60 years. ABC operates across
Scotland and beyond, working on Marine projects, Water Management, Infrastructure, and
Energy.
Problems faced by ABC
ABC is a civil contracting company that takes the project from various clients. ABC is
required to track all projects and their tasks, like when & what tasks are assigned to
their engineers, whether they are on track or not, and whether the assigned task will be
completed on time. Any roadblocks the engineer faces due to the delay in completion will
occur, and many more crucial informative items like this. Also, the column in the report
must display whether the task was Compliance or Non- Compliance (If “Compliance,” then the
job was completed within 60 Days, and Non-compliance if more than 60 days). ABC was using
spreadsheets to monitor their project tasks completion process, which was time-consuming,
prone to errors, and lacked real-time insights.
Our client ABC receives an Excel E1 from their client monthly, which holds information
like (project Id, project description, contractor, regional Hub like the south or west
regional hub, contract Reference id, its completion date is essential, Draft submission
date is the first date when this project started, duration compliance to FDA, Etc.
Another Excel E2, which our client ABC maintains monthly internally for the same projects
to track progress, had data columns like Auto Code Number, Contract number, Contract Name,
pricing, Contract manager, site agent, contract engineer, etc.
ABC is required to manually co-relate the information each month between two excel E1 and
excel E2 and figure out the status of the projects, their task, and the status of the
tasks assigned to their engineers. Also, color shades are applied to excel data according
to these conditions red color for less than 75%, orange color for between 75% to 90%, and
green for above 90%.
So, this activity required the number of employees each month working to generate the
monthly composite report by merging the information from both excel E1 and E2. It was also
prone to human error as the information was handled manually and was required to pass from
desk to desk. Hence, each employee involved in this process provided their input to the
monthly composite report required.
Solution ABC Wanted
ABC organization wanted some solution by which they could overcome the manual excel
merging requirement as it was prone to human error and took a lot of time for their
employee to prepare it. They wanted this whole process to be fully automated and the
reports they were creating manually over excel by applying numerous formulae to it.
ABC used to go through the same process each month to create several different sheets in
excel to show the project its tasks and the Engineer progress assigned to the task’s
month-wise, along with the data on whether the task was falling under Compliance or Non-
Compliance. In other words, ABC wanted completely automated and restructured reporting to
give enhanced business insights.
Our Solution
Here XRM Labs advised ABC to keep their requirement in mind to go with PowerBI reporting
services that will cover all their requirements to drill down information through
reporting services.
To automate the system entirely. Firstly, XRM is required to deal with the two excel our
client ABC receives monthly. So, to overcome this, we decided to finalize a location over
“One Drive” and created a folder named “Excel Dropbox” in which staff personnel will paste
the latest excel files monthly. Our PowerBI reporting system will further read to extract
data from it and to generate different required reports further.
We redesigned the existing reports that they required and provided more enhanced
drill-down reporting features. PowerBI helped us a lot to connect to the two files excel
spreadsheet and read data sources from cloud storage One Drive, allowing us to build
reports that suit our client-specific needs quickly.
The following features are implemented to overcome their issues:
• Automated manually
extracting the information and handling the two excel which clients must create
reports.
• Automatic reading of two excel by the system and generated required reports in
Power
BI with enhanced features like drilling down to any selected record to get further
insights.
• Producing composite drill-down reports with user and Period filters like User
Specific
measure report, which displays the info as per given requirements like which user on
which month worked on which project’s task along with its min percentge compliance
towards its assigned tasks.
• Provides the feature of viewing records, updating & delete records depending
on
provided permissions to users.
• The system can produce a variety of reports to evaluate how well account
managers and
the sales team are doing at pitching services against lines of business.
• Display per user wise the average percentage of compliance per period.
• Count of compliance per user as selected period-wise.
• Project Agreement report displaying data period-wise, regional hub-wise, no of
agreement with period completed and completion percentage achieved, not achieved as
per
the given time scale.
• Drilled down reporting feature showing compliance users-wise, like Minimum
compliance
reached by users and its total count.
• Gave the ability to users to see their monthly statements with drill-down
details of
the compliance components.
• Many values analysis reports are created so each user can show his % compliance
towards each project task assigned to him.
Technologies Used
• Power BI
• Web Services
• HTML, CSS, JavaScript
Client Benefits-their improved statistics (Facts and
Figures)
• With the help of our system, the client observed that their managers could more
accurately monitor their team's performance, and task tracking also improved.
• Our client experienced a lot of satisfaction as all their requirements have been
catered
to and in a much more attractive and easy way, the complete information was
presented.
• Increased the revenue and our client confidence through the Power BI reporting as it
eliminated human error in the provided information.
• Timely delivery of projects and billing cycle of our client streamlined as the
accurate
project completion information was made available.
• The new reporting is much more user-friendly and drastically reduced the time taken
off
the manual excel report generation tasks.
• Better client customer satisfaction as the tasks were much more accurately tracked
and
further can be drilled down.
• Reduced our client staff time by almost 100%, which was used to create excel reports.
• ABC employee workload was reduced due to the automation for the month report
generation.
• Through the Power BI reporting tool, drilled-down reports were implemented, which
helped
many high-level managers get better insights into the business.