Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

Calculated column not giving values

Received Response
242
Views
13
Comments
MaartenDIF
MaartenDIF Rank 4 - Community Specialist
in Oracle Transactional Business Intelligence

Content

I'm creating an AP dashboard where I want to combine the subject areas for AP invoices and payments to show in a single column whether an invoice is paid/unpaid and the payment is cleared/negotiable.

The logic should be as follows:
IF Payment Status is null, THEN Invoice Payment status, ELSE Payment Status

My OTBI code is as follows:
CASE WHEN "Payables Payments - Payment History Real Time"."- Payment Information"."Check Status lookup Code" is null THEN "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name" ELSE "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status" END

This is somehow giving me only values when the payment status is not null. I've tried a number of things:
Writing the logic the other way around
Specifying all the values instead of comparing to null (not in(...,...,...)). 
Comparing to '' instead of null
Using the IfNull function.

The only reason I can think of this code is not working is because the fields belong to two different subject areas: Payables Payments - Payment History Real Time and Payables Invoices - Transactions Real Time.

The rest of my dashboard is not too much fancy and the columns are giving values when put in separate columns of each other.

Any help is much appreciated!

Code Snippet

<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160">
   <saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""Payables Invoices - Transactions Real Time"" withinHierarchy="true">
      <saw:columns>
         <saw:column xsi:type="saw:regularColumn" columnID="c80f48f67da6baa77">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Check Date"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cdf65b89d27677cba">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c34fafae75f300bcb">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Reference Information"."Payment Status Name"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c310eee8fdf45c939">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">CASE WHEN "Payables Payments - Payment History Real Time"."- Payment Information"."Check Status lookup Code" is null THEN "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name" ELSE "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status" END</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c6dc81e1859116dc2">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."Payments History Details"."Accounting Date"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c4100e2e73c0a0c45">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Ledger"."Ledger Name"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c3d5d59034508ad6f">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Balancing Segment"."Balancing Segment Code"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cf3819067578824a5">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Balancing Segment"."Balancing Segment Description"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="ca1d16b83f4ac7b71">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Natural Account"."Account Code"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c6f4ca10834c472f1">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Name"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c582ede7afd5e12c6">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Number"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c311454c5e705ba1f">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"Supplier Site"."Supplier Site Code"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cc3c7bf2e60299b62">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Number"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="ca3692628f202b30c">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Type Name"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c6aacb38437bc5913">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Description"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cfc52801de059d8d3">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Date"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c3f208fd93bac1863">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Accounting Date"."Accounting Date"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c47e43bbb54d19736">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Last Updated Date"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cae0a234aa6c23e07">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Last Updated By Username"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c24b86a3678c088be">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Invoice Currency"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cc20ca6de33cfabd0">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Distribution Amount"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c539f40551e1769c8">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Ledger Currency"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="c89305da30d04be2a">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Distribution Base Amount"</sawx:expr></saw:columnFormula></saw:column>
         <saw:column xsi:type="saw:regularColumn" columnID="cdbe30125cd21ba7d">
            <saw:columnFormula>
               <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Amounts"."Invoice Amount"-"- Invoice Amounts"."Paid Amount"</sawx:expr></saw:columnFormula>
            <saw:tableHeading>
               <saw:caption fmt="text">
                  <saw:text>Invoice Amounts</saw:text></saw:caption></saw:tableHeading>
            <saw:columnHeading>
               <saw:caption fmt="text">
                  <saw:text>Outstanding Amount</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
      <saw:filter>
         <sawx:expr xsi:type="sawx:logical" op="and">
            <sawx:expr xsi:type="sawx:comparison" op="greaterOrEqual">
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Date"</sawx:expr>
               <sawx:expr xsi:type="xsd:date">2020-05-01</sawx:expr></sawx:expr>
            <sawx:expr xsi:type="sawx:comparison" op="null">
               <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Cancelled Date"</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
   <saw:views currentView="0">
      <saw:view xsi:type="saw:compoundView" name="compoundView!1">
         <saw:cvTable>
            <saw:cvRow>
               <saw:cvCell viewName="titleView!1"/></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName="tableView!1"/></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName="tableView!2"/></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName="pivotTableView!1"/></saw:cvRow></saw:cvTable></saw:view>
      <saw:view xsi:type="saw:titleView" name="titleView!1"/>
      <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true" width="2800">
         <saw:edges>
            <saw:edge axis="page" showColumnHeader="true"/>
            <saw:edge axis="section"/>
            <saw:edge axis="row" showColumnHeader="true">
               <saw:edgeLayers>
                  <saw:edgeLayer type="column" columnID="c4100e2e73c0a0c45"/>
                  <saw:edgeLayer type="column" columnID="c3d5d59034508ad6f">
                     <saw:levels>
                        <saw:level>
                           <saw:displaySubTotal id="st_c3d5d59034508ad6f" subTotalPosition="none"/></saw:level></saw:levels></saw:edgeLayer>
                  <saw:edgeLayer type="column" columnID="cf3819067578824a5"/>
                  <saw:edgeLayer type="column" columnID="ca1d16b83f4ac7b71">
                     <saw:levels>
                        <saw:level>
                           <saw:displaySubTotal id="st_ca1d16b83f4ac7b71" subTotalPosition="none"/></saw:level></saw:levels></saw:edgeLayer>
                  <saw:edgeLayer type="column" columnID="c6f4ca10834c472f1"/>
                  <saw:edgeLayer type="column" columnID="c582ede7afd5e12c6"/>
                  <saw:edgeLayer type="column" columnID="c311454c5e705ba1f"/>
                  <saw:edgeLayer type="column" columnID="cc3c7bf2e60299b62"/>
                  <saw:edgeLayer type="column" columnID="ca3692628f202b30c"/>
                  <saw:edgeLayer type="column" columnID="c6aacb38437bc5913"/>
                  <saw:edgeLayer type="column" columnID="cfc52801de059d8d3"/>
                  <saw:edgeLayer type="column" columnID="c3f208fd93bac1863"/>
                  <saw:edgeLayer type="column" columnID="c47e43bbb54d19736"/>
                  <saw:edgeLayer type="column" columnID="cae0a234aa6c23e07"/>
                  <saw:edgeLayer type="column" columnID="c24b86a3678c088be"/>
                  <saw:edgeLayer type="column" columnID="cc20ca6de33cfabd0"/>
                  <saw:edgeLayer type="column" columnID="c539f40551e1769c8"/>
                  <saw:edgeLayer type="column" columnID="c89305da30d04be2a"/>
                  <saw:edgeLayer type="column" columnID="cdbe30125cd21ba7d"/>
                  <saw:edgeLayer type="column" columnID="c6dc81e1859116dc2"/>
                  <saw:edgeLayer type="column" columnID="c80f48f67da6baa77"/>
                  <saw:edgeLayer type="column" columnID="cdf65b89d27677cba"/>
                  <saw:edgeLayer type="column" columnID="c310eee8fdf45c939"/>
                  <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/></saw:edgeLayers></saw:edge>
            <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view>
      <saw:view xsi:type="saw:tableView" name="tableView!2" scrollingEnabled="true" width="2800">
         <saw:edges>
            <saw:edge axis="page" showColumnHeader="true"/>
            <saw:edge axis="section">
               <saw:displayFormat>
                  <saw:formatSpec/></saw:displayFormat></saw:edge>
            <saw:edge axis="row" showColumnHeader="true">
               <saw:displayGrandTotals>
                  <saw:displayGrandTotal id="gt_row" grandTotalPosition="none">
                     <saw:dataBodyFormat>
                        <saw:displayFormat>
                           <saw:formatSpec/></saw:displayFormat></saw:dataBodyFormat></saw:displayGrandTotal></saw:displayGrandTotals>
               <saw:edgeLayers>
                  <saw:edgeLayer type="column" columnID="c4100e2e73c0a0c45"/>
                  <saw:edgeLayer type="column" columnID="ca3692628f202b30c">
                     <saw:memberFormat>
                        <saw:displayFormat>
                           <saw:formatSpec/></saw:displayFormat></saw:memberFormat></saw:edgeLayer>
                  <saw:edgeLayer type="column" columnID="cdbe30125cd21ba7d"/>
                  <saw:edgeLayer type="column" columnID="c539f40551e1769c8"/>
                  <saw:edgeLayer type="column" columnID="c80f48f67da6baa77"/>
                  <saw:edgeLayer type="column" columnID="cdf65b89d27677cba"/>
                  <saw:edgeLayer type="column" columnID="c310eee8fdf45c939"/>
                  <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/></saw:edgeLayers></saw:edge>
            <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view>
      <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="true">
         <saw:edges>
            <saw:edge axis="page" showColumnHeader="true"/>
            <saw:edge axis="section"/>
            <saw:edge axis="row" showColumnHeader="true">
               <saw:edgeLayers>
                  <saw:edgeLayer type="column" columnID="cdf65b89d27677cba"/>
                  <saw:edgeLayer type="column" columnID="ca3692628f202b30c"/>
                  <saw:edgeLayer type="column" columnID="c4100e2e73c0a0c45"/>
                  <saw:edgeLayer type="column" columnID="c80f48f67da6baa77"/>
                  <saw:edgeLayer type="column" columnID="c6dc81e1859116dc2"/>
                  <saw:edgeLayer type="column" columnID="cfc52801de059d8d3"/>
                  <saw:edgeLayer type="column" columnID="c3f208fd93bac1863"/>
                  <saw:edgeLayer type="column" columnID="c3d5d59034508ad6f"/>
                  <saw:edgeLayer type="column" columnID="cf3819067578824a5"/>
                  <saw:edgeLayer type="column" columnID="ca1d16b83f4ac7b71"/>
                  <saw:edgeLayer type="column" columnID="c6f4ca10834c472f1"/>
                  <saw:edgeLayer type="column" columnID="c582ede7afd5e12c6"/>
                  <saw:edgeLayer type="column" columnID="c311454c5e705ba1f"/>
                  <saw:edgeLayer type="column" columnID="c47e43bbb54d19736"/>
                  <saw:edgeLayer type="column" columnID="cc3c7bf2e60299b62"/>
                  <saw:edgeLayer type="column" columnID="c6aacb38437bc5913"/>
                  <saw:edgeLayer type="column" columnID="c539f40551e1769c8"/>
                  <saw:edgeLayer type="column" columnID="c310eee8fdf45c939"/>
                  <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/></saw:edgeLayers></saw:edge>
            <saw:edge axis="column" showColumnHeader="rollover">
               <saw:edgeLayers>
                  <saw:edgeLayer type="measure"/></saw:edgeLayers></saw:edge></saw:edges>
         <saw:measuresList>
            <saw:measure columnID="cc20ca6de33cfabd0"/>
            <saw:measure columnID="c89305da30d04be2a"/>
            <saw:measure columnID="cdbe30125cd21ba7d"/></saw:measuresList></saw:view></saw:views>
   <saw:prompts scope="report" subjectArea=""Payables Invoices - Transactions Real Time"">
      <saw:promptStep>
         <saw:individualPrompts>
            <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c21" subjectArea=""Payables Invoices - Transactions Real Time"" required="false">
               <saw:formula>
                  <sawx:expr xsi:type="sawx:sqlExpression">"Ledger"."Ledger Name"</sawx:expr></saw:formula>
               <saw:promptOperator op="in"/>
               <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true">
                  <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
               <saw:promptDefaultValues type="allChoices" usingCodeValue="false"/>
               <saw:constrainPrompt type="none"/>
               <saw:setPromptVariables>
                  <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
               <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
            <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c19" subjectArea=""Payables Invoices - Transactions Real Time"" required="false">
               <saw:formula>
                  <sawx:expr xsi:type="sawx:sqlExpression">"- Balancing Segment"."Balancing Segment Code"</sawx:expr></saw:formula>
               <saw:promptOperator op="in"/>
               <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true">
                  <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
               <saw:promptDefaultValues type="allChoices" usingCodeValue="false"/>
               <saw:constrainPrompt type="specificPrompts">
                  <saw:constrainedByFormula>
                     <sawx:expr xsi:type="sawx:sqlExpression">"Ledger"."Ledger Name"</sawx:expr></saw:constrainedByFormula></saw:constrainPrompt>
               <saw:setPromptVariables>
                  <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
               <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
            <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c20" subjectArea=""Payables Invoices - Transactions Real Time"" required="false">
               <saw:formula>
                  <sawx:expr xsi:type="sawx:sqlExpression">"- Accounting Date"."Accounting Date"</sawx:expr></saw:formula>
               <saw:promptOperator op="lessOrEqual"/>
               <saw:promptUIControl xsi:type="saw:calendar">
                  <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
               <saw:promptDefaultValues type="specificValue" usingCodeValue="false">
                  <saw:promptDefaultValue>2019-09-30</saw:promptDefaultValue></saw:promptDefaultValues>
               <saw:constrainPrompt type="none"/>
               <saw:setPromptVariables>
                  <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
               <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
            <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c25" subjectArea=""Payables Payments - Payment History Real Time"" required="false">
               <saw:formula>
                  <sawx:expr xsi:type="sawx:columnExpression" formulaUse="code">
                     <saw:columnFormula formulaUse="display">
                        <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"</sawx:expr></saw:columnFormula>
                     <saw:columnFormula formulaUse="code">
                        <sawx:expr xsi:type="sawx:sqlExpression">DESCRIPTOR_IDOF("Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status")</sawx:expr></saw:columnFormula></sawx:expr></saw:formula>
               <saw:promptOperator op="in"/>
               <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true" enableSelectByCodeColumn="false" showCodeColumnValue="false">
                  <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
               <saw:promptDefaultValues type="allChoices" usingCodeValue="true"/>
               <saw:constrainPrompt type="none"/>
               <saw:setPromptVariables>
                  <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
               <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
            <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c26" subjectArea=""Payables Invoices - Transactions Real Time"" required="true">
               <saw:formula>
                  <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Type Name"</sawx:expr></saw:formula>
               <saw:promptOperator op="in"/>
               <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true">
                  <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
               <saw:promptDefaultValues type="specificValue" usingCodeValue="false">
                  <saw:promptDefaultValue>Standard</saw:promptDefaultValue></saw:promptDefaultValues>
               <saw:constrainPrompt type="none"/>
               <saw:setPromptVariables>
                  <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
               <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt></saw:individualPrompts>
         <saw:customWidth width="120" using="custompixels"/></saw:promptStep></saw:prompts></saw:report>

Capture 5.PNG

Tagged:
«1

Answers

  • Ashu Raj-Oracle
    Ashu Raj-Oracle Rank 2 - Community Beginner

    For the statement "Case when A then B else C", can you try printing all A, B, C as well so that we can see the values for each of them that you have.

  • MaartenDIF
    MaartenDIF Rank 4 - Community Specialist

    Hi Ashu,

    A - "Payables Payments - Payment History Real Time"."- Payment Information"."Check Status lookup Code"
    CLEARED
    VOIDED
    NEGOTIABLE

    B - "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name"
    Fully paid
    Not paid
    Partially paid

    C - "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"
    Cleared
    Cleared but unaccounted
    Issued
    Negotiable
    Overflow
    Reconciled
    Reconciled unaccounted
    Setup
    Spoiled
    Stop initiated
    Unconfirmed setup
    Voided

    The italics I've not found in our data. I found all these values by adding the field to my report and then select 'filter' so that it would show all values.

    Kind regards,
    Maarten

  • Ashu Raj-Oracle
    Ashu Raj-Oracle Rank 2 - Community Beginner

    Well its working for me for 2 subject areas as well. Please see below code. 

    <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">                        
       <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Procurement - Purchasing Real Time&quot;" withinHierarchy="true">                                                
          <saw:columns>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c3dd2a9d64f333c0f">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"Purchase Order Header Detail"."PO Header Id"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c43c526c42ac0ff56">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"Purchase Order Header Detail"."Order"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c7c62329b263ea8b0">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"Purchase Order Header Detail"."PO_HEADERS_PO_TYPE_"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="cce20da21165bc31e">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Purchase Order Line Detail"."Line Number"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c9a2527c15a0ae777">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Purchase Order Line Detail"."Supplier Item"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c34fafae75f300bcb">                                                            
                <saw:columnFormula>                                                                           
                   <sawx:expr xsi:type="sawx:sqlExpression">CASE WHEN "- Purchase Order Line Detail"."Supplier Item" IS NULL THEN "- Procurement Item"."Item Description" ELSE "Purchase Order Header Detail"."PO_HEADERS_PO_TYPE_" END</sawx:expr></saw:columnFormula>
                <saw:displayFormat>
                   <saw:formatSpec suppress="suppress" backgroundColor="#FF0000" wrapText="true"/></saw:displayFormat>
                <saw:tableHeading>
                   <saw:caption fmt="text">
                      <saw:text>Purchase Order Line Detail</saw:text></saw:caption></saw:tableHeading>
                <saw:columnHeading>
                   <saw:displayFormat>
                      <saw:formatSpec/></saw:displayFormat>
                   <saw:caption fmt="text">
                      <saw:text>CustomHeader</saw:text></saw:caption></saw:columnHeading></saw:column>                                             
             <saw:column xsi:type="saw:regularColumn" columnID="c08cac1091e62c593">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Procurement Item"."Item Description"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c0ada19b9b7af1e8c">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Procurement Item"."Item Name"</sawx:expr></saw:columnFormula></saw:column>                                                                        
             <saw:column xsi:type="saw:regularColumn" columnID="c29dd594106b3551a">                                                                                                
                <saw:columnFormula>                                                                                                                        
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Procurement Item"."Category Name"</sawx:expr></saw:columnFormula></saw:column></saw:columns></saw:criteria>                        
       <saw:views currentView="2">                                                
          <saw:view xsi:type="saw:compoundView" name="compoundView!1">                                                                        
             <saw:cvTable>                                                                                                
                <saw:cvRow>                                                                                                                        
                   <saw:cvCell viewName="titleView!1"/></saw:cvRow>                                                                                                
                <saw:cvRow>                                                                                                                        
                   <saw:cvCell viewName="tableView!1"/></saw:cvRow></saw:cvTable></saw:view>                                                
          <saw:view xsi:type="saw:titleView" name="titleView!1"/>                                                
          <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true">                                                                        
             <saw:edges>
                <saw:edge axis="page" showColumnHeader="true"/>
                <saw:edge axis="section"/>
                <saw:edge axis="row" showColumnHeader="true">                                                                                                                        
                   <saw:edgeLayers>
                      <saw:edgeLayer type="column" columnID="c3dd2a9d64f333c0f"/>
                      <saw:edgeLayer type="column" columnID="c43c526c42ac0ff56"/>
                      <saw:edgeLayer type="column" columnID="c7c62329b263ea8b0"/>
                      <saw:edgeLayer type="column" columnID="cce20da21165bc31e"/>
                      <saw:edgeLayer type="column" columnID="c9a2527c15a0ae777"/>
                      <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/>
                      <saw:edgeLayer type="column" columnID="c08cac1091e62c593"/>
                      <saw:edgeLayer type="column" columnID="c0ada19b9b7af1e8c"/>
                      <saw:edgeLayer type="column" columnID="c29dd594106b3551a"/></saw:edgeLayers></saw:edge>
                <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views>            
       <saw:prompts scope="report" subjectArea="&quot;Procurement - Purchasing Real Time&quot;"/></saw:report>

  • Ashu Raj-Oracle
    Ashu Raj-Oracle Rank 2 - Community Beginner

    Maarten, please re-check your case condition based on actual values in that particular row. 

    OTBICapture.PNG

  • MaartenDIF
    MaartenDIF Rank 4 - Community Specialist

    Ugh just typed up my comment and clicked select as best answer by accident and cannot undo it. Here goes again:

    I input the following formula:

    CASE 
    WHEN "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status" in ('Cleared','Negotiable','Voided')

    THEN "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"

    ELSE "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name"

    END

    Got only results for the first (WHEN THEN) clause.

    Then tried below code:

    CASE 
    WHEN "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name" = 'Fully paid'

    THEN "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"

    ELSE "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name"

    END

    Only got results where the Invoice Header Payment Status = Fully paid (so I'd get Cleared/Negotiable/Voided).

    I have the hunch that it has to do that there's no record of a payment in the second subject area when there's no payment created yet, so the calculation can not be performed. I just don't know how to get around this.

    Kind regards,
    Maarten

  • Ashu Raj-Oracle
    Ashu Raj-Oracle Rank 2 - Community Beginner

    Can you use this code and show the results you are getting:

    <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160">
       <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;" withinHierarchy="true">
          <saw:columns>
             <saw:column xsi:type="saw:regularColumn" columnID="c80f48f67da6baa77">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Check Date"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cdf65b89d27677cba">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c34fafae75f300bcb">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Reference Information"."Payment Status Name"</sawx:expr></saw:columnFormula></saw:column>

             <saw:column xsi:type="saw:regularColumn" columnID="c3dd2a9d64f333c0f">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Check Status lookup Code"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c43c526c42ac0ff56">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c7c62329b263ea8b0">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"</sawx:expr></saw:columnFormula></saw:column>

             <saw:column xsi:type="saw:regularColumn" columnID="c310eee8fdf45c939">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">CASE WHEN "Payables Payments - Payment History Real Time"."- Payment Information"."Check Status lookup Code" is null THEN "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name" ELSE "Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status" END</sawx:expr></saw:columnFormula></saw:column>

             <saw:column xsi:type="saw:regularColumn" columnID="c6dc81e1859116dc2">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."Payments History Details"."Accounting Date"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c4100e2e73c0a0c45">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Ledger"."Ledger Name"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c3d5d59034508ad6f">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Balancing Segment"."Balancing Segment Code"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cf3819067578824a5">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Balancing Segment"."Balancing Segment Description"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="ca1d16b83f4ac7b71">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Natural Account"."Account Code"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c6f4ca10834c472f1">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Name"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c582ede7afd5e12c6">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Number"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c311454c5e705ba1f">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Supplier Site"."Supplier Site Code"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cc3c7bf2e60299b62">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Number"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="ca3692628f202b30c">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Type Name"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c6aacb38437bc5913">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Description"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cfc52801de059d8d3">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Date"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c3f208fd93bac1863">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Accounting Date"."Accounting Date"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c47e43bbb54d19736">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Last Updated Date"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cae0a234aa6c23e07">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Last Updated By Username"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c24b86a3678c088be">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Invoice Currency"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cc20ca6de33cfabd0">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Distribution Amount"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c539f40551e1769c8">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Ledger Currency"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c89305da30d04be2a">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Distribution Base Amount"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="cdbe30125cd21ba7d">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Amounts"."Invoice Amount"-"- Invoice Amounts"."Paid Amount"</sawx:expr></saw:columnFormula>
                <saw:tableHeading>
                   <saw:caption fmt="text">
                      <saw:text>Invoice Amounts</saw:text></saw:caption></saw:tableHeading>
                <saw:columnHeading>
                   <saw:caption fmt="text">
                      <saw:text>Outstanding Amount</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
          <saw:filter>
             <sawx:expr xsi:type="sawx:logical" op="and">
                <sawx:expr xsi:type="sawx:comparison" op="greaterOrEqual">
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Date"</sawx:expr>
                   <sawx:expr xsi:type="xsd:date">2020-05-01</sawx:expr></sawx:expr>
                <sawx:expr xsi:type="sawx:comparison" op="null">
                   <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Cancelled Date"</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
       <saw:views currentView="0">
          <saw:view xsi:type="saw:compoundView" name="compoundView!1">
             <saw:cvTable>
                <saw:cvRow>
                   <saw:cvCell viewName="titleView!1"/></saw:cvRow>
                <saw:cvRow>
                   <saw:cvCell viewName="tableView!1"/></saw:cvRow>
                <saw:cvRow>
                   <saw:cvCell viewName="tableView!2"/></saw:cvRow>
                <saw:cvRow>
                   <saw:cvCell viewName="pivotTableView!1"/></saw:cvRow></saw:cvTable></saw:view>
          <saw:view xsi:type="saw:titleView" name="titleView!1"/>
          <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true" width="2800">
             <saw:edges>
                <saw:edge axis="page" showColumnHeader="true"/>
                <saw:edge axis="section"/>
                <saw:edge axis="row" showColumnHeader="true">
                   <saw:edgeLayers>
                      <saw:edgeLayer type="column" columnID="c4100e2e73c0a0c45"/>
                      <saw:edgeLayer type="column" columnID="c3d5d59034508ad6f">
                         <saw:levels>
                            <saw:level>
                               <saw:displaySubTotal id="st_c3d5d59034508ad6f" subTotalPosition="none"/></saw:level></saw:levels></saw:edgeLayer>
                      <saw:edgeLayer type="column" columnID="cf3819067578824a5"/>
                      <saw:edgeLayer type="column" columnID="ca1d16b83f4ac7b71">
                         <saw:levels>
                            <saw:level>
                               <saw:displaySubTotal id="st_ca1d16b83f4ac7b71" subTotalPosition="none"/></saw:level></saw:levels></saw:edgeLayer>
                      <saw:edgeLayer type="column" columnID="c6f4ca10834c472f1"/>
                      <saw:edgeLayer type="column" columnID="c582ede7afd5e12c6"/>
                      <saw:edgeLayer type="column" columnID="c311454c5e705ba1f"/>
                      <saw:edgeLayer type="column" columnID="cc3c7bf2e60299b62"/>
                      <saw:edgeLayer type="column" columnID="ca3692628f202b30c"/>
                      <saw:edgeLayer type="column" columnID="c6aacb38437bc5913"/>
                      <saw:edgeLayer type="column" columnID="cfc52801de059d8d3"/>
                      <saw:edgeLayer type="column" columnID="c3f208fd93bac1863"/>
                      <saw:edgeLayer type="column" columnID="c47e43bbb54d19736"/>
                      <saw:edgeLayer type="column" columnID="cae0a234aa6c23e07"/>
                      <saw:edgeLayer type="column" columnID="c24b86a3678c088be"/>
                      <saw:edgeLayer type="column" columnID="cc20ca6de33cfabd0"/>
                      <saw:edgeLayer type="column" columnID="c539f40551e1769c8"/>
                      <saw:edgeLayer type="column" columnID="c89305da30d04be2a"/>
                      <saw:edgeLayer type="column" columnID="cdbe30125cd21ba7d"/>
                      <saw:edgeLayer type="column" columnID="c6dc81e1859116dc2"/>
                      <saw:edgeLayer type="column" columnID="c80f48f67da6baa77"/>
                      <saw:edgeLayer type="column" columnID="cdf65b89d27677cba"/>
                      <saw:edgeLayer type="column" columnID="c310eee8fdf45c939"/>
                      <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/></saw:edgeLayers></saw:edge>
                <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view>
          <saw:view xsi:type="saw:tableView" name="tableView!2" scrollingEnabled="true" width="2800">
             <saw:edges>
                <saw:edge axis="page" showColumnHeader="true"/>
                <saw:edge axis="section">
                   <saw:displayFormat>
                      <saw:formatSpec/></saw:displayFormat></saw:edge>
                <saw:edge axis="row" showColumnHeader="true">
                   <saw:displayGrandTotals>
                      <saw:displayGrandTotal id="gt_row" grandTotalPosition="none">
                         <saw:dataBodyFormat>
                            <saw:displayFormat>
                               <saw:formatSpec/></saw:displayFormat></saw:dataBodyFormat></saw:displayGrandTotal></saw:displayGrandTotals>
                   <saw:edgeLayers>
                      <saw:edgeLayer type="column" columnID="c4100e2e73c0a0c45"/>
                      <saw:edgeLayer type="column" columnID="ca3692628f202b30c">
                         <saw:memberFormat>
                            <saw:displayFormat>
                               <saw:formatSpec/></saw:displayFormat></saw:memberFormat></saw:edgeLayer>
                      <saw:edgeLayer type="column" columnID="cdbe30125cd21ba7d"/>
                      <saw:edgeLayer type="column" columnID="c539f40551e1769c8"/>
                      <saw:edgeLayer type="column" columnID="c80f48f67da6baa77"/>
                      <saw:edgeLayer type="column" columnID="cdf65b89d27677cba"/>
                      <saw:edgeLayer type="column" columnID="c310eee8fdf45c939"/>
                      <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/></saw:edgeLayers></saw:edge>
                <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view>
          <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="true">
             <saw:edges>
                <saw:edge axis="page" showColumnHeader="true"/>
                <saw:edge axis="section"/>
                <saw:edge axis="row" showColumnHeader="true">
                   <saw:edgeLayers>
                      <saw:edgeLayer type="column" columnID="cdf65b89d27677cba"/>
                      <saw:edgeLayer type="column" columnID="ca3692628f202b30c"/>
                      <saw:edgeLayer type="column" columnID="c4100e2e73c0a0c45"/>
                      <saw:edgeLayer type="column" columnID="c80f48f67da6baa77"/>
                      <saw:edgeLayer type="column" columnID="c6dc81e1859116dc2"/>
                      <saw:edgeLayer type="column" columnID="cfc52801de059d8d3"/>
                      <saw:edgeLayer type="column" columnID="c3f208fd93bac1863"/>
                      <saw:edgeLayer type="column" columnID="c3d5d59034508ad6f"/>
                      <saw:edgeLayer type="column" columnID="cf3819067578824a5"/>
                      <saw:edgeLayer type="column" columnID="ca1d16b83f4ac7b71"/>
                      <saw:edgeLayer type="column" columnID="c6f4ca10834c472f1"/>
                      <saw:edgeLayer type="column" columnID="c582ede7afd5e12c6"/>
                      <saw:edgeLayer type="column" columnID="c311454c5e705ba1f"/>
                      <saw:edgeLayer type="column" columnID="c47e43bbb54d19736"/>
                      <saw:edgeLayer type="column" columnID="cc3c7bf2e60299b62"/>
                      <saw:edgeLayer type="column" columnID="c6aacb38437bc5913"/>
                      <saw:edgeLayer type="column" columnID="c539f40551e1769c8"/>
                      <saw:edgeLayer type="column" columnID="c310eee8fdf45c939"/>
                      <saw:edgeLayer type="column" columnID="c34fafae75f300bcb"/></saw:edgeLayers></saw:edge>
                <saw:edge axis="column" showColumnHeader="rollover">
                   <saw:edgeLayers>
                      <saw:edgeLayer type="measure"/></saw:edgeLayers></saw:edge></saw:edges>
             <saw:measuresList>
                <saw:measure columnID="cc20ca6de33cfabd0"/>
                <saw:measure columnID="c89305da30d04be2a"/>
                <saw:measure columnID="cdbe30125cd21ba7d"/></saw:measuresList></saw:view></saw:views>
       <saw:prompts scope="report" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;">
          <saw:promptStep>
             <saw:individualPrompts>
                <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c21" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;" required="false">
                   <saw:formula>
                      <sawx:expr xsi:type="sawx:sqlExpression">"Ledger"."Ledger Name"</sawx:expr></saw:formula>
                   <saw:promptOperator op="in"/>
                   <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true">
                      <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
                   <saw:promptDefaultValues type="allChoices" usingCodeValue="false"/>
                   <saw:constrainPrompt type="none"/>
                   <saw:setPromptVariables>
                      <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
                   <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
                <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c19" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;" required="false">
                   <saw:formula>
                      <sawx:expr xsi:type="sawx:sqlExpression">"- Balancing Segment"."Balancing Segment Code"</sawx:expr></saw:formula>
                   <saw:promptOperator op="in"/>
                   <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true">
                      <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
                   <saw:promptDefaultValues type="allChoices" usingCodeValue="false"/>
                   <saw:constrainPrompt type="specificPrompts">
                      <saw:constrainedByFormula>
                         <sawx:expr xsi:type="sawx:sqlExpression">"Ledger"."Ledger Name"</sawx:expr></saw:constrainedByFormula></saw:constrainPrompt>
                   <saw:setPromptVariables>
                      <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
                   <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
                <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c20" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;" required="false">
                   <saw:formula>
                      <sawx:expr xsi:type="sawx:sqlExpression">"- Accounting Date"."Accounting Date"</sawx:expr></saw:formula>
                   <saw:promptOperator op="lessOrEqual"/>
                   <saw:promptUIControl xsi:type="saw:calendar">
                      <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
                   <saw:promptDefaultValues type="specificValue" usingCodeValue="false">
                      <saw:promptDefaultValue>2019-09-30</saw:promptDefaultValue></saw:promptDefaultValues>
                   <saw:constrainPrompt type="none"/>
                   <saw:setPromptVariables>
                      <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
                   <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
                <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c25" subjectArea="&quot;Payables Payments - Payment History Real Time&quot;" required="false">
                   <saw:formula>
                      <sawx:expr xsi:type="sawx:columnExpression" formulaUse="code">
                         <saw:columnFormula formulaUse="display">
                            <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"</sawx:expr></saw:columnFormula>
                         <saw:columnFormula formulaUse="code">
                            <sawx:expr xsi:type="sawx:sqlExpression">DESCRIPTOR_IDOF("Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status")</sawx:expr></saw:columnFormula></sawx:expr></saw:formula>
                   <saw:promptOperator op="in"/>
                   <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true" enableSelectByCodeColumn="false" showCodeColumnValue="false">
                      <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
                   <saw:promptDefaultValues type="allChoices" usingCodeValue="true"/>
                   <saw:constrainPrompt type="none"/>
                   <saw:setPromptVariables>
                      <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
                   <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt>
                <saw:prompt xsi:type="saw:columnFilterPrompt" columnID="c26" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;" required="true">
                   <saw:formula>
                      <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Type Name"</sawx:expr></saw:formula>
                   <saw:promptOperator op="in"/>
                   <saw:promptUIControl xsi:type="saw:browse" maxChoices="-1" includeAllChoices="true">
                      <saw:customWidth width="120" using="custompixels"/></saw:promptUIControl>
                   <saw:promptDefaultValues type="specificValue" usingCodeValue="false">
                      <saw:promptDefaultValue>Standard</saw:promptDefaultValue></saw:promptDefaultValues>
                   <saw:constrainPrompt type="none"/>
                   <saw:setPromptVariables>
                      <saw:setPromptVariable location="value" type="none" variableFormula=""/></saw:setPromptVariables>
                   <saw:promptSource xsi:type="saw:allChoices"/></saw:prompt></saw:individualPrompts>
             <saw:customWidth width="120" using="custompixels"/></saw:promptStep></saw:prompts></saw:report>

  • Ashu Raj-Oracle
    Ashu Raj-Oracle Rank 2 - Community Beginner

    I have added below 3 additionally:

             <saw:column xsi:type="saw:regularColumn" columnID="c3dd2a9d64f333c0f">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Check Status lookup Code"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c43c526c42ac0ff56">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name"</sawx:expr></saw:columnFormula></saw:column>
             <saw:column xsi:type="saw:regularColumn" columnID="c7c62329b263ea8b0">
                <saw:columnFormula>
                   <sawx:expr xsi:type="sawx:sqlExpression">"Payables Payments - Payment History Real Time"."- Payment Information"."Payment Status"</sawx:expr></saw:columnFormula></saw:column>

     

  • MaartenDIF
    MaartenDIF Rank 4 - Community Specialist

    Hi Ashu,

    These are all the unique combination I am getting in my data:
     

    Payment Status CASE WHEN Payment Status Code IS NULL THEN Payment Status Name ELSE Payment Status END Payment Status Name
        Not paid
    Cleared Cleared Fully paid
        Fully paid
    Negotiable Negotiable Fully paid
    Voided Voided Fully paid

    So unfortunately, it does not work. My desired result is as follows:

    Payment Status CASE WHEN Payment Status Code IS NULL THEN Payment Status Name ELSE Payment Status END Payment Status Name
      Not paid Not paid
    Cleared Cleared Fully paid
        Fully paid
    Negotiable Negotiable Fully paid
    Voided Voided Fully paid

     

    Kind regards,
    Maarten

  • MaartenDIF
    MaartenDIF Rank 4 - Community Specialist

    Hi Ashu,

    I do not understand. Where should I put this code?

    Kind regards,
    Maarten

  • Ashu Raj-Oracle
    Ashu Raj-Oracle Rank 2 - Community Beginner

    I added these 3 fields (in the code xml) so that we can see the values of these side by side the derived field(having case condition).