Custom reports from OER with BI Publisher

Resources:

  1. My other posts on OER and BIP installation and configuration
  2. http://www.slideshare.net/sreenisetty/oer-reports-and-obi-publisher

 

Configuration directories

OER reports’ configuration directory:

[/oracle]/fmwhome/user_projects/applications/oer_domain/applications/oer_11.1.1.5.0/oer-app/WEB-INF/config/reports

 

BI Publisher reports’ configuration directory:

[/oracle/bipubhome]/xmlpserver.war/XMLP/Reports/OER

OER configuration

OER holds 3 types of files:

  • “jod” – xml file describing structure of the report. I’m not using “jod” files in this document
  • “toc” – xml file describing webpart on OER’s Reports page. “toc” files can be customized to meet specific requirements
  • “xml” – files describing parameters of particular reports, accessible via “toc” files

BI Publisher (BIP) configuration

BIP holds two types of files:

  • “rtf” – template of the document in rtf format
  • “xdo” – definition of report’s data and parameters. “xdo” file stores definitions of the SQL statements, used to retrieve data from the database

RTF template commands

 

<?if:TOTAL_PROJECTS !=0?>początek ifa

<?xdofx:decode(P_ORDER_BY,’Tphr’,’Total Project Hours’,’Tprr’,’Total Project Hours – reverse’)?>tajemnicze decode

<?for-each:PROJECTS_GROUP?>poczatek foreach

<?USAGE_TYPE?>wyswietlam wartosc

<?end for-each?>koniec foreach

<?end if?>koniec ifa

Database configuration

Database structure

Retrieving values from XML data

Part of the job is to retrieve values from XML data located in ASSETXML table. Query can look like this:

select

x.ASSETID as AssetId

,a.name as AssetName

,EXTRACTVALUE (xmltype.createxml(Blob2Clob(x.xml)),’//custom-data/documentation/document[1]/document-name[1]’) as AssetDocumentation

,length(XML)

,XML

from assets a, ASSETXML x

where a.id = x.assetid

 

where //custom-data/documentation/document/document-name’ is an XPath.

Please note that ExtractValue() return only single value. In case given xPath returns list of variables, you need to specify elements of the list to be returned, e.g. the following returns only first occurrences: //custom-data/documentation/document[1]/document-name[1]

Since XML is of BLOB type, and EXTRACTVALUE takes text as an input, BLOB has to be converted to CLOB, using the following function:

CREATE OR REPLACE FUNCTION Blob2Clob(plob IN BLOB) RETURN CLOB IS

lclob_Result CLOB := ‘X’;

l_dest_offsset INTEGER := 1;

l_src_offsset INTEGER := 1;

l_lang_context INTEGER := dbms_lob.default_lang_ctx;

l_warning INTEGER;

BEGIN

IF plob IS NOT NULL

AND LENGTH(plob) > 0

THEN

dbms_lob.converttoclob(dest_lob => lclob_Result

,src_blob => plob

,amount => dbms_lob.lobmaxsize

,dest_offset => l_dest_offsset

,src_offset => l_src_offsset

,blob_csid => dbms_lob.default_csid

,lang_context => l_lang_context

,warning => l_warning);

IF l_warning != 0

THEN

dbms_output.put_line(‘Function blob_to_clob warning:’ || l_warning);

RETURN NULL;

END IF;

RETURN(lclob_Result);

ELSE

RETURN NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(‘Function blob_to_clob error:’ || SQLCODE);

RETURN NULL;

END Blob2Clob;