Oracle Transactional Business Intelligence

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

OTBI Column Formula - TO derive values based on a report prompt/parameter

Received Response
152
Views
7
Comments

Hi Experts,

We have created an OTBI analysis to get data for the ranking of suppliers based on their total invoice value.Further, this should be able to run based on a invoice date range as well.

Please advice as to how we can have a column formula mentioning that the invoice values should be grouped based on the suppliers and further the invoice creation that will be selected in prompt while running the OTBI Analysis.

Currently we can group the invoices based on supplier. However, we face a difficulting in filtering the data based on the invoice date selected at report running prompt.

Thanks,

VC

Tagged:

Answers

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 5 - Community Champion

    Hi

    Please provide us the below details for our review

    a) catalog using the archive option

    b) NQSQuery.log of the report. Follow the below steps

    Doc ID 2225909.1 Fusion Applications: How to Get nqquery Using Manage Session

  • 231094
    231094 Rank 2 - Community Beginner

    Hi Rajasekhar

    Please find the requested details and thank you for the support provided.

    Thanks,

    VC

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 5 - Community Champion

    Hi

    Thank you for the details.

    Please try the below for group by clause

    Example:sum("Procurement - Purchasing Real Time"."Purchase Order Header Detail"."Purchase Order Amount" BY "Procurement - Purchasing Real Time"."Supplier Profile"."Supplier Name")

  • 231094
    231094 Rank 2 - Community Beginner

    Hi Rajasekhar,

    We have tried that and it gives the below error.

    Formula syntax is invalid.
    [nQSError: 10058] A general error has occurred. (HY000)
    [nQSError: 43113] Message returned from OBIS. (HY000)
    [nQSError: 27045] Nonexistent column: "Supplier Profile"."Supplier Name"."Purchase Order Header Detail"."Creation Date". (HY000)
    SQL Issued: SELECT COUNT(DISTINCT "Purchase Order Header Detail"."Order" BY "Supplier Profile"."Supplier Name"."Purchase Order Header Detail"."Creation Date") FROM "Procurement - Purchasing Real Time"
    OK (Ignore Error)

    Please Advice

    Thanks,

    VC

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited April 30

    Hi VC

    You said you needed sum of invoice amount. But now you are counting volume of purchase orders. But assume you still want the aggregate for each supplier? Why did you add created date? Anyway the column in your BY clause is not a column hence your syntax error is expected. If you want 2+ columns in BY then they need to be comma separated list. I have removed created date to fix it. And changed supplier name to number because it is a unique identifier. And use the header id since purchasing document of type purchase order is also not unique across sold to legal entity. Maybe you have configured to concatenate sold to legal entity as a prefix to your supplier numbers in which case put it back - or concatenate that field in the count.

    https://docs.oracle.com/en/cloud/saas/procurement/24b/oedmp/pozsuppliers-21547.html#pozsuppliers-21547

    POZ_SUPPLIERS_U1
    Unique
    VENDOR_ID

    POZ_SUPPLIERS_U2
    Unique
    SEGMENT1 (supplier number)

    https://docs.oracle.com/en/cloud/saas/procurement/24b/oedmp/poheadersall-4282.html#poheadersall-4282

    PO_HEADERS_U1 Unique Default PO_HEADER_ID
    PO_HEADERS_U2 Unique Default SEGMENT1, TYPE_LOOKUP_CODE, CASE WHEN "TYPE_LOOKUP_CODE" = 'STANDARD' THEN "SOLDTO_LE_ID" ELSE "PRC_BU_ID" END

    select all "Purchase Order Header Detail"."Order" as purchase_order_reference 
    , cast("Purchase Order Header Detail"."PO Header ID" as character) as purchase_order_identifier
    , count(DISTINCT 
    "Purchase Order Header Detail"."PO Header ID" 
    by "Supplier Profile"."Supplier Number" 
    ) as n
    from "Procurement - Purchasing Real Time"
    

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi, Ok I re read your original question. To rank supplier by invoice amount but only for invoices in a date range. I think first you need a query to get all the child payables transactions that make up the invoice amount on your purchase orders filter to select only invoices created in a date range from your prompt with a sum of the invoice amounts then a sum those aggregate invoice amounts for each purchase order by supplier.

    In addition to trying to use narrow "Procurement - Purchasing Real Time" which may not have the invoice created dates you need take a look perhaps at using the wider subject areas "Procurement - Spend Real Time" and "Procurement - Procure To Pay Real Time". The latter includes purchase orders (ordered), purchase receipts (delivered), payables invoices (invoiced).

  • 231094
    231094 Rank 2 - Community Beginner

    Hi Nathan,

    Thank you for your inputs and sorry for the confusion. I have mistakenly mentioned it as Invoice Amount instead of PO values. The requirement is explained below.

    We have created an OTBI analysis to get data for the ranking of suppliers based on their total purchase order value.Further, this should be able to run based on a PO creation date range as well.

    Please advice as to how we can have a column formula mentioning that the PO values should be grouped based on the suppliers and further based on the PO creation date range that will be selected in prompt while running the OTBI Analysis.

    Currently we can group the POs based on supplier. However, we face a difficulting in filtering the data based on the creation date selected at report running prompt.

    Please Advice

    Thanks,

    VC