Sub Reports in Openbravo

Report is one of the key benefit for using ERP application. More enhanced and flexible the reports are, more happy customer is. In Openbravo, we use jasper reports to provide us the flexibility to handle all report generation and rendering. Jasper Report provide us iReport, graphical tool used to design and test jasper based reports. I have provided a simple example of how to add sub-report for an Openbravo Report using iReport.

Use Case:
we will create a sales report that containing 2 dimensions (business partner and group) with 4 measurement (sales count, qty, average unit price, and total sales amount). Sales count is how many basket have been sold for that particular customer and product. Total sales amount is calculated by qty multiply by unit price for each sales record. for summaryzing, both qty and total sales amount will be sum-up, however unit price will be averaged.

Main Report:
We need to create main report which is customer with completed sales order exists:

select a.name as customer, c_bpartner_id
from c_bpartner a
where exists (select 1 from c_order
              where c_order.c_bpartner_id=a.c_bpartner_id
              and c_order.processed='Y'
              and c_order.docstatus not in ('VO')
              and c_order.issotrx='Y')
order by a."name"

Open your iReport, create new from wizard, then put query above to iReport’s query pane, refer to screenshot below:

click next to continue new report wizard until you get report prapared

make some adjustment, you may change report title.

Save and run your report to preview. in this case I save my main report as salesreport1.jrxml.

Adding Sub Report:
The Sub Report can be created by adding the sub report icon as follows:

  1. adjust detail band to get more space. you may set 20px added height for subreport area and 5px for bottom margin. since customer detail in main report have 17px in height, then we need to ajust band detail height to 42. right click on detail band then select band properties. set band height to 42 then click apply. close band properties pop-up.
  2. click subreport icon and draw subreport box
  3. subreport wizard will appear. select new report, then click next.
  4. build sql query for subreport. sql query below use hardcoded business partner id, we will refine latter.
    select d."name" as product_category, sum(a.qtyordered) as qty, sum(a.linenetamt) as salestotal, avg(a.priceactual) as unitprice
    from c_orderline a
    inner join c_order b on b.c_order_id=a.c_order_id
    inner join m_product c on c.m_product_id=a.m_product_id
    inner join m_product_category d on d.m_product_category_id=c.m_product_category_id
    where b.c_bpartner_id='0F76C0C2B6814A7D89D3ADC7303E29CD'
    and b.issotrx='Y'
    and b.processed='Y'
    and b.docstatus not in ('VO')
    group by d."name"
    order by d."name"
    
  5. put query to sub report wizard, then click next.
  6. use all available field, or in another word: bring all field to right side. then click next.
  7. select prefered template. if your main report portrait, then make sure subreport portrait also. select tabular report for this subreport, it’s good to show. then click next and save your subreport.
  8. make some adjustment. make all numeric field align right. change field title from product_category to Product Category. adjust field pattern for numeric field, give them thousand separator and 2 decimal point.
  9. preview your sub report
  10. create new parameter c_bpartner_id, type String, and set default value to 0F76C0C2B6814A7D89D3ADC7303E29CD.
  11. change sub report query to avoid hardcoded business partner ID
    select d."name" as product_category, sum(a.qtyordered) as qty, sum(a.linenetamt) as salestotal, avg(a.priceactual) as unitprice
    from c_orderline a
    inner join c_order b on b.c_order_id=a.c_order_id
    inner join m_product c on c.m_product_id=a.m_product_id
    inner join m_product_category d on d.m_product_category_id=c.m_product_category_id
    where b.c_bpartner_id=$P{c_bpartner_id}
    and b.issotrx='Y'
    and b.processed='Y'
    and b.docstatus not in ('VO')
    group by d."name"
    order by d."name"
    
  12. preview once again, just to make sure query parameter works.
  13. back to main report, adjust sub report box size. set sub report position to left=0, top=17, height=20. set width equal to main report width, in this case 535px.
  14. run your report. you will get main report with subreport inserted. however, information inside sub report not tie up with main report. no problem, just make sure all thing works for now.
  15. now we need to pass business partner ID from main report to sub report. right click on sub report box, select properties. sub report pop-up will appear, then select Subreport (other) tab.
  16. click add. put sub report parameter name c_bpartner_id (remember, we set 1 parameter on sub report, named c_bpartner_id). then set default value expression $F{customer}. This will tell jasper report to pass value of field customer from main report to subreport’s parameter named c_bpartner_id. This will make our report dynamic, for each row in main report will have different busisness partner id from row to row, and it’s affacted our sub report for each main report’s row.
    The sub-report can be added by drag and dropping the highlighted portion in the above screen shot. It will open the sub report wizard.
  17. preview once again, now you will see the report work properly, both main and sub report.

 

now your report ready, but it’s ready for iReport, but not yet for Openbravo. why? your main report declare subreport full path based on your current development machine. when you deploy this report, it will fail to lookup subreport file since full path to subreport file may different with your development machine. to cope this, openbravo give built-in parameter named BASE_DESIGN. when openbravo render your jasper report, openbravo assign folder <tomcat_home>/webapps/<openbravo_context_name>/src-loc/design. of course this is run time variable, not design time. for desin time, it’s equal to your module’s src folder. for my case, according to openbravo modularity convention, this report stored in <openbravo_home>/modules/org.wirabumi.gen.oez/src/org/wirabumi/gen/oez/ad_report. those, BASE_DESIGN will equal to <openbravo_home>/modules/org.wirabumi.gen.oez/src. now we are ready to make adjustment to our main report.

  1. create new parameter BASE_DESIGN, set default value to <openbravo_home>/modules/org.wirabumi.gen.oez/src. makse sure you change <openbravo_home> to your openbravo installation folder.
  2. right click sub report box, select properties. on sub report pop-up, open tab Subreport (other). change value of subreport expression to your full path to sub report file, in help of BASE_DESIGN. for my case, this value will set to $P{BASE_DESIGN} + “/org/wirabumi/gen/oez/ad_report/salesreport1_subreport0.jasper”.
  3. preview your main report, to make sure this new parameter (BASE_DESIGN) works in development environment.

now we are ready to deploy report to openbravo. make sure 2 required report file (salesreport1.jrxml and salesreport1_subreport0.jasper) already exists in your module (sub)folder. in my case it should exists at <openbravo_home>/modules/org.wirabumi.gen.oez/src/org/wirabumi/gen/oez/ad_report.

Declare your report on openbravo

  1. login to openbravo as system administator and open Report and Process window. create new record as screesnhot below. make sure you NOT tick report check box, but tick jasper report check box. for JR Template name, in my case, I put @basedesign@/org/wirabumi/gen/oez/ad_report/salesreport1.jrxml. openbravo will change @basedesign@ into <tomcat_home>/webapps/<openbravo_context_name>/src-loc/design when render report. this part /org/wirabumi/gen/oez/ad_report/ based on my source code organization a.k.a javapackage. and this part salesreport1.jrxml is my main report file name. you need to change to your path and javapackage accordingly.
  2. open parameter sub tab, create a new record as screenshot below. this parameter used to tell openbravo to render report output format based on supplied parameter.
  3. open menu window, create a new record to make your report accessible. Even this is a report, since openbravo need to execute report utility java class, this menu record should declared as a process instead a report. make sure action set to process instead a report.
  4. still in menu window, chanage from grid mode to tree mode. then arrange menu layout, so this report become a child of analysis tools of sales management menu.

Compile and run report

  1. delete build, srcAD, and src-gen folder
  2. run ant task to compile: ant compile.complete.deploy
  3. restart tomcat
  4. login as business user, open menu Sales Report by Customer and Product Group
  5. select your preferred format, then click ok.
  6. wohaaaa, this is your report

 

to continue on iReport, you head to how to add image to openbravo report.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s