How to create a string formula in BI Publisher from joined Data Sets — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to create a string formula in BI Publisher from joined Data Sets

Received Response
151
Views
8
Comments
Dclay
Dclay Rank 3 - Community Apprentice
in Oracle Analytics Cloud and Server

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

  • Dclay
    Dclay Rank 3 - Community Apprentice

    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
    Venkat Thota - BIP Rank 7 - Analytics Coach

    please upload RTF template and xml, Thanks

  • Dclay
    Dclay Rank 3 - Community Apprentice

    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
    Venkat Thota - BIP Rank 7 - Analytics Coach

    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
    Venkat Thota - BIP Rank 7 - Analytics Coach

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

  • Dclay
    Dclay Rank 3 - Community Apprentice

    The requirement is Excel.

  • Sketz
    Sketz Rank 4 - Community Specialist

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

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

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