Reporting catalog

9.1 Reporting infrastructure

MES-Plus uses three reporting mechanisms, in descending order of volume:

• Crystal Reports — the primary reporting engine. ~180 report definitions (.rpt files) live on a network share, executed by the Crystal Reports runtime embedded in the thick client and the integration broker.
• In-application list views — most application screens have an “export to Excel” button on their list view. This writes a CSV (despite the button label) which is opened by the default application; effectively, ad-hoc query results.
• Direct SQL queries — the period-close lead and the sysadmin have read access to the database and use SQL Server Management Studio for queries that the standard reports do not answer. There is no formal “ad-hoc query” governance.

9.2 Crystal Reports inventory

The ~180 reports break down approximately:

Class	Count	Typical user
Operational (daily / shift)	35	Operators, planners, warehouse
Procurement	15	Procurement
Sales / customer	20	Sales, accounting
Production (variance, scrap, OEE-like)	18	Planning, production manager
Inventory / stock	22	Warehouse, procurement, planner
Financial (sub-ledger, GL summaries)	35	Accounting, close lead
Statutory (VAT, intrastat, statistical)	12	Accounting, close lead
Master-data audits (e.g. items with no BOM)	8	Sysadmin, close lead
Management dashboards (single-page summaries)	6	Managing director, close lead
Vendor-shipped originals (rarely or never used)	9	(legacy)

Approximately 40 reports are in heavy regular use; the remaining ~140 are run monthly or less, including the 9 vendor-originals that no one runs.

9.3 Top 10 reports by usage frequency

Ranked from a 90-day sample of Crystal client-invocation telemetry (captured by a logging wrapper added in 2021):

1. Daily work-centre release — printed each morning for the production noticeboard.
2. Stock by location — interactive, queried throughout day by warehouse and planner.
3. Open customer orders by delivery date — sales dashboard, refreshed several times daily.
4. Production variance summary — weekly by production manager.
5. Supplier invoice three-way-match exceptions — daily by accounting.
6. AR ageing — weekly by accounting; daily during cash-collection campaigns.
7. Period-close sub-ledger reconciliation — monthly; the largest report (60+ pages).
8. Scrap analysis by reason code — weekly by quality.
9. Bank-statement unmatched lines — daily by accounting.
10. MD weekly summary (one-pager) — Monday morning.

9.4 Ad-hoc query and BI

For questions not answered by Crystal Reports, two routes exist:

• In-application Excel export. Users export a list view and pivot in Excel. This is the most common ad-hoc path. The export is unstructured — no metadata, no consistency — but adequate for ~80 % of ad-hoc questions. Auditor observed several recurring “ad-hoc” exports that have evolved into informal monthly reports.
• External BI / data warehouse. The nightly BI export (§8.1 entry 15) feeds a separate customer-owned data warehouse. The warehouse is queried by a Power BI tenant licensed to the managing director, the close lead, and the sales director. The warehouse contains transactional history back to 2018 (when the BI export was set up) and is refreshed each night.

The Power BI tenant has approximately 12 dashboards in active use. Three of these are quasi-operational (daily-decision support); the rest are weekly/monthly review. The Power BI dashboards are not part of the MES-Plus audit scope but are a major consumer of MES-Plus data, and any modernisation that changes the BI export shape (§12) is an implicit dependency on Power BI dashboard refresh.

9.5 Reporting risks and limitations

Four observations:

1. Crystal Reports runtime version. The runtime in use is Crystal Reports 2013 SP-something, no longer in support. SAP (Crystal’s owner) supports the latest runtime, which would require some report re-saving / re-testing; the customer has not budgeted for this transition. Severity-3 risk, see §11.

2. No report-level access control beyond the file share. Any user with read access to the reports network share can run any report (subject to the database role’s permission to read the underlying data, which limits damage but not exposure). Reports containing sensitive payroll-summary or margin data are protected only by the share ACL. [CFG]

3. Reports embedding direct SQL. Approximately 40 of the 180 reports contain hard-coded SQL (rather than parameterised stored-procedure calls). Schema changes — even cosmetic ones, like column renames — break these reports silently. The reports were last comprehensively re-tested in the 2017 upgrade.

4. No formal “report retirement” process. New reports are added on request; old reports are never removed. The ~140 rarely-used reports represent dormant attack surface, dormant schema dependencies, and dormant operational confusion.

9.6 Reporting versus operational data

A pattern worth noting: roughly 30 % of the Crystal Reports are essentially operational forms (e.g. the daily work-centre release, the goods-receipt confirmation slip) rather than reports in the analytical sense. These are tightly coupled to the operating process; their replacement is not “modernise the reporting layer” but “modernise the operating process that produces them.” This distinction matters for §12 — a Crystal-Reports-to-modern-BI migration that does not account for the operational-form half of the inventory will solve only part of the problem.

9.7 What is missing from current reporting

Three reporting needs surfaced during the audit that current reporting does not address:

• Operator productivity by shift. Production manager has been requesting this for two years. The data exists (production_events has the operator and the elapsed time) but no report aggregates it.
• Inventory aging. Stock-on-hand by item, broken down by receipt age. Useful for write-down decisions. Auditor verified the data exists; no report consumes it.
• Real-time WIP visibility. Current reports are 24-hour latent (rebuilt overnight). The production manager would value a near-real-time WIP picture.

These gaps are not faults of the audited system; they are scope decisions the customer has not yet revisited. They are recorded here for completeness and are touched on in §12.