nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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.

Find more posts tagged with

Accepted answers

All comments

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>

Venkat Thota - BIP

please upload RTF template and xml, Thanks

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?

Venkat Thota - BIP

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.

Venkat Thota - BIP

using RTF template you can generate output in excel , pdf , html etc.

The requirement is Excel.

As Venkat already said... better use a rtf template because you can achieve much more with it

Venkat Thota - BIP

you can easily achieve requirement using RTF template , please upload direct xml file and uplosd here , i will send you new RTF template. Thanks