OTBI Column Formula - TO derive values based on a report prompt/parameter
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
Answers
-
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
0 -
0
-
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")
0 -
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
0 -
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.
POZ_SUPPLIERS_U1
Unique
VENDOR_IDPOZ_SUPPLIERS_U2
Unique
SEGMENT1 (supplier number)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" ENDselect 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"
0 -
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).
0 -
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
0