Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to create a string formula in BI Publisher from joined Data Sets

Summary
How to create a string formula in BI Publisher from joined Data Sets
Content
In BI Publisher I have a Data Model with Data Sets from different systems (Oracle & Sybase) that are joined via elements.
Is there a way within BI Publisher to create a formula that includes elements from both datasets?
I want to check if a element from the Oracle database is null, use an element in the Sybase database.
Answers
-
I am not creating an RTF template, this will be an interactive report that will be exported to Excel.
Columns in Horizontal Layout:
"tc_cde"
"DP_rid"
"last_nme"
"first_nm"
"addr1"
"addr2"
"cty"
"st"
"zip"
"recip_lang"
"age"
"phys_first_nme"
"phys_last_nme"
"ctr_nme"
"tcc_first_nme" [if "TC_COORD_FIRST_NAME" is null then "tcc_polite_first_nme" else "TC_COORD_FIRST_NAME"]
"tcc_last_nme" [if "TC_COORD_LAST_NAME" is null then "tcc_polite_last_nme" else "TC_COORD_LAST_NAME"]
"tcc_phone_nbr"
Below is my xml:
<link name="dl-4570" parentGroup="G_1" parentColumn="DP_rid" childQuery="trx_recipients" childColumn="TR.DP_RID" childColumnAlias="DP_RID"/>
<output rootName="DATA_DS" uniqueRowName="false">
<nodeList name="data-structure">
<dataStructure tagName="DATA_DS">
<group name="G_1" label="G_1" source="pt_formal_report">
<element name="tc_cde" value="tc_cde" label="tc_cde" dataType="xsd:integer" breakOrder="" fieldOrder="1"/>
<element name="DP_rid" value="DP_rid" label="DP_rid" dataType="xsd:double" breakOrder="" fieldOrder="2"/>
<element name="last_nme" value="last_nme" label="last_nme" dataType="xsd:string" breakOrder="" fieldOrder="3"/>
<element name="first_nm" value="first_nm" label="first_nm" dataType="xsd:string" breakOrder="" fieldOrder="4"/>
<element name="addr1" value="addr1" label="addr1" dataType="xsd:string" breakOrder="" fieldOrder="5"/>
<element name="addr2" value="addr2" label="addr2" dataType="xsd:string" breakOrder="" fieldOrder="6"/>
<element name="cty" value="cty" label="cty" dataType="xsd:string" breakOrder="" fieldOrder="7"/>
<element name="st" value="st" label="st" dataType="xsd:string" breakOrder="" fieldOrder="8"/>
<element name="zip" value="zip" label="zip" dataType="xsd:string" breakOrder="" fieldOrder="9"/>
<element name="recip_lang" value="recip_lang" label="recip_lang" dataType="xsd:string" breakOrder="" fieldOrder="19"/>
<element name="age" value="age" label="age" dataType="xsd:string" breakOrder="" fieldOrder="20"/>
<element name="phys_first_nme" value="phys_first_nme" label="phys_first_nme" dataType="xsd:string" breakOrder="" fieldOrder="10"/>
<element name="phys_last_nme" value="phys_last_nme" label="phys_last_nme" dataType="xsd:string" breakOrder="" fieldOrder="11"/>
<element name="ctr_nme" value="ctr_nme" label="ctr_nme" dataType="xsd:string" breakOrder="" fieldOrder="12"/>
<element name="tcc_first_nme" value="tcc_first_nme" label="tcc_first_nme" dataType="xsd:string" breakOrder="" fieldOrder="13"/>
<element name="tcc_last_nme" value="tcc_last_nme" label="tcc_last_nme" dataType="xsd:string" breakOrder="" fieldOrder="14"/>
<element name="tcc_phone_nbr" value="tcc_phone_nbr" label="tcc_phone_nbr" dataType="xsd:string" breakOrder="" fieldOrder="15"/>
<element name="sl_worker_first_nme" value="sl_worker_first_nme" label="sl_worker_first_nme" dataType="xsd:string" breakOrder="" fieldOrder="21"/>
<element name="sl_worker_last_nme" value="sl_worker_last_nme" label="sl_worker_last_nme" dataType="xsd:string" breakOrder="" fieldOrder="22"/>
<element name="sl_worker_phn" value="sl_worker_phn" label="sl_worker_phn" dataType="xsd:string" breakOrder="" fieldOrder="23"/>
<element name="tcc_polite_first_nme" value="tcc_polite_first_nme" label="tcc_polite_first_nme" dataType="xsd:string" breakOrder="" fieldOrder="16"/>
<element name="tcc_polite_last_nme" value="tcc_polite_last_nme" label="tcc_polite_last_nme" dataType="xsd:string" breakOrder="" fieldOrder="17"/>
<element name="tcc_polite_phone_nbr" value="tcc_polite_phone_nbr" label="tcc_polite_phone_nbr" dataType="xsd:string" breakOrder="" fieldOrder="18"/>
<group name="G_2" label="G_2" source="trx_recipients">
<element name="DP_RID" value="DP_RID" label="DP_RID" dataType="xsd:integer" breakOrder="" fieldOrder="1"/>
<element name="TR_CENTER_CDE" value="TR_CENTER_CDE" label="TR_CENTER_CDE" dataType="xsd:integer" breakOrder="" fieldOrder="2"/>
<element name="TC_COORD_NME" value="TC_COORD_NME" label="TC_COORD_NME" dataType="xsd:string" breakOrder="" fieldOrder="3"/>
<element name="SRCH_COORD_IID" value="SRCH_COORD_IID" label="SRCH_COORD_IID" dataType="xsd:integer" breakOrder="" fieldOrder="6"/>
<element name="COORD_NME" value="COORD_NME" label="COORD_NME" dataType="xsd:string" breakOrder="" fieldOrder="7"/>
<element name="SCID" value="SCID" label="SCID" dataType="xsd:integer" breakOrder="" fieldOrder="8"/>
<element name="TC_COORD_LAST_NAME" value="TC_COORD_LAST_NAME" label="TC_COORD_LAST_NAME" dataType="xsd:string" breakOrder="" fieldOrder="5"/>
<element name="TC_COORD_FIRST_NAME" value="TC_COORD_FIRST_NAME" label="TC_COORD_FIRST_NAME" dataType="xsd:string" breakOrder="" fieldOrder="4"/>
</group>
</group>
<group name="G_3" label="G_3" source="pt_prelim_report">
<element name="tc_cde" value="tc_cde" label="tc_cde" dataType="xsd:integer" breakOrder="" fieldOrder="1"/>
<element name="DP_rid" value="DP_rid" label="DP_rid" dataType="xsd:double" breakOrder="" fieldOrder="2"/>
<element name="last_nme" value="last_nme" label="last_nme" dataType="xsd:string" breakOrder="" fieldOrder="3"/>
<element name="first_nm" value="first_nm" label="first_nm" dataType="xsd:string" breakOrder="" fieldOrder="4"/>
<element name="addr1" value="addr1" label="addr1" dataType="xsd:string" breakOrder="" fieldOrder="5"/>
<element name="addr2" value="addr2" label="addr2" dataType="xsd:string" breakOrder="" fieldOrder="6"/>
<element name="cty" value="cty" label="cty" dataType="xsd:string" breakOrder="" fieldOrder="7"/>
<element name="st" value="st" label="st" dataType="xsd:string" breakOrder="" fieldOrder="8"/>
<element name="zip" value="zip" label="zip" dataType="xsd:string" breakOrder="" fieldOrder="9"/>
<element name="recip_lang" value="recip_lang" label="recip_lang" dataType="xsd:string" breakOrder="" fieldOrder="10"/>
<element name="age" value="age" label="age" dataType="xsd:string" breakOrder="" fieldOrder="11"/>
</group>
</dataStructure>
</nodeList>
</output>
0 -
please upload RTF template and xml, Thanks
0 -
I am relatively new to BI Publisher, not sure where this formula should be added. I have linked the data sets, generated the xml; should this be added when I create the report? If so, where?
0 -
once you linked both the data sets ,generate xml and create RTF as usual ,then use this formula xdofx:nvl(COL2,COL1) in place of column that replace values. Thanks let me know if you have issues.
0 -
using RTF template you can generate output in excel , pdf , html etc.
0 -
The requirement is Excel.
0 -
As Venkat already said... better use a rtf template because you can achieve much more with it
0 -
you can easily achieve requirement using RTF template , please upload direct xml file and uplosd here , i will send you new RTF template. Thanks
0