OpenERP 7: Creating report using SQL query

OpenERP allows creating different reports easily using their interface. If you want to use SQL queries to work as back-end of your report and show it like other built in reports under a menu, here is how you can do.

To create a report backed by SQL query we will go through the following steps:

  1. Create a custom model,
  2. Create a Tree View,
  3. Create a Window Action and
  4. Create a menu entry.

Create a custom model

In our example we are going to create a new module My Report which will have the model we are going to use for creating the report. In the module folder create a file my_report.py. In the file we are going to define our model my_report_model.

In my_report.py:

from openerp.osv import fields,osv
from openerp import tools
 
class my_report_model(osv.osv):
    _name = "my.report.model"
    _description = "My Report"
    _auto = False
    _columns = {
        'child_name': fields.char('Analytic Account', size=128, readonly=True),
        'parent_name': fields.char('Analytic Account - Parent', size=128, readonly=True),
        'date_from': fields.date('Start Date', readonly=True),
        'date_to': fields.date('End Date', readonly=True),
        'planned_amount': fields.float('Planned Amount', readonly=True)
    }
    _order = 'parent_name asc, child_name asc'
 
    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'my_report_model')
        cr.execute("""
            CREATE OR REPLACE VIEW my_report_model AS (
                SELECT cbl.analytic_account_id AS id,
                    aaap.name AS parent_name,
                    aaa.name AS child_name,
                    cbl.date_from,
                    cbl.date_to,
                    cbl.planned_amount
                FROM crossovered_budget_lines cbl
                INNER JOIN account_analytic_account aaa ON cbl.analytic_account_id = aaa.id
                LEFT OUTER JOIN account_analytic_account aaap ON aaa.parent_id = aaap.id
            )
        """)
 
my_report_model()

In this model we have defined the fields we are going to use in the report. In the init() method we have defined the SQL View which we are going to use for the report. The view name should match the class name defined in _name attribute (replace dot with underscore). Also the view columns should match the columns defined in _columns attribute. Save the model and install the newly created module. The my.report.model object will now be available.

Create a View

We will now create a tree view to use in the report. Follow these steps to create the tree view:

  1. Go to Settings > Technical > User Interface > Views.
  2. Click on the Create button.
  3. Enter my.report.tree.view as View Name (you may use name of your choice).
  4. Enter the name of the model (declared in the model using _name attribute) in Object field.
  5. Enter the XML for the tree view in Architecture field.
    <?xml version="1.0"?>
    <tree string="My Report" create="false">
      <field name="id" invisible="1"/>
      <field name="analytic_account_name" string="Analytic Account"/>
      <field name="budget_name"/>
      <field name="date_from"/>
      <field name="date_to"/>
      <field name="planned_amount" sum="Planned Amount"/>
    </tree>
  6. Save the View.

tree-view

Create a Window Action

Now we shall create a Window Action which will show the report. To create the Window Action follow the following steps:

  1. Go to Settings > Technical > Actions > Window Actions.
  2. Click on the Create button.
  3. Give an Action Name, in our case My Report.
  4. In Object field enter the model name, same as name we entered in the tree view.
  5. In View Mode field enter tree, form if you want to show both tree and form view. If you don’t want to allow the user to edit the entry the use only tree. In our case we have used tree.
  6. From the View Ref. dropdown select the tree view we have created earlier.
  7. Save the Window Action.

window-action

Create a menu entry

Now we have the report ready. But we need a menu item to allow the users to view the report. To create a menu item for this report/Window Action, do the following:

  1. Go to Settings > Technical > User Interface > Menu Items.
  2. Click on the Create button.
  3. Enter Name of the menu item, in our case it is My Report.
  4. We have selected Reporting/Accounting as the Parent Menu.
  5. From the Action dropdown we have selected ir.actions.act_window and then the selected the Window Action we have created earlier.
  6. Save the menu.
    menu-entry
  7. Refresh the window and the new menu item will be available under Reporting > Accounting menu.

report

So, the report is ready. Now if we want to define default fields for searching we shall have to create a search view and tie it with the Window Action. Follow these steps to create the Search View:

  1. Go to the Window Action we have created.
  2. Click on the Search View Ref. dropdown and click on Create and Edit… link. The new View form will be opened. The Search View Ref. field requires a view of type Search. Don’t worry if the new View form shows View Type as Tree. It will change on save.
  3. Give a name like my.report.search.view.
  4. Enter the class name we have created in Object field.
  5. Enter the XML in Architecture field as follows with the fields to be searched on by default.
    <?xml version="1.0"?>
    <search>
        <field name="analytic_account_name" string="Analytic Account"/>
        <field name="budget_name" string="Budget"/>
    </search>
  6. Save the view and then save the Window Action.

Now if you type anything in the search box, options will be given to search in fields you have entered in Search view.

search-option

Hope this will give you an idea on how we may add a report backed by SQL queries. If you have any other ideas/suggestions then share with us.

Share

30 thoughts on “OpenERP 7: Creating report using SQL query”

  1. help me please i have the error

    An error has occurred during validation / the field (s) res_model, src_model: not valid in the Model definition of the action.

  2. Thanks for sharing.
    I do the same with example, but the tree view can not display correctly. For example I use PostgreSql Maestro to execute the query, I have 600 records. But the tree view only show 2 records.
    Can you help me?

  3. Thanks for answer for my problem. In the tree view, It show the number of record, when I click to it, I can select the number of record (ex: 80, 200,500,2000,unlimited). When i choose unlimited, It can not show anymore.
    About limit field’s value in the windows action: it is default (80).
    The code is:
    .py

    class x_pttstl_report_parent(osv.osv):
        _name = "x_pttstl_report_parent"
        _description = "tree map"
        _auto = False
        _columns = {
            'tuyennr_parent': fields.char('parent', size=128, readonly=True),
            'tendd': fields.char('child', size=128, readonly=True),
           }
        _order = 'tuyennr_parent,tendd'
        def init(self, cr):
            tools.drop_view_if_exists(cr, 'x_pttstl_report_parent')
            cr.execute(""" create or replace view x_pttstl_report_parent AS (
                   select 
                       nrprt.id as id,
                       nrprt.x_tendd_tuyennr_parent as tuyennr_parent,
                       nr.x_tendd as tendd
                    from 
                        x_pttstl_tuyennr nr
                    join 
                        x_pttstl_tuyennr_parent nrprt on (nrprt.id = nr.x_tuyennr_tuyennr_parent_id)
                    group by 
                        nrprt.id,tendd_tuyennr_parent,tendd
                        )
            """)
    x_pttstl_report_parent()

    Tree view:

    <record model="ir.ui.view" id="x_pttstl_report_parent_tree_view">
        <field name="name">x_pttstl_report_parent.tree</field>
        <field name="model">x_pttstl_report_parent</field>			
        <field name="arch" type="xml">
            <tree string="Tree map" version="7.0">								
                <field name="tuyennr_parent"/>
                <field name="tendd"/>
            </tree>
        </field>
    </record>
     
    <record model="ir.ui.view" id="x_pttstl_report_parent_form_view">
        <field name="name">x_pttstl_report_parent.form</field>
        <field name="model">x_pttstl_report_parent</field>			
        <field name="arch" type="xml">
            <tree string="So do cay" version="7.0">								
                <field name="tuyennr_parent"/>
                <field name="tendd"/>
            </tree>
        </field>
    </record>

    Action:

    <record model="ir.actions.act_window" id="action_pttstl_report_parent">
    	<field name="name">Tree map</field>
    	<field name="res_model">x_pttstl_report_parent</field>
    </record>

    menu:

    <menuitem name="Map" parent="pttstl_menu" id="vanhanh_menu"/>   
    <menuitem name="Tree" parent="vanhanh_menu" id="tree_menu" action="action_pttstl_report_parent"/>

    Please help me!

  4. I don’t see anything wrong with the code. Can you please check after removing the GROUP BY clause from the query?

    Another important thing if you make any change to the query, run the CREATE OR REPLACE statement in the database or uninstall/install the module to reflect the change to database.

  5. Ok. Thanks. I’ve fixed this error. I am wrong with “nrprt.id as id”. I change “nrprt.id as id” with “nr.id as id”, it’s OK.
    I’ve new problem. The problem is:There is not argument to pass value to sql query. If I have to pass value to where clause, how can i do it? Please help me?

  6. I have a problem with this solution.
    OpenERP error:

    ProgrammingError: “immobiliare_richiesteofferte” is not a view
    HINT: Use DROP TABLE to remove a table.

    And a table with name “immobiliare_richiesteofferte” is created into DB by this code.

    Why OpenERP create a table when is a view ?
    The “_model” in the name is a special key ?

    Thanks.

  7. Yes you are right. You will have to set the _auto property to False in your model.

    _auto = False

    OpenERP maps a model with a table with the same name and it creates the table when installing the module. If _auto is set to False then it does not create corresponding table. So, make sure you have the _auto variable set to False in your model.

  8. Great !!! It’s work !!!
    Thanks…

    I use a view to make a join beetwin request and offers in real estate management system.

    Your is the best example of use a view in OpenERP in all Internet 😉

    F.

  9. Francesco, how does you fix the error you posted previously because I am getting the same error
    Now I get “OpenERP client Error: Uncaught SyntaxError: Unexpected token F” when click on menu item that open the view.

    Thanks

  10. Hello.
    We have an assignment on OpenERP and we are new to it.
    We’re having some issues.
    Where to save the model and how install the newly created module?
    Please, help us.
    Thank you.

  11. hello sanybody will you please explain me how to use our own formulas and expression in openerp report designer to customize existing module reports………..i know how to customize reports in libreoffice but not getting how to use our own formulas to make changes…???

  12. Betel: The unexpected (OpenERP client Error: Uncaught SyntaxError: Unexpected token F”) is because of the capital F in the view.
    create=”False” should be replaced with create=”false”.

  13. Great post, I’m about to get it done, but I’m getting an OpeERP server error when I select the menu that I’ve created for my view …

    The error says:
    ProgrammingError: column sge_report_model.id does not exist
    LINE 1: SELECT “sge_report_model”.id FROM “sge_report_model” ORDER B…

    ‘sge_report_model’ is the custom model I’ve created based on a SQL query (SQL View).
    It has two columns (), but no ‘id’ and that’s apparently where the error comes from. Below you can find the python code for my module (sge_report.py). Why do I need an ‘id’ field in my model and is there a way to generate it automatically?
    Many thanks in advance,
    Rod.

    from openerp.osv import fields,osv
    from openerp import tools
     
    class sge_report_model(osv.osv):
        _name = "sge.report.model"
        _description = "SGE Report"
        _auto = False
        _columns = {
            'nombre_pais': fields.char('Nombre del Pais', size=30, readonly=True),
            'credito_total': fields.float('Credito total concedido', readonly=True)
        }
        _order = 'nombre_pais asc, credito_total asc'
     
        def init(self, cr):
            tools.sql.drop_view_if_exists(cr, 'my_report_model')
            cr.execute("""
                CREATE OR REPLACE VIEW sge_report_model AS (
                    SELECT C.name AS nombre_pais,
                           SUM(P.credit_limit) AS credito_total
                    FROM res_partner P INNER JOIN res_country C ON P.country_id = C.id
                    GROUP BY C.name
                )
            """)
     
    sge_report_model()
  14. Does this work in openerp 6.1???? I’m new to openerp and i’m trying to learn how to create reports which seems very very difficult. Hope i could get some help from you friends

  15. Thank you so much for the example. Could I suggest you make correction from False to false in Create View so that the “F” issue will no longer an issue.

  16. Great article Mohammad. I was able to create a few reports but what I was not able to get working is the export feature. When y select a few rows and clic on More->Export there are no columns available to select. I tried a few things (I think it has something to do with the fields_get or get_fields function in the model) but I were not able to get it working since I don’t have much expertise with python.

    Do you know if it is easy to add the export feature to this kind of reports?

    And on another topic, it would also be great if you know how to support “Group by” on the report (different grouping available on search menu).

    Thanks

  17. Great post, thank you. I got it working but the list doesn’t allow ordering. I click and does nothing.
    Moreover, the search section is not shown. I declared it but shows nothing:

    Search
    mc.report.test.model

  18. Hey Rod,
    I know, it’s 2015, but I’ve resolved the problem of missed id, simply:

            cr.execute("""
                CREATE OR REPLACE VIEW contract_use_report AS (                SELECT min(c.id) AS id , c.use as use, count(*) as total_contracts from contract c group by c.use,  c.id )""")

    So, with min(c.id) we have the solution, the report works.

    Thanks Mohammad Sajjad Hossain For your blog, it helped me !

Leave a Reply

Your email address will not be published. Required fields are marked *