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

Add column to OTBI using Advanced SQL Clauses

Received Response
5
Views
4
Comments
Mitali Meshram
Mitali Meshram Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

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.

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    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!

  • Mitali Meshram
    Mitali Meshram Rank 1 - Community Starter

    Hi Nathan,

    Thank you for your prompt response. Yes, the "Installment on Hold Indicator" displays as Y/N.

    The Criteria tab currently appears as shown.

    Is there a way to add a new column to the report?

    Regards,

    Mitali

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi,

    It is not a report in OTBI. You are editing an analysis in OTBI. This is a custom analysis built using the advanced technique based on some custom logical SQL to join different subject areas rather than build using the wizard from individual subject areas. Payables Instalments and Suppliers.

    Because you have used this advanced technique you cannot easily add columns because you are using a "custom subject area" built as a view on the inner query from the 2 subject areas with a left outer join.

    You can edit formulas in the existing columns (if its ok to sacrifice one). Or you can edit the XML and/or SQL in the advanced tab. Or you will need to go back to the 2 original analysis on each individual subject area you had to begin with, to edit the columns, before joining them again, then rebuild analysis from the custom logical SQL.

    Also I would recommend doing your deep links using criteria tab to edit column properties interaction to add action links as per the user guide rather than hard code html in your SQL for invoice link.

  • Mitali Meshram
    Mitali Meshram Rank 1 - Community Starter

    Thank you for the detailed explanation. Since rebuilding the analysis would remove some of the existing customizations, I would prefer to add the new column using either the XML or the logical SQL in the Advanced tab. Could you please advise on the best approach for adding a column this way, and any key considerations or best practices I should keep in mind to avoid impacting the current customizations?

    Regards,

    Mitali.