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

OpenERP: Module showing in Windows installation but not in Linux installation

logo_openerpA few days back I was working on an OpenERP project. I had to develop a module for it. I created a module and it was working fine is my Windows machine. When I uploaded the module to Linux installation, I could not find the module in the module list. I double checked if it is uploaded in correct directory or not… it’s in the right place. Tried with updating the module list several times… no luck!

Then one thought came in my mind. In one of my previous projects I faced an issue (though that was not an OpenERP project). It was related to end of line (EOL) character. I checked the module files and found that it was using Windows EOL characters CRLF. I changed the character to Unix EOL character LF. Uploaded the files again on Linux server, restarted server, updated module list and found the module in the list.

How I changed the character?

I have used Notepad++ to do this task. You may follow the following steps to do this:

  • Download and install Notepad++ if you do not have it.
  • Open the module file.
  • To view the EOL character go to View menu > Show symbol > Show End of Line.
  • If the EOl is CRLF then proceed to next steps. If the character is LF then no need to change.
  • To change the EOL to Unix format, go to Edit > EOL Conversion > Unix format. You will see the EOL changed from CRLF to LF.

That’s it!

Share
blog