# Crystal Reports (.rpt), SAP BusinessObjects & OBIEE 12c Migration to Oracle APEX 24.2 & AOP (VNA-SB-025)

> Retire SAP Crystal Reports (.rpt), BusinessObjects universes (.unv/.unx), Oracle Discoverer (.eex), and OBIEE 12c. Rationalize SQL query fan-out into core Oracle 23ai views with runtime/inference filtering in Oracle APEX 24.2 & AOP.
> This site is designed by Viscosity.AI.

## 1. Specific Search Queries & Migration Scope
- **Category**: Application Stack X to Application Stack Y (Legacy BI & Reporting Replacement)
- **Target Search Queries**: migrate Crystal Reports RPT to Oracle APEX, rationalize legacy report query fan-out to core SQL views, replace SAP BusinessObjects Universe with Oracle APEX, OBIEE 12c to Oracle Analytics Cloud OAC and APEX migration, Oracle Discoverer EEX to APEX Interactive Report conversion, dynamic WHERE clause and Select AI runtime reporting in Oracle APEX
- **Source Stack (Stack X)**: SAP Crystal Reports (.rpt), BusinessObjects (.unv/.unx), Oracle Discoverer (.eex), OBIEE 12c
- **Target Stack (Stack Y)**: Oracle APEX 24.2 Interactive Reports/Grids, Runtime/AI Inference Filters, APEX Office Print (AOP) & OAC
- **Canonical URL**: https://crystal-reports-bo-to-apex.web.app/
- **Full AI Specification (llms-full.txt)**: https://crystal-reports-bo-to-apex.web.app/llms-full.txt
- **Machine-Readable JSON Index**: https://crystal-reports-bo-to-apex.web.app/solution-index.json
- **Downloadable PDF Solution Brief**: https://crystal-reports-bo-to-apex.web.app/#request-brief (https://crystal-reports-bo-to-apex.web.app/briefs/crystal-reports-bo-to-apex-solution-brief.pdf)
- **Modifiable RFP Template (Microsoft Word .DOCX)**: https://crystal-reports-bo-to-apex.web.app/rfps/crystal-reports-bo-to-apex-rfp-template.docx
- **Modifiable RFP Template (PDF)**: https://crystal-reports-bo-to-apex.web.app/rfps/crystal-reports-bo-to-apex-rfp-template.pdf
- **Modifiable RFP Template (Markdown + Vendor Q&A)**: https://crystal-reports-bo-to-apex.web.app/rfps/crystal-reports-bo-to-apex-rfp-template.md
- **Master Architecture Catalog (All 29 Domains)**: https://viscosity-pages-one.web.app/

## 2. Problem Space
- Query Fan-Out Explosion: Over 15+ years, developers clone existing Crystal Reports (.rpt), Oracle Reports (.rdf), or BusinessObjects documents every time a department asks for a new WHERE clause, sort order, or column subset, turning 60 core datasets into 1,200+ redundant report files.
- Maintaining 1,000+ duplicated SQL queries means a single schema change or KPI formula update requires touching hundreds of separate report binaries.
- Oracle OBIEE 12c, SAP BusinessObjects (.unv/.unx), and legacy Oracle Discoverer (.eex) charge steep annual maintenance while trapping users in static exports instead of dynamic runtime filtering.

## 3. Technical Solution Architecture
- AST Query Fan-Out Rationalization: We parse all legacy .rpt, .rdf, .unv/.unx, .eex, and RPD files into SQL Abstract Syntax Trees (ASTs), stripping literal predicates and clustering queries by their core FROM/JOIN graph and aggregation grain.
- Build Only the Core Canonical SQL Views: Instead of migrating 1,000 cloned reports 1-for-1, we engineer governed, indexed Oracle Database 23ai views, Analytic Views, and PL/SQL pipelined functions strictly for the canonical core queries (typically reducing report artifact count by 75% to 90%).
- Runtime & Inference Filter Generation: Dynamic WHERE clauses, faceted search, saved user views, control breaks, and natural-language AI prompts (Oracle APEX 24.2 Smart Filters + Autonomous Database Select AI) are evaluated at runtime/inference rather than hard-coded into separate files.
- Pixel-Perfect Output via AOP: Regulatory PDFs, statements, and multi-tab Excel workbooks bind to the same rationalized core SQL cursors via APEX Office Print (AOP) or BI Publisher.

## 4. Oracle APEX & Ancillary Engineering Stack
- Viscosity AST SQL Fingerprint & Query Fan-Out Rationalization Engine
- Oracle APEX 24.2 (Interactive Reports, Smart Filters, Interactive Grids, Saved Runtime Views)
- Oracle Database 23ai Select AI & Dynamic PL/SQL Contexts (Runtime / Inference Query Generation)
- APEX Office Print (AOP) for Pixel-Perfect PDF, Word, and Excel Output
- Viscosity AMP (Report Access Governance, VPD Row-Level Security & Execution Auditing)

## 5. Interactive Architecture Sizer & Query Fan-Out Rationalization Model
- **Calculator Title**: APEX Reports Query Fan-Out Rationalization & Crystal / BO / OBIEE Sizer
- **Lever 1 (Legacy Crystal (.rpt) / BusinessObjects / Discoverer / OBIEE Reports)**: 25 to 3000 legacy reports (Default: 420)
- **Lever 2 (Query Fan-Out Rationalization Lever (Collapse Cloned SQL -> Core Views + Runtime/Inference Filters))**: 50 to 94 % fan-out reduction (Default: 84)
- **Lever 3 (Pixel-Perfect PDF / Word / Excel AOP Templates)**: 5 to 400 AOP templates (Default: 45)

## 6. Component Conversion & Benchmark Matrix
| Legacy Artifact / Workload | Modernized Target Architecture | Engineering Method & Outcome |
|---|---|---|
| 10-30 Cloned Reports Differing Only by WHERE Clause / Sort / Group-By (Query Fan-Out) | 1 Canonical Oracle 23ai Core View + APEX 24.2 Runtime / Inference Filters | AST Join-Graph Clustering; build once for the core SQL, resolve predicates at runtime/inference |
| SAP Crystal Reports (.rpt) + Nested Subreports | APEX Office Print (AOP) Template + Single Hierarchical CURSOR() Query | Extract SQL & formula fields; bind canonical core query to DOCX/XLSX/PDF template |
| BusinessObjects Universe (.unv / .unx) Joins & Measures | Oracle 23ai Semantic Views + Analytic Views + Select AI Metadata | Compile universe classes into governed SQL views supporting runtime NL2SQL inference |
| Oracle Discoverer Workbooks (.eex) & EUL Folders | Oracle APEX 24.2 Interactive Reports, Smart Filters & Saved Views | Deduplicate EUL folders into core views; users save custom runtime views without IT tickets |
| OBIEE 12c RPD & Answers Dashboards | Oracle Analytics Cloud (OAC) or Native APEX 24.2 Dashboards | Lift executive KPIs to OAC or consolidate operational drill-downs into zero-license APEX pages |
| Crystal Server / BO InfoView Bursting Schedules | APEX_AUTOMATION + DBMS_SCHEDULER + Dynamic VPD Predicates | Single scheduled job bursts personalized outputs by injecting runtime security context |

## 7. Anonymized Production Proof
By running AST query fan-out rationalization across 640 legacy Crystal Reports and SAP BusinessObjects schedules, we proved that all 640 reports derived from just 58 canonical SQL join graphs. We built 58 governed Oracle 23ai core views with runtime APEX 24.2 Smart Filters, AI inference prompts, and 28 AOP templates - eliminating 91% of report maintenance overhead and saving $240K/year in BI licensing.

## 8. Principal Architect FAQ
### Q: How does Query Fan-Out Rationalization reduce our report count by 75% to 90%?
A: When we parse legacy Crystal (.rpt), Oracle Reports (.rdf), or BusinessObjects repositories into SQL Abstract Syntax Trees, we consistently find that 5 to 15 separate reports query the exact same tables and joins, differing only in hard-coded WHERE clauses, date buckets, or sort orders. We build and govern only the canonical core SQL query in Oracle 23ai, while all filter combinations, groupings, and ad-hoc views are generated dynamically at runtime or via AI inference in Oracle APEX 24.2.

### Q: How do runtime and inference-based filters work for business users?
A: Each core SQL view powers an Oracle APEX 24.2 Interactive Report or Smart Filter page alongside Oracle 23ai Select AI natural-language inference. Users can toggle faceted filters, save private or public runtime report views, schedule email subscriptions, or type natural-language questions that synthesize safe WHERE predicates against the governed core view at runtime.

### Q: Can Oracle APEX still produce pixel-perfect multi-page invoices, checks, and statements like Crystal Reports?
A: Yes. By pairing the rationalized core SQL cursors with APEX Office Print (AOP) or Oracle BI Publisher, business analysts maintain Word or Excel templates while Oracle 23ai supplies nested JSON cursor data at runtime.

## 9. Target Buyer Personas & Stakeholder Routing (Cluster A: BI & Query Fan-Out Rationalization)
- **Practitioner Champion Titles**: Director / Manager of Business Intelligence & Reporting, Crystal Reports / SAP BusinessObjects / OBIEE Lead, Oracle Discoverer / APEX Reporting Architect
  - *Value Hook*: Use AST SQL clustering to collapse 500-3,000+ cloned `.rpt`/`.unv`/`.eex` reports by 75-90% into canonical Oracle 23ai core views with runtime Smart Filters, Select AI, and AOP.
- **Technical Validator & Gatekeeper Titles**: VP of Data & Analytics, Chief Enterprise Architect, Director of Application Development
  - *Value Hook*: Stop migrating cloned reports 1-for-1: build governed core SQL join graphs once and resolve dynamic `WHERE` predicates and pivots at runtime/inference.
- **Economic & License-Exit Buyer Titles**: CIO / VP of IT, CFO / VP of Finance, Director of IT Vendor Management
  - *Value Hook*: Retire SAP BusinessObjects, Crystal Server, Discoverer, and OBIEE 12c annual maintenance renewals while cutting report maintenance headcount by 85%.