BI reporting in Excel: consolidated data, clear KPIs and automated reports

Inconsistent data and manual evaluations cost time. With a consolidated BI data model in Excel, jovoco creates transparency, provides clear KPIs and enables reports at the touch of a button.

Customer

Customer benefits

Quick root cause analysis - consolidated and comparable evaluation of loss events
Management receives PDF reports with all relevant KPIs at the touch of a button
Employees use familiar Excel functions instead of complex tools
Reliable database - standardized model creates a "single source of truth"
Fast project results in a familiar evaluation tool without the need for training

Technology stack

"With the new BI reporting, we can identify causes much more quickly and derive targeted measures. The consolidated reports save us time and improve the quality of our decisions."

Company management

Challenges of the customer

A reporting system was already in place before the project, but it was only based on simple pivot tables. Claims were recorded inconsistently, typing errors in causation made analysis difficult and there were no hierarchical relationships between main orders and individual orders. Production data was only available in overall aggregates – a detailed analysis per order was not possible. The added value of jovoco: With a consolidated data model, inputs were standardized, production data integrated and clear hierarchies established. This made it possible for the first time to analyze causes and hotspots in a targeted manner and provide automated reports for management and the store floor.

Our approach

In close consultation with the customer, a solution was developed that combines data consolidation, flexible filtering and automated report export – seamlessly embedded in the existing Excel environment and without an additional platform.

Data consolidation

Inconsistent entries such as typing errors or different designations led to incorrect evaluations. Thanks to a mapping system, these entries are now automatically standardized and consistently transferred to the model. As a result, dimensions, slicers and key figures work reliably and the analysis remains comparable.

Data modeling

A hierarchical model links main order numbers with orders as well as production and damage data. Supplemented by time hierarchies (year/month), users can evaluate and make comparisons for the first time in various dimensions, e.g. at order level. Power Pivot and DAX-Measures, which calculate key figures such as breakage rate or average glass area in a reproducible manner, form the basis.

Transformation & KPIs

With Power Query (M), all transformations were standardized and implemented in an auditable manner. In addition to cleansing, additional columns were added to classify causes. DAX measures calculate central KPIs such as the number of breakages, square meter totals and percentages of production - reliably and comprehensibly.

Reporting & Visualization

Interactive Excel slicers enable intuitive filtering by order, time period, originator or cause. Diagrams are quickly customizable and show developments and distributions clearly and allow connections between breakage events and production to be quickly identified. This creates transparency that was previously not available.

Automation

A VBA module exports reports as PDFs at the touch of a button. This means that all evaluations are available in a standardized form for quality rounds or management reviews - without manual reworking. This significantly reduces effort and increases acceptance among users and managers.

Results for the customer

Consolidated damage descriptions in reporting

0 %

Report preparation instead of several hours of Excel rework

0 Hours

Further results:

Do you have a specific project in mind?

Your decision would be the same as that of renowned companies:

Similar case studies

Case studies Switzerland, Data analytics

Centralized logging with Kafka – transparency, efficiency and fewer outages

Distributed log files delay troubleshooting and increase downtime. With a central logging system based on Apache Kafka, jovoco creates transparency, accelerates analyses and reduces development and operating costs.

Case studies Switzerland, M365

Migration from Google to Microsoft 365 – how jovoco ensures a secure and smooth email transition

Standardizing IT infrastructure and reducing costs - how a medium-sized company switched from Google Workspace to Microsoft 365 with the support of jovoco GmbH and migrated emails, calendars and contacts without any downtime.

Case studies Munich, Data analytics

Proactive system monitoring with Azure & Co: avoid outages, increase security, reduce costs

Unnoticed failures and certificate errors lead to costs and risks. With a monitoring solution provided in Azure, jovoco enables early detection, automatic notification and continuous system availability.