Design BIP Report on the fly...

Have you think of creating BIP report dynamically? Data Processor class has many functionalities, which we do not use in general. Lets try some of these functionalities to generate BIP report on the FLY.

DataProcessor class is part of BIP core APIs. Along with data extraction, it also generates RTF layout template, Data Template, XML schema and the BIP report (.xdo) for a given SQL statement or an existing Data Template.

Lets try following peace of code....

public static void bipOnTheFly()
{
String sqlQuery = "select * from emp where deptno=:p_deptno");
Connection connection=null;
try {
connection = getConnection("host:port:sid","userid","passwd");
Hashtable parameter = new Hashtable();

DataProcessor dp = new DataProcessor();
dp.setConnection(connection);
dp.setSql(sqlQuery);
ArrayList paras = dp.getParameters();
Iterator it = paras.iterator();

//Setting parameter value//
while (it.hasNext())
{
Parameter p = (Parameter) it.next();
if (p.getName().equalsIgnoreCase("p_deptno"))
p.setValue("20");
}

//Generate RTF Layout//
dp.writeDefaultLayout("c:\\sample\\employee.rtf");
//Generate XML Schema//
dp.writeXMLSchema("c:\\sample\\employee.xsd");
//Generate BIP Report (.xdo)//
//dp.WriteXDOReport("DataSourceRef","Layout Template","report path","Description","Report Title");
dp.WriteXDOReport("ora10g","employee.rtf","c:\\sample\\employee.xdo","Employee report","Employee Details");

//Generate Sample Data//
dp.setOutput("c:\\sample\\employee_data.xml");

if (paras != null && !paras.isEmpty())
dp.setParameters(paras);

XMLDocument document = (XMLDocument) dp.getDataTemplate();

//Generate data Template//
File outFile = new File("c:\\sample\\employee_dataTemplate.xml");
FileOutputStream outStream = new FileOutputStream(outFile);
document.print(outStream);
dp.processData();

} catch (Exception e)
{ e.printStackTrace(); }
finally
{
try
{ connection.close();}
catch (Exception e)
{ e.printStackTrace(); } } }


The above code will generate RTF template and BIP report. Along with these two components; it also generates sample xml data file, xml schema and Data Template.

RTF Template (Layout)


BIPText1.jpg

BIP Report (.xdo)



<?xml version = '1.0' encoding = 'UTF-8'?>
<report xmlns="http://xmlns.oracle.com/oxp/xmlp"
defaultDataSourceRef="ora10g" version="1.1">
<title >Employee Details</title>
<description >Employee report</description>
<properties />
<dataModel defaultDataSet="main-query" >
<dataSet id="main-query">
<sql dataSourceRef="ora10g" ><![CDATA[select * from emp
where deptno=:p_deptno]]></sql>
<input id="p_deptno" value="${p_deptno}"/>
</dataSet>
</dataModel>
<parameters >
<parameter id="p_deptno" defaultValue="" dataType="varchar2">
<input label="p_deptno"/>
</parameter>
</parameters>
<templates default="employee" >
<template label="employee" type="rtf" url="employee.rtf"/>
</templates>
</report>

Note: With some of the XDK Version, BIP report might have extra null "xmlns=""" attribute. Please remove these attributes with the help of any text editor.

Upload the BIP Report to BIP server and upload the associated Layout template as well. Make sure "Data Source Ref" defined in the BIP report is present in BIP Server.


BIPText1.jpg


uploadRTF.jpg

Upload the rtf layout template.


BIPText1.jpg

Test the report with View option.

Preview.jpg

Lets try the same for Master->Detail report using SQL Cursor.

Sql Query :

Select deptno,dname,loc,cursor (select * from emp where emp.deptno=dept.deptno)
employee from dept where dept.deptno= nvl(:p_deptno,dept.deptno)

This will generate the RTF Layout with Dept-> Employee master details relationship.


MasterDetailRTF.jpg

Note: There is bug in current code, which mark the detail group as "EMPLOYEE" instead of EMPLOYEE_ROW. Please replace the "EMPLOYEE" with "EMPLOYEE_ROW" before uploading the layout template

FixGroupBug.jpg


Upload the BIP Report and RTF Template to BIP server and test it using View option

MasterDetailReport.jpg

The manual upload could be avoided by using APIs or web services to directly seed the report, this way the whole process could be on the FLY.


Comments:

Hi, Is there a way to allow BI report parameter in XDO to accept null value?

Posted by Jay Pavia on May 11, 2010 at 01:47 PM PDT #

Hi Is it possible to use this with Oracle Forms? Currenty, I am using PublicReportService web service to pass values for template, format, parameter values etc. But I want to pass SQL statement and parameters from my application (Oracle Forms) dynamically without specifying these in BIP. Can you please guide? Thanks and kind regards, Aparna

Posted by Aparna on August 03, 2010 at 07:43 PM PDT #

Hi Aparna, In that case, you need not to use BIP server at all. Just use the BI Publisher core APIs, pass the SQL query and parameters and get the default PDF output. I am not sure that what your are looking for. Please describe your requiremnt in details. Thanks, Ashish

Posted by ashish.shrivastava on August 04, 2010 at 01:55 PM PDT #

Hi Ashish, Excellent post - thanks for the info! Will speed up development time for simple reports. Regards, Gareth

Posted by Gareth Roberts on May 17, 2011 at 06:34 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today