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

Calculated column not giving values

MaartenDIF

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

Find more posts tagged with

Reporting

OTBI

Analyses

Accepted answers

All comments

Ashu Raj-Oracle

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

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

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" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
<saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""Procurement - Purchasing Real Time"" 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=""Procurement - Purchasing Real Time""/></saw:report>

Ashu Raj-Oracle

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

MaartenDIF

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

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

<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=""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>

Ashu Raj-Oracle

I have added below 3 additionally:

MaartenDIF

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

Hi Ashu,

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

Kind regards,
Maarten

Ashu Raj-Oracle

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).