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

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

Vipusha Chandrakumaran

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,

Find more posts tagged with

Analysis

OTBI

Accepted answers

All comments

Rajasekhar Bandaru-Oracle

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

Vipusha Chandrakumaran

Hi Rajasekhar

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

NQSQuery.log

DEV Supplier Ranking Report.catalog

Thanks,

Rajasekhar Bandaru-Oracle

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

Vipusha Chandrakumaran

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,

Nathan CCC

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

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

Vipusha Chandrakumaran

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,