BI SQL Editor¶

Last Updated: March 2026

The BI SQL Editor is an OCA module that lets IT administrators and developers build custom SQL-based reports directly inside Odoo. You write a SELECT query against the PostgreSQL database, and the module auto-generates a full Odoo model, views (pivot, graph, tree, form), a search view, a menu item, and access controls. Reports can be backed by normal SQL views (always live) or materialized views (cached, faster, refreshed on a schedule). This is a developer/admin tool -- it requires SQL knowledge and familiarity with Odoo's database schema.

Dashboards
├── SQL Reports            <-- Generated reports appear here
└── Configuration
    └── SQL Views          <-- Where you create and manage BI SQL Views

SQL Reports is visible to users in the SQL Request / User group.
SQL Views (the configuration list) is visible only to SQL Request / Manager.

How It Works¶

The module follows a four-stage state machine. Each stage unlocks the next set of actions:

State	Name	What Happens
`draft`	Draft	You write the SQL query, set the name and technical name, choose groups, and configure options.
`sql_valid`	SQL Valid	The query has been validated. The module parsed the columns and created field mapping rows. You configure each field's type, graph role, indexing, and visibility.
`model_valid`	SQL View and Model Created	A PostgreSQL view (or materialized view) and an Odoo ORM model have been created in the database. A cron job is created for materialized views.
`ui_valid`	Views, Action and Menu Created	Form, tree, graph, pivot, and search views plus a menu item and window action have been generated. The report is live.

You advance through states by clicking the header buttons. You can also reverse through states to make changes.

Creating a New SQL Report¶

Step 1: Create the Record¶

Navigate to Dashboards > Configuration > SQL Views.
Click New.
Fill in the header fields:

Field	What It Means	Example
Name	Human-readable report name (shown in menus and views)	`Pickup Summary by Customer`
Technical Name	Suffix for the SQL view and Odoo model. Must follow PostgreSQL identifier rules (lowercase, underscores, no spaces). The full view name is auto-computed as `x_bi_sql_view_<technical_name>`.	`pickup_summary_by_customer`
Is Materialized View	If checked, creates a `MATERIALIZED VIEW` (data is cached and must be refreshed). If unchecked, creates a normal `VIEW` (always live, but can be slower on large data). Defaults to checked.	Checked
View Order	Comma-separated list of view types and the order they appear. Possible values: `pivot`, `graph`, `tree`, `form`.	`pivot,graph,tree`
Sequence	Controls the ordering of this report in the menu and list view. Lower numbers appear first.	`10`

Step 2: Write the SQL Query¶

On the SQL Settings tab, write your SELECT query in the ACE code editor.

Column Naming Rule

Every selected column must be aliased with a name that starts with x_. Columns without the x_ prefix are silently ignored and will not appear in your report. This is a hard requirement of the module.

Prohibited SQL Words

The module blocks queries containing: DELETE, DROP, INSERT, ALTER, TRUNCATE, EXECUTE, CREATE, UPDATE, or ir_config_parameter. Only SELECT queries are allowed.

Example query:

SELECT
    rp.name AS x_customer_name,
    rp.city AS x_city,
    COUNT(cp.id) AS x_pickup_count,
    SUM(cp.actual_weight) AS x_total_weight,
    rp.company_id AS x_company_id
FROM res_partner rp
LEFT JOIN customer_pickups cp ON cp.partner_id = rp.id
WHERE rp.customer_rank > 0
GROUP BY rp.id, rp.name, rp.city, rp.company_id

Preview Before Validating

Click Preview Results to run the query and see the first 100 rows in a popup. This does not change the state -- the query is executed inside a savepoint that gets rolled back.

Do NOT use SELECT *

Always name each column explicitly. SELECT * and SELECT table.* are not supported because every column needs an x_ alias.

Step 3: Set Security (Allowed Groups)¶

On the Security tab:

Allowed Groups -- Select which Odoo security groups can view the generated report. Defaults to SQL Request / User. You can add any group (e.g., Sales / User, Inventory / Manager).
Extra Rule Definition -- An optional domain expression that creates a global ir.rule on the generated model. Useful for multi-company filtering.

Field	What It Means	Example
Allowed Groups	Groups that get read-only access to the generated report model	`SQL Request / User`
Extra Rule Definition	Domain expression for row-level security (uses `x_` field names)	`['\|', ('x_company_id','child_of',[user.company_id.id]),('x_company_id','=',False)]`

Groups Can Be Changed Later

If you change the allowed groups after the model has been created, click the Update Model Access button that appears in the header to apply the changes.

Step 4: Validate the SQL¶

Click Validate SQL Expression in the header.
The module executes the query inside a rolled-back savepoint to verify it runs without errors.
If valid, the state changes to SQL Valid and the SQL Fields tab appears.

If the query has errors, you get a popup with the PostgreSQL error message. Fix the query and try again.

Step 5: Configure Field Mappings¶

After validation, the SQL Fields tab shows one row per column returned by your query. The module auto-detects the SQL type and guesses the Odoo field type. Review and adjust each field:

Field	What It Means	Example
Name	Column name from the query (read-only, always starts with `x_`)	`x_customer_name`
SQL Type	PostgreSQL data type detected from the query (read-only)	`character varying`
Field Description	Human-readable label displayed to users. Auto-generated from the column name. Editable.	`Customer Name`
Field Type	Odoo field type. Auto-mapped from SQL type. Can be changed.	`char`
Model	For `many2one` fields only -- select the related Odoo model	`res.partner`
Clickable	For `many2one` fields -- if enabled, the field is clickable in the list view (opens the related record)	Checked
Selection Options	For `selection` fields only -- Python list of `(key, label)` pairs	`[('draft','Draft'),('done','Done')]`
Group Operator	How this field aggregates when grouped. Options: Sum, Average, Minimum, Maximum. Only for `integer` and `float` fields. Default is Sum.	`sum`
Is Index	Create a database index on this column. Recommended for columns used in filters and group-by. Only available for materialized views.	Checked
Graph Type	Role in pivot/graph views: Row (group-by dimension), Column (column header), or Measure (aggregated value). Leave empty to exclude from graphs.	`measure`
Is Group By	Add a "Group By" filter option for this field in the search view	Checked
Tree Visibility	How the field appears in the list (tree) view	`available`
Field Context	Custom Odoo context dict for this field in all views (use single quotes)	`{}`

SQL Type to Odoo Field Type Mapping¶

PostgreSQL Type	Odoo Field Type
`boolean`	`boolean`
`bigint`	`integer`
`integer`	`integer`
`double precision`	`float`
`numeric`	`float`
`text`	`char`
`character varying`	`char`
`date`	`date`
`timestamp without time zone`	`datetime`
`integer` (column name ends in `_id`)	`many2one` (model auto-guessed)

Tree Visibility Options¶

Value	Behavior
Available	Column is visible by default
Optional (shown)	Column is shown but user can hide it via the column picker
Optional (hidden)	Column is hidden but user can show it via the column picker
Invisible	Column exists but is never shown in the list
Unavailable	Column is completely excluded from the list view

Step 6: Create SQL Elements¶

Click Create SQL Elements.
The module:
- Creates an Odoo model (x_bi_sql_view.<technical_name>)
- Creates the PostgreSQL view (or materialized view) named x_bi_sql_view_<technical_name>
- Creates any requested indexes on the materialized view
- Creates an ir.rule with the domain you specified
- Creates ir.model.access records granting read-only access to each allowed group
- For materialized views: creates a scheduled action (cron) that refreshes the view daily
State advances to Model Valid.

Many2one Fields Must Have a Model

If any field is set to type many2one but does not have a related model selected, validation will fail. Set the model before proceeding.

Before creating the UI, you can change the Parent Odoo Menu on the Extras Information tab. By default, reports appear under Dashboards > SQL Reports. Change this to place the report under a different menu (e.g., under Sales or Inventory).

Step 8: Create the UI¶

Click Create UI.
The module generates:
- A form view with all fields in a group
- A tree (list) view respecting your tree visibility settings
- A graph view (bar chart) using your graph type settings
- A pivot view using your graph type settings
- A search view with all fields searchable and your group-by filters
- A window action with the view order you specified
- A menu item under the chosen parent menu
State advances to UI Valid.
Click Open View to see the finished report.

Common Tasks¶

Refreshing a Materialized View¶

Materialized views cache data and do not update automatically in real time. There are two ways to refresh:

Manual refresh:

Open the SQL View record in Dashboards > Configuration > SQL Views.
Click the Refresh button in the header.
The data is reloaded from the underlying tables and the action name updates with the refresh timestamp.

Automatic refresh (cron):

When a materialized view is created, a daily cron job is automatically set up.
To change the frequency, click the Odoo Cron link on the form to open the scheduled action, then adjust the interval.
The cron field is visible on the form when the view is materialized and in state Model Valid or UI Valid.

Check the Refresh Timestamp

For materialized views, the menu item name includes the last refresh timestamp (e.g., "Pickup Summary by Customer (03/03/2026 14:30:00 UTC)"). This tells users how current the data is.

Modifying an Existing Report¶

To change the SQL query or field configuration:

Open the SQL View record.
Click Delete UI to go back to Model Valid state (removes menu, action, and all views).
Click Delete SQL Elements to go back to SQL Valid state (drops the PostgreSQL view and ORM model).
Click Set to Draft to go back to Draft state (allows editing the query).
Make your changes and walk through the states again.

Stepping Back Destroys Generated Objects

Going back to an earlier state deletes all objects created in later states. The menu, views, action, model, and database view are all dropped. Any bookmarks or favorites users created pointing to the old action will break.

Changing Security Groups After Creation¶

If you need to add or remove groups after the model exists:

Open the SQL View record.
On the Security tab, modify the Allowed Groups.
A yellow Update Model Access button appears in the header.
Click it to drop and recreate the ir.model.access records.

Deleting a Report¶

The report must be in Draft or SQL Valid state to delete.
If it is in a later state, click Set to Draft first (this walks backward through all states, cleaning up the view, model, cron, menu, etc.).
Then delete the record.

Cron Is Deactivated, Not Deleted

When you set a materialized view back to draft, the associated cron job is deactivated but not deleted. It will be reactivated if you re-create the SQL elements.

Copying a Report¶

You can duplicate an existing SQL View. The copy gets:

Name: <original name> (Copy)
Technical name: <original_technical_name>_copy
State: draft (you must walk through all states again)

Action Context Settings¶

The Action Settings tab has two context sections:

Computed Context¶

Auto-generated from your field graph type settings. Controls which fields are pre-loaded as pivot measures, row groups, and column groups. This is read-only.

Example:

{'pivot_measures': ['x_total_weight', 'x_pickup_count'], 'pivot_row_groupby': ['x_city'], 'pivot_column_groupby': []}

If no fields have graph type set to measure, the pivot defaults to showing __count__ (record count).

Custom Context¶

A manually editable Python dict that merges into the computed context when the action is created. Use this to override defaults or add extra context keys.

Field	What It Means	Example
Computed Context	Auto-generated from graph type settings (read-only)	`{'pivot_measures': ['x_total_weight']}`
Custom Context	Your overrides, merged on top of computed context	`{'search_default_group_by_x_city': 1}`

Extras Information Tab¶

This tab shows all the generated Odoo objects. These fields are read-only and populated after each creation step.

Field	What It Means
Model Name	Full qualified model name (`x_bi_sql_view.<technical_name>`)
Odoo Model	Link to the `ir.model` record (visible after model creation)
Parent Odoo Menu	Where the report's menu item will be placed (editable until UI is created)
Odoo Form View	Link to the generated form view
Odoo Tree View	Link to the generated list view
Odoo Graph View	Link to the generated graph view
Odoo Pivot View	Link to the generated pivot view
Odoo Search View	Link to the generated search view
Odoo Action	Link to the generated window action
Odoo Menu	Link to the generated menu item

Materialized vs. Normal Views¶

Aspect	Materialized View	Normal View
Data freshness	Snapshot -- refreshed on schedule or manually	Always live -- queries run in real time
Performance	Fast reads (data is pre-computed and cached)	Can be slow on large datasets or complex joins
Indexes	Supported -- you can create indexes on individual columns	Not supported
Database size	Consumes disk space (shown in the "Database Size" field)	No extra storage
Cron job	Auto-created, runs daily by default	Not needed
Best for	Large datasets, complex aggregations, reports that don't need real-time data	Small datasets, simple queries, data that must always be current

Default to Materialized

For most reporting use cases at Scott Recycling, use materialized views. Our dataset is large enough that live views on joins across res_partner, customer_pickups, and fleet_vehicle can be slow. A daily refresh is sufficient for most management reports.

Writing Good Queries¶

Rules¶

Prefix all column aliases with x_ -- Columns without the prefix are ignored.
Do not use SELECT * -- Name each column explicitly.
No DDL or DML -- Only SELECT is allowed. The module blocks INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, and EXECUTE.
No references to ir_config_parameter -- Blocked for security.
Use column aliases for expressions -- SQL functions like COUNT(), SUM(), EXTRACT(), and CASE must have an AS x_<name> alias.

Tips¶

Many2one Columns

To create a clickable link to another Odoo record, select the foreign key column (must be an integer ending in _id). The module will auto-detect it as many2one and attempt to guess the related model. If the guess is wrong, manually set the correct model in the field mapping.

Selection Fields

If your query uses a CASE expression to produce a fixed set of labels, set the field type to selection and define the options as a Python list:

[('active','Active'),('inactive','Inactive'),('prospect','Prospect')]

Date Grouping

If you include date or datetime columns, users can group by them in the pivot view with automatic day/week/month/quarter/year granularity. This makes date columns very useful as graph type "Row".

Example: Pickup Volume by City¶

SELECT
    rp.city AS x_city,
    rp.state_id AS x_state_id,
    COUNT(cp.id) AS x_pickup_count,
    COALESCE(SUM(cp.actual_weight), 0) AS x_total_weight,
    DATE_TRUNC('month', cp.date) AS x_month
FROM customer_pickups cp
JOIN res_partner rp ON rp.id = cp.partner_id
WHERE rp.customer_rank > 0
GROUP BY rp.city, rp.state_id, DATE_TRUNC('month', cp.date)

After validation, you would configure:

x_city: Graph Type = Row, Is Group By = checked
x_state_id: Field Type = many2one, Model = res.country.state, Graph Type = Row, Is Group By = checked
x_pickup_count: Graph Type = Measure, Group Operator = sum
x_total_weight: Graph Type = Measure, Group Operator = sum
x_month: Field Type = datetime, Graph Type = Row, Is Group By = checked

Security¶

Groups¶

Group	Access Level
SQL Request / Manager	Full CRUD access to `bi.sql.view` and `bi.sql.view.field`. Can create, validate, build, and delete SQL reports. Can access the SQL Views configuration menu. The Odoo admin user has this group by default.
SQL Request / User	Can view generated SQL reports (the menu items under SQL Reports). Cannot access the configuration. This is the default group assigned to new reports.

The Manager group implies the User group (managers automatically get User access).

Per-Report Access¶

Each generated report model gets its own ir.model.access records. Only the groups listed in the Allowed Groups field on the SQL View receive read-only access to that specific report. Write, create, and unlink permissions are never granted on generated report models.

Row-Level Security (ir.rule)¶

The Extra Rule Definition field creates a global ir.rule on the generated model. This restricts which rows a user can see based on a domain expression. Common patterns:

Use Case	Domain Expression
Multi-company filtering	`['\|',('x_company_id','child_of',[user.company_id.id]),('x_company_id','=',False)]`
No restriction	`[]` (default)

Static Module Access¶

The base bi.sql.view model has these access rules:

Rule	Group	Read	Write	Create	Delete
`access_bi_sql_view_all`	(everyone)	No	No	No	No
`access_bi_sql_view_manager`	SQL Request / Manager	Yes	Yes	Yes	Yes
`access_bi_sql_view_field_all`	(everyone)	No	No	No	No
`access_bi_sql_view_field_manager`	SQL Request / Manager	Yes	Yes	Yes	Yes

This means only SQL Request Managers can see or modify the SQL View configuration records.

Troubleshooting¶

No Columns Found

If you get the error "No Column was found. Columns name should be prefixed by 'x_'", your query's column aliases are missing the x_ prefix. Go back to the SQL query and add AS x_<name> to every column.

Unsafe Word Detected

If you get "The query is not allowed because it contains unsafe word 'create'" (or similar), your query contains a prohibited keyword. Even if it appears inside a string literal or column name, the check uses a word-boundary regex. Rewrite the query to avoid the word.

Cannot Create Index on Non-Materialized View

Indexes are only supported on materialized views. If you need indexes, check the Is Materialized View checkbox. If you uncheck it later, any index flags on fields will cause a validation error.

Cannot Delete a Non-Draft View

You must set the view back to Draft state before deleting it. The Set to Draft button walks backward through all states, cleaning up the database view, ORM model, cron job, menu, action, and Odoo views.

Large Materialized View Refresh

If a materialized view has millions of rows, the refresh can take a long time and lock the table. Schedule refreshes during off-hours by editing the cron job's Next Execution Date and interval.