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

34 Comments

  • By Priyanka, July 12, 2013 @ 2:30 pm

    thanks………..
    its worked..
    can u please let us know how to create pdf reports from openerp

    thanks.. 🙂

  • By Mohammad Sajjad Hossain, July 17, 2013 @ 3:44 pm

    You may create PDF reports with the help of Openoffice Report Designer plugin. This post may give you an idea – http://www.zbeanztech.com/blog/openerp-openoffice-report-designer.

  • By walid, July 26, 2013 @ 3:21 pm

    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.

  • By Mohammad Sajjad Hossain, August 22, 2013 @ 9:12 am

    Please check if the fields you have used in the views exists in the model or not.

  • By David, August 28, 2013 @ 1:59 pm

    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?

  • By Mohammad Sajjad Hossain, August 29, 2013 @ 2:02 pm

    Check the Limit field’s value in the window action.

    Share more details about what you have done.

  • By David, August 29, 2013 @ 11:40 pm

    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!

  • By Mohammad Sajjad Hossain, August 30, 2013 @ 10:23 am

    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.

  • By David, September 3, 2013 @ 7:26 pm

    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?

  • By Francesco, September 26, 2013 @ 10:25 am

    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.

  • By Francesco, September 26, 2013 @ 10:35 am

    Ok the way to tell OpenERP not create the table is “_auto = False”

    F.

  • By Mohammad Sajjad Hossain, September 26, 2013 @ 10:39 am

    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.

  • By Francesco, September 27, 2013 @ 10:22 am

    Now I get “OpenERP client Error: Uncaught SyntaxError: Unexpected token F” when click on menu item that open the view.

    I validate the xml of the view and the action bat the view not open and get the error.
    OpenERP, in the error form, give me the URL of the page before to the click on the menu item:

    http://localhost:8069/?db=kp2#view_type=kanban&model=immobiliare.immobile&menu_id=300&action=369:1

  • By Mohammad Sajjad Hossain, September 27, 2013 @ 4:29 pm

    It is very hard to tell where the problem is without seeing your implementation. You may check the XMLs again to see if ‘F’ has been entered anywhere mistakenly, may be in tags.

  • By Francesco, September 28, 2013 @ 1:39 pm

    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.

  • By Betel, October 2, 2013 @ 6:27 pm

    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

  • By Francesco, October 7, 2013 @ 3:39 pm

    Betel: in mine view I’had specify the wrog model to the view.

    F.

  • By Katrina, November 4, 2013 @ 6:19 pm

    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.

  • By sourav rajak, November 27, 2013 @ 6:52 pm

    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…???

  • By nyeri, December 18, 2013 @ 1:19 pm

    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”.

  • By Rakhi, December 20, 2013 @ 12:36 pm

    In view mode we can select both tree and form. But cannot visible the form view of the model..pls reply….

  • By Mohammad Sajjad Hossain, December 20, 2013 @ 1:14 pm

    Sorry, did not understand your question. Can you please tell in details?

  • By Rod, December 30, 2013 @ 9:52 pm

    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()
  • By priya, January 13, 2014 @ 4:18 pm

    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

  • By John C, March 20, 2014 @ 9:20 am

    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.

  • By Rodrigo, September 13, 2014 @ 8:45 pm

    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

  • By Gonzalo, April 22, 2015 @ 5:32 pm

    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

  • By Roberta, April 29, 2015 @ 9:28 pm

    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 !

  • By Sombat Charnchaleo, July 7, 2015 @ 4:55 am

    The XML sections in the article can not be read. Please help.

  • By Mohammad Sajjad Hossain, July 31, 2015 @ 2:17 pm

    The post has been updated with the missing XML sections.

  • By maxime, October 24, 2017 @ 4:13 pm

    Hello I am Maxime AKEBOUE I am a novice in odoo.
    I have a problem. in the postponement of the tab “activity tracking” precisely “sales analysis” of “point of sale” the total pri field does not take into account the total discount. So I go to the model “pos_order_report.py” to modify the statement <> by <> of the request. I redémer the server but no change. I even entered a bad code in the request but no change. help me

  • By Javed Shoukat, May 31, 2018 @ 10:50 pm

    Hi all,
    I want to export serial number, Students Names, Father Names, Mobile Numbers, Emails, Course, Batch, invoice total, amounts paid with dates, due amount, etc. Any help plz?
    Using Select ()
    Or
    I need cr. execute()

  • By Gedeon Ilunga, July 25, 2018 @ 8:48 pm

    Hey Mohammad Sajjad Hossain!
    i am sorry its 2018 but im developing in openerp in the pass 6 months now… i have a problem i want to print a report but before printing it i want to check two fields if the match then print just to retrieve the correct report ( not sure if i have to do a SELECTquery and use that or just give a IF statement ) PLEASE HELP Mohammad.

    thanks Gedeon.

  • By Mario, March 8, 2019 @ 4:43 pm

    Hello every one,
    can you help me please!

    how to create a report pdf on odoo 10 using select query in the stock.quant and put the result in the table on pdf,

    please please , help me!!

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment

blog