What crosstab analyses are
A crosstab (pivot table) in OTBI displays measures as cells in a matrix, with row and column dimensions. Example: employees as rows, months as columns, and hours worked as values — giving a time-attendance matrix in a single view.
Creating a pivot view
- Create an OTBI analysis with your data columns
- Add a Pivot Table view: Edit Analysis → Views → Add View → Pivot Table
- In the pivot editor:
- Drag dimensions to Rows (vertical axis)
- Drag dimensions to Columns (horizontal axis)
- Drag measures to Measures (cell values)
Common pivot layout example
Rows: Department Name Columns: Calendar Month (format: MMM-YY) Measures: Headcount, Average Salary
This gives a matrix where each cell shows headcount and average salary for a department in a specific month.
Grand totals and subtotals
Right-click a row/column dimension → Aggregation → Show Total. Configure position: before or after. For nested dimensions (Year → Quarter → Month), subtotals at the Quarter level are automatic if Quarter is in the column structure.
Handling dynamic column headers
When columns are time periods (months, quarters), the number of columns changes as new periods are added. OTBI handles this automatically — no template changes needed. This is a major advantage of OTBI pivot tables over BIP RTF templates where column counts are often fixed.
Conditional formatting in pivot cells
Right-click a measure column → Format Column → Conditional Format:
- If
Headcount < 5→ background red - If
Variance > 10%→ font green bold
These conditions apply to the entire measure column across all cells.
Drilling down in pivots
Configure column or row headers as drillable — clicking a quarter opens the monthly breakdown. Set up drill navigation via Edit Analysis → Interaction → Column Interactions → Drill in Place.
Exporting pivot tables
OTBI pivot tables export to Excel with the matrix structure preserved — rows, columns, and totals maintain their layout. This makes OTBI pivots particularly useful for finance teams who need Excel-compatible period reports.