Oracle Analytics and AI

Mitali Meshram

In my OTBI report, I want to add a column from the Payables Invoices - Installments Real Time subject area using the following CASE statement:
CASE WHEN "Invoices Installment Details"."Installment on Hold" = 'Yes' THEN 'Y' ELSE 'N' END.

However, the report is customized and the subject area is not visible.

1. I attempted to add a new calculated measure under the Results tab, but received an error indicating that the column is not defined in the subquery.

Error:

[nQSError: 43113] Message returned from OBIS. (HY000)

[nQSError: 27045] Nonexistent column: "Invoices Installment Details"."Installment on Hold". (HY000)

SQL Issued: SELECT CASE WHEN "Invoices Installment Details"."Installment on Hold" = 'Yes' THEN 'Y' ELSE 'N' END FROM (SELECT PIIRT.business_unit business_unit , PIIRT.payment_type payment_type , PIIRT.supplier_name supplier_name , PIIRT.supplier_site supplier_site , PIIRT.supplier_number supplier_number , PIIRT.supplier_bank_account supplier_bank_account , PIIRT.invoice_currency invoice_currency , PIIRT.invoice_date invoice_date , PIIRT.invoice_number invoice_number , PIIRT.validation_status validation_status , PIIRT.invoice_amount invoice_amount , PIIRT.po_number po_number , PIIRT.amount_due amount_due , PIIRT.due_date due_date , (CASE WHEN PIIRT.business_unit = 'INS JP BU' then round(PIIRT.amount_due_in_functional_currency,0) else PIIRT.amount_due_in_functional_currency end) amount_due_in_functional_currency , PIIRT.invoice_approval_status invoice_approval_status , PIIRT.invoice_link invoice_link , PIIRT.invoice_id invoice_id , PIIRT.legal_entity legal_entity , PIIRT.payment_status payment_status , PIIRT.po_requestor po_requestor , SSRT.supplier_iban supplier_iban , Source FROM (SELECT "Business Unit"."Business Unit Name" business_unit , "Invoices Installment Details"."Payment Method" payment_type , "Supplier"."Supplier Name" supplier_name , "Supplier Site"."Supplier Site Code" supplier_site , "Supplier"."Supplier Number" supplier_number , "Invoices Installment Details"."Bank Account Number" supplier_bank_account , "Payables Invoices - Transactions Real Time"."- Invoice Amounts"."Invoice Currency" invoice_currency , "Payables Invoices - Transactions Real Time"."- General Information"."Invoice Date" invoice_date , "Payables Invoices - Transactions Real Time"."- General Information"."Invoice Number" invoice_number , "- Reference Information"."Validation Status" validation_status , "Payables Invoices - Transactions Real Time"."- Invoice Amounts"."Invoice Amount" invoice_amount , IFNULL("- General Information"."Identifying PO",' ') po_number , "Invoices Installment Amounts"."Remaining Amount In Entered Currency" amount_due , "Invoices Installment Details"."Due Date" due_date , "Invoices Installment Amounts"."Unpaid amount in Base Currency" amount_due_in_functional_currency , "Payables Invoices - Transactions Real Time"."- Invoice Approval Information"."Approval Status" invoice_approval_status , ''||'/fscmUI/faces/deeplink?objType=AP_VIEWINVOICE&action=VIEW&objKey=InvoiceId='||"- General Information"."Invoice ID" invoice_link , "- General Information"."Invoice ID" invoice_id , "Legal Entity"."Legal Entity Name" legal_entity , "- Reference Information"."Payment Status Name" payment_status , "- General Information"."Requester" po_requestor , "- Reference Information"."Invoice Source Name" Source FROM "Payables Invoices - Installments Real Time" WHERE "- Reference Information"."Payment Status Name" IN ('Not paid','Partially paid')) PIIRT LEFT OUTER JOIN (SELECT "- Supplier Profile"."Supplier Number" supplier_number , "- Supplier Profile"."Supplier Name" supplier_name , "- Supplier Site Details"."Supplier Site Code" supplier_site , "- Procurement BU"."Procurement Business Unit Name" business_unit , IFNULL("- Supplier Site Bank Accounts"."IBAN","- Supplier Bank Accounts"."IBAN") supplier_iban , IFNULL("- Supplier Site Bank Accounts"."Account Number","- Supplier Bank Accounts"."Account Number") supplier_bank_account FROM "Supplier - Supplier Real Time" WHERE IFNULL("Supplier - Supplier Real Time"."- Supplier Site Bank Accounts"."Assignment Inactive Date", CURRENT_DATE)>=CURRENT_DATE) SSRT ON PIIRT.supplier_number = SSRT.supplier_number AND PIIRT.supplier_name = SSRT.supplier_name AND PIIRT.supplier_site = SSRT.supplier_site AND PIIRT.business_unit = SSRT.business_unit AND PIIRT.supplier_bank_account = SSRT.supplier_bank_account ) OUTER_TABLE1

OK (Ignore Error)

2. I also tried adding the column using the Advanced tab under Advanced SQL Clauses, but encountered another error.

Is this the correct approach? Please refer to the image below for clarification.

What is the correct way to add a new column to a customized OTBI report? Please provide guidance.

Nathan CCC

Hi, This is an analysis in OTBI (blue) not a (publisher) report in OTBI (yellow). The correct approach for novice authors is to add a column using the criteria tab (the advanced tab is for expert users). Add any presentation column, then select column (gear icon), select edit formula. You can build up a custom calculation using functions - for a wizard use the fx button. In this case you can use the Bins to generate your case statement. Also instead of a case, if the column is configured in the subject area as a code name pair, consider using descriptor_idof() function to get the code for a name. Or in this case you have both the code and name cols so use the code column "Installment on Hold Indicator" ("Invoices Installment Details"."Hold Flag"). You do not need a custom column as the column you need already exists!