How to create a SQL report getting ids from OpenErp

Posted on February 24, 2014 at 1:44am

Hi,

 

I have a OpenErp installation, JasperReports working well and Ireport 3.7.5. I'm trying to pass the ids of the products selected to the report. I want to create a dynamic SQL Report.

In the report I have the next parameters :

ids, PIDS, product_ids and LIDS.

In OpenErp I have the next code : 

def do_catalog(self, cr, uid, ids, context={}):        product_ids = context.get('active_ids',False)                 catalog_builder = self.browse(cr,uid,ids[0])        data = {           'model': 'product.product',           'ids': product_ids ,           }        return {            'type': 'ir.actions.report.xml',            'report_name': 'pruebas',            'datas': data,            'parameters': {                               'product_ids':product_ids,            },            'nodestroy': False        }

I have try lots of SQL's , now I have this :

select * from product_product where $X{ IN ,product_product.id,product_ids} .

It returns me all the data in produts_prodcts not the ones I have selected.

I don't know what I'm doing bad, I had read all the information I have find.

Can anyone help me?

Regards

Attachments: 
Binary Data productreport.jrxml
iReport Designer
JasperReports® Library
yamaksy's picture
yamaksy
52
Joined: Feb 24 2014 - 1:11am
Last seen: 8 years 11 months ago

2 Answers:

Posted on February 25, 2014 at 1:37am

After reading a lot, I get the answer and I will post it :

In the function called by the action you have to pass the ids to the report like this.
 

def do_catalog(self, cr, uid, ids, context={}):
        product_ids = context.get('active_ids',False)
 
        catalog_builder = self.browse(cr,uid,ids[0])
        data = {
           'model': 'product.product',
           'ids': product_ids,
           }
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'pruebas',
            'datas': data,
        }

aIn the report you have to have a parameter called the same but in UPPERCASE. In this case IDS, and the class have to be java.lang.Object . You sould have another parameter called as you want to convert that object to collection :

$P{IDS}.toList()

The SQL should look like this :

SELECT * FROM product_product WHERE $X{ IN ,product_product.id,PIDS}

This works for me .

Thanks for helping.

yamaksy's picture
yamaksy
52
Joined: Feb 24 2014 - 1:11am
Last seen: 8 years 11 months ago
Posted on February 24, 2014 at 8:19am

Yamarksy,

At first glance it looks like your query is correct (note: i do not have a lot of experience with OpenERP).

Your report is likely showing all the data because the parameter prodcut_id is null. Look how $X behaves in building the query here http://jasperreports.sourceforge.net/sample.reference/query/#query (scroll down to Built-in SQL Clause Functions )

Can you attache your JRXML report? That will help me better understand where the problem may be.

 

marianol's picture
marianol
15800
Joined: Sep 13 2011 - 8:04am
Last seen: 4 years 6 months ago

I have attache the JRXML report, but I think my problem is earlier, when I try to pass the parameters from OpenErp to the report.
Thanks for your help.

yamaksy - 9 years 1 month ago
add comment
Feedback