2024-05-03 09:07:13 +00:00
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
< html xmlns = "http://www.w3.org/1999/xhtml" xml:lang = "en" lang = "en" >
< head >
< meta http-equiv = "Content-Type" content = "text/html; charset=utf-8" / >
< meta name = "generator" content = "Docutils: https://docutils.sourceforge.io/" / >
< title > BI SQL Editor< / title >
< style type = "text/css" >
/*
:Author: David Goodger (goodger@python.org)
:Id: $Id: html4css1.css 8954 2022-01-20 10:10:25Z milde $
:Copyright: This stylesheet has been placed in the public domain.
Default cascading style sheet for the HTML output of Docutils.
See https://docutils.sourceforge.io/docs/howto/html-stylesheets.html for how to
customize this style sheet.
*/
/* used to remove borders from tables and images */
.borderless, table.borderless td, table.borderless th {
border: 0 }
table.borderless td, table.borderless th {
/* Override padding for "table.docutils td" with "! important".
The right padding separates the table cells. */
padding: 0 0.5em 0 0 ! important }
.first {
/* Override more specific margin styles with "! important". */
margin-top: 0 ! important }
.last, .with-subtitle {
margin-bottom: 0 ! important }
.hidden {
display: none }
.subscript {
vertical-align: sub;
font-size: smaller }
.superscript {
vertical-align: super;
font-size: smaller }
a.toc-backref {
text-decoration: none ;
color: black }
blockquote.epigraph {
margin: 2em 5em ; }
dl.docutils dd {
margin-bottom: 0.5em }
object[type="image/svg+xml"], object[type="application/x-shockwave-flash"] {
overflow: hidden;
}
/* Uncomment (and remove this text!) to get bold-faced definition list terms
dl.docutils dt {
font-weight: bold }
*/
div.abstract {
margin: 2em 5em }
div.abstract p.topic-title {
font-weight: bold ;
text-align: center }
div.admonition, div.attention, div.caution, div.danger, div.error,
div.hint, div.important, div.note, div.tip, div.warning {
margin: 2em ;
border: medium outset ;
padding: 1em }
div.admonition p.admonition-title, div.hint p.admonition-title,
div.important p.admonition-title, div.note p.admonition-title,
div.tip p.admonition-title {
font-weight: bold ;
font-family: sans-serif }
div.attention p.admonition-title, div.caution p.admonition-title,
div.danger p.admonition-title, div.error p.admonition-title,
div.warning p.admonition-title, .code .error {
color: red ;
font-weight: bold ;
font-family: sans-serif }
/* Uncomment (and remove this text!) to get reduced vertical space in
compound paragraphs.
div.compound .compound-first, div.compound .compound-middle {
margin-bottom: 0.5em }
div.compound .compound-last, div.compound .compound-middle {
margin-top: 0.5em }
*/
div.dedication {
margin: 2em 5em ;
text-align: center ;
font-style: italic }
div.dedication p.topic-title {
font-weight: bold ;
font-style: normal }
div.figure {
margin-left: 2em ;
margin-right: 2em }
div.footer, div.header {
clear: both;
font-size: smaller }
div.line-block {
display: block ;
margin-top: 1em ;
margin-bottom: 1em }
div.line-block div.line-block {
margin-top: 0 ;
margin-bottom: 0 ;
margin-left: 1.5em }
div.sidebar {
margin: 0 0 0.5em 1em ;
border: medium outset ;
padding: 1em ;
background-color: #ffffee ;
width: 40% ;
float: right ;
clear: right }
div.sidebar p.rubric {
font-family: sans-serif ;
font-size: medium }
div.system-messages {
margin: 5em }
div.system-messages h1 {
color: red }
div.system-message {
border: medium outset ;
padding: 1em }
div.system-message p.system-message-title {
color: red ;
font-weight: bold }
div.topic {
margin: 2em }
h1.section-subtitle, h2.section-subtitle, h3.section-subtitle,
h4.section-subtitle, h5.section-subtitle, h6.section-subtitle {
margin-top: 0.4em }
h1.title {
text-align: center }
h2.subtitle {
text-align: center }
hr.docutils {
width: 75% }
img.align-left, .figure.align-left, object.align-left, table.align-left {
clear: left ;
float: left ;
margin-right: 1em }
img.align-right, .figure.align-right, object.align-right, table.align-right {
clear: right ;
float: right ;
margin-left: 1em }
img.align-center, .figure.align-center, object.align-center {
display: block;
margin-left: auto;
margin-right: auto;
}
table.align-center {
margin-left: auto;
margin-right: auto;
}
.align-left {
text-align: left }
.align-center {
clear: both ;
text-align: center }
.align-right {
text-align: right }
/* reset inner alignment in figures */
div.align-right {
text-align: inherit }
/* div.align-center * { */
/* text-align: left } */
.align-top {
vertical-align: top }
.align-middle {
vertical-align: middle }
.align-bottom {
vertical-align: bottom }
ol.simple, ul.simple {
margin-bottom: 1em }
ol.arabic {
list-style: decimal }
ol.loweralpha {
list-style: lower-alpha }
ol.upperalpha {
list-style: upper-alpha }
ol.lowerroman {
list-style: lower-roman }
ol.upperroman {
list-style: upper-roman }
p.attribution {
text-align: right ;
margin-left: 50% }
p.caption {
font-style: italic }
p.credits {
font-style: italic ;
font-size: smaller }
p.label {
white-space: nowrap }
p.rubric {
font-weight: bold ;
font-size: larger ;
color: maroon ;
text-align: center }
p.sidebar-title {
font-family: sans-serif ;
font-weight: bold ;
font-size: larger }
p.sidebar-subtitle {
font-family: sans-serif ;
font-weight: bold }
p.topic-title {
font-weight: bold }
pre.address {
margin-bottom: 0 ;
margin-top: 0 ;
font: inherit }
pre.literal-block, pre.doctest-block, pre.math, pre.code {
margin-left: 2em ;
margin-right: 2em }
pre.code .ln { color: grey; } /* line numbers */
pre.code, code { background-color: #eeeeee }
pre.code .comment, code .comment { color: #5C6576 }
pre.code .keyword, code .keyword { color: #3B0D06; font-weight: bold }
pre.code .literal.string, code .literal.string { color: #0C5404 }
pre.code .name.builtin, code .name.builtin { color: #352B84 }
pre.code .deleted, code .deleted { background-color: #DEB0A1}
pre.code .inserted, code .inserted { background-color: #A3D289}
span.classifier {
font-family: sans-serif ;
font-style: oblique }
span.classifier-delimiter {
font-family: sans-serif ;
font-weight: bold }
span.interpreted {
font-family: sans-serif }
span.option {
white-space: nowrap }
span.pre {
white-space: pre }
span.problematic {
color: red }
span.section-subtitle {
/* font-size relative to parent (h1..h6 element) */
font-size: 80% }
table.citation {
border-left: solid 1px gray;
margin-left: 1px }
table.docinfo {
margin: 2em 4em }
table.docutils {
margin-top: 0.5em ;
margin-bottom: 0.5em }
table.footnote {
border-left: solid 1px black;
margin-left: 1px }
table.docutils td, table.docutils th,
table.docinfo td, table.docinfo th {
padding-left: 0.5em ;
padding-right: 0.5em ;
vertical-align: top }
table.docutils th.field-name, table.docinfo th.docinfo-name {
font-weight: bold ;
text-align: left ;
white-space: nowrap ;
padding-left: 0 }
/* "booktabs" style (no vertical lines) */
table.docutils.booktabs {
border: 0px;
border-top: 2px solid;
border-bottom: 2px solid;
border-collapse: collapse;
}
table.docutils.booktabs * {
border: 0px;
}
table.docutils.booktabs th {
border-bottom: thin solid;
text-align: left;
}
h1 tt.docutils, h2 tt.docutils, h3 tt.docutils,
h4 tt.docutils, h5 tt.docutils, h6 tt.docutils {
font-size: 100% }
ul.auto-toc {
list-style-type: none }
< / style >
< / head >
< body >
< div class = "document" id = "bi-sql-editor" >
< h1 class = "title" > BI SQL Editor< / h1 >
<!-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!! This file is generated by oca-gen-addon-readme !!
!! changes will be overwritten. !!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2024-10-01 05:53:56 +00:00
!! source digest: sha256:1acb226c184032febd7db6e48ee618c7e5a6899c4685aa78b5b2e7578ebd9ce9
2024-05-03 09:07:13 +00:00
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -->
< p > < a class = "reference external image-reference" href = "https://odoo-community.org/page/development-status" > < img alt = "Beta" src = "https://img.shields.io/badge/maturity-Beta-yellow.png" / > < / a > < a class = "reference external image-reference" href = "http://www.gnu.org/licenses/agpl-3.0-standalone.html" > < img alt = "License: AGPL-3" src = "https://img.shields.io/badge/licence-AGPL--3-blue.png" / > < / a > < a class = "reference external image-reference" href = "https://github.com/OCA/reporting-engine/tree/17.0/bi_sql_editor" > < img alt = "OCA/reporting-engine" src = "https://img.shields.io/badge/github-OCA%2Freporting--engine-lightgray.png?logo=github" / > < / a > < a class = "reference external image-reference" href = "https://translation.odoo-community.org/projects/reporting-engine-17-0/reporting-engine-17-0-bi_sql_editor" > < img alt = "Translate me on Weblate" src = "https://img.shields.io/badge/weblate-Translate%20me-F47D42.png" / > < / a > < a class = "reference external image-reference" href = "https://runboat.odoo-community.org/builds?repo=OCA/reporting-engine&target_branch=17.0" > < img alt = "Try me on Runboat" src = "https://img.shields.io/badge/runboat-Try%20me-875A7B.png" / > < / a > < / p >
< p > This module extends the functionality of reporting, to support creation
of extra custom reports. It allows user to write a custom SQL request.
(Generally, admin users)< / p >
< p > Once written, a new model is generated, and user can map the selected
field with odoo fields. Then user ends the process, creating new menu,
action and graph view.< / p >
< p > Technically, the module create SQL View (or materialized view, if option
is checked). Materialized view duplicates datas, but request are
fastest. If materialized view is enabled, this module will create a cron
task to refresh the data).< / p >
< p > By default, users member of ‘ ’
You can specify extra groups that have the right to access to a specific
view.< / p >
< div class = "section" id = "warning" >
< h1 > Warning< / h1 >
< p > This module is intended for technician people in a company and for Odoo
integrators.< / p >
< p > It requires the user to know SQL syntax and Odoo models.< / p >
< p > If you don’
on a production environment.< / p >
< / div >
< div class = "section" id = "use-cases" >
< h1 > Use Cases< / h1 >
< p > this module is interesting for the following use cases< / p >
< ul class = "simple" >
< li > You want to realize technical SQL requests, that Odoo framework
doesn’
case is if you want to have Sale Orders and PoS Orders datas in a
same table< / li >
< li > You want to customize an Odoo report, removing some useless fields
and adding some custom ones. In that case, you can simply select the
fields of the original report (sale.report model for exemple), and
add your custom fields< / li >
< li > You have a lot of data, and classical SQL Views have very bad
performance. In that case, MATERIALIZED VIEW will be a good solution
to reduce display duration< / li >
< / ul >
< p > < strong > Table of contents< / strong > < / p >
< div class = "contents local topic" id = "contents" >
< ul class = "simple" >
< li > < a class = "reference internal" href = "#configuration" id = "toc-entry-1" > Configuration< / a > < / li >
< li > < a class = "reference internal" href = "#usage" id = "toc-entry-2" > Usage< / a > < / li >
< li > < a class = "reference internal" href = "#bug-tracker" id = "toc-entry-3" > Bug Tracker< / a > < / li >
< li > < a class = "reference internal" href = "#credits" id = "toc-entry-4" > Credits< / a > < / li >
< / ul >
< / div >
< div class = "section" id = "configuration" >
< h2 > < a class = "toc-backref" href = "#toc-entry-1" > Configuration< / a > < / h2 >
< ul >
< li > < p class = "first" > Go to Dashboard / Configuration / SQL Views< / p >
< / li >
< li > < p class = "first" > tip your SQL request< / p >
< p > < img alt = "image1" src = "https://raw.githubusercontent.com/OCA/reporting-engine/17.0/bi_sql_editor/static/description/01_sql_request.png" / > < / p >
< / li >
< li > < p class = "first" > Select the group(s) that could have access to the view< / p >
< p > < img alt = "image2" src = "https://raw.githubusercontent.com/OCA/reporting-engine/17.0/bi_sql_editor/static/description/02_security_access.png" / > < / p >
< / li >
< / ul >
< p > Optionnaly, you can add a domain.< / p >
< p > A tipical domain in a multi company context is to write
< tt class = "docutils literal" > < span class = "pre" > ['|',< / span > ('company_id', < span class = "pre" > '=',< / span > False), ('company_id', 'in', company_ids)]< / tt >
to make reporting depending on the current companies of the user.< / p >
< ul >
< li > < p class = "first" > Click on the button ‘ ’ < / p >
< / li >
< li > < p class = "first" > Once the sql request checked, the module analyses the column of the
view, and propose field mapping. For each field, you can decide to
create an index and set if it will be displayed on the pivot graph as
a column, a row or a measure.< / p >
< p > < img alt = "image3" src = "https://raw.githubusercontent.com/OCA/reporting-engine/17.0/bi_sql_editor/static/description/03_field_mapping.png" / > < / p >
< / li >
< li > < p class = "first" > Click on the button ‘ ’
while, if view is materialized)< / p >
< / li >
< li > < p class = "first" > If it’ < / p >
< blockquote >
< ul class = "simple" >
< li > a cron task is created to refresh the view. You can so define
the frequency of the refresh.< / li >
< li > the size of view (and the indexes is displayed)< / li >
< / ul >
< / blockquote >
< p > < img alt = "image4" src = "https://raw.githubusercontent.com/OCA/reporting-engine/17.0/bi_sql_editor/static/description/04_materialized_view_setting.png" / > < / p >
< / li >
2024-10-01 05:53:56 +00:00
< li > < p class = "first" > Before applying the final step, you will need to add a specific
Parent Menu to use when creating the UI Menu for the report. By
default, it will be set with the < tt class = "docutils literal" > SQL Views< / tt > menu, which can be
changed before creating the UI elements in order to have the report
accessible from a different place within Odoo.< / p >
< / li >
2024-05-03 09:07:13 +00:00
< li > < p class = "first" > Finally, click on ‘ ’
and search view.< / p >
< / li >
< / ul >
< / div >
< div class = "section" id = "usage" >
< h2 > < a class = "toc-backref" href = "#toc-entry-2" > Usage< / a > < / h2 >
< p > To use this module, you need to:< / p >
< ol class = "arabic simple" >
< li > Go to ‘ > SQL Reports’ < / li >
< li > Select the desired report< / li >
< / ol >
<!-- -->
< blockquote >
< img alt = "usage-image1" src = "https://raw.githubusercontent.com/OCA/reporting-engine/17.0/bi_sql_editor/static/description/05_reporting_pivot.png" / > < / blockquote >
< ul class = "simple" >
< li > You can switch to ‘ ’ ‘ ’ < / li >
< / ul >
< / div >
< div class = "section" id = "bug-tracker" >
< h2 > < a class = "toc-backref" href = "#toc-entry-3" > Bug Tracker< / a > < / h2 >
< p > Bugs are tracked on < a class = "reference external" href = "https://github.com/OCA/reporting-engine/issues" > GitHub Issues< / a > .
In case of trouble, please check there if your issue has already been reported.
If you spotted it first, help us to smash it by providing a detailed and welcomed
< a class = "reference external" href = "https://github.com/OCA/reporting-engine/issues/new?body=module:%20bi_sql_editor%0Aversion:%2017.0%0A%0A**Steps%20to%20reproduce**%0A-%20...%0A%0A**Current%20behavior**%0A%0A**Expected%20behavior**" > feedback< / a > .< / p >
< p > Do not contact contributors directly about support or help with technical issues.< / p >
< / div >
< div class = "section" id = "credits" >
< h2 > < a class = "toc-backref" href = "#toc-entry-4" > Credits< / a > < / h2 >
< / div >
< / div >
< div class = "section" id = "authors" >
< h1 > Authors< / h1 >
< ul class = "simple" >
< li > GRAP< / li >
< / ul >
< / div >
< div class = "section" id = "contributors" >
< h1 > Contributors< / h1 >
< ul class = "simple" >
< li > Sylvain LE GAL (< a class = "reference external" href = "https://twitter.com/legalsylvain" > https://twitter.com/legalsylvain< / a > )< / li >
< li > Richard deMeester, WilldooIT (< a class = "reference external" href = "http://www.willdooit.com/" > http://www.willdooit.com/< / a > )< / li >
< li > David James, WilldooIT (< a class = "reference external" href = "http://www.willdooit.com/" > http://www.willdooit.com/< / a > )< / li >
2024-10-01 05:53:56 +00:00
< li > Guillem Casassas < a class = "reference external" href = "mailto:guillem.casassas@forgeflow.com" > guillem.casassas@ forgeflow.com< / a > < / li >
2024-05-03 09:07:13 +00:00
< li > This module is highly inspired by the work of< ul >
< li > Onestein: (< a class = "reference external" href = "http://www.onestein.nl/" > http://www.onestein.nl/< / a > ) Module:
OCA/server-tools/bi_view_editor. Link:
< a class = "reference external" href = "https://github.com/OCA/reporting-engine/tree/9.0/bi_view_editor" > https://github.com/OCA/reporting-engine/tree/9.0/bi_view_editor< / a > < / li >
< li > Anybox: (< a class = "reference external" href = "https://anybox.fr/" > https://anybox.fr/< / a > ) Module :
OCA/server-tools/materialized_sql_view link:
< a class = "reference external" href = "https://github.com/OCA/server-tools/pull/110" > https://github.com/OCA/server-tools/pull/110< / a > < / li >
< li > GRAP, Groupement Régional Alimentaire de Proximité:
(< a class = "reference external" href = "http://www.grap.coop/" > http://www.grap.coop/< / a > ) Module:
grap/odoo-addons-misc/pos_sale_reporting link:
< a class = "reference external" href = "https://github.com/grap/odoo-addons-misc/tree/7.0/pos_sale_reporting" > https://github.com/grap/odoo-addons-misc/tree/7.0/pos_sale_reporting< / a > < / li >
< / ul >
< / li >
< / ul >
< / div >
< div class = "section" id = "maintainers" >
< h1 > Maintainers< / h1 >
< p > This module is maintained by the OCA.< / p >
< a class = "reference external image-reference" href = "https://odoo-community.org" > < img alt = "Odoo Community Association" src = "https://odoo-community.org/logo.png" / > < / a >
< p > OCA, or the Odoo Community Association, is a nonprofit organization whose
mission is to support the collaborative development of Odoo features and
promote its widespread use.< / p >
< p > Current < a class = "reference external" href = "https://odoo-community.org/page/maintainer-role" > maintainer< / a > :< / p >
< p > < a class = "reference external image-reference" href = "https://github.com/legalsylvain" > < img alt = "legalsylvain" src = "https://github.com/legalsylvain.png?size=40px" / > < / a > < / p >
< p > This module is part of the < a class = "reference external" href = "https://github.com/OCA/reporting-engine/tree/17.0/bi_sql_editor" > OCA/reporting-engine< / a > project on GitHub.< / p >
< p > You are welcome to contribute. To learn how please visit < a class = "reference external" href = "https://odoo-community.org/page/Contribute" > https://odoo-community.org/page/Contribute< / a > .< / p >
< / div >
< / div >
< / body >
< / html >
