Purchase Request (PR) raised by purchase type
Modified 9 months ago
Mekha Mathew
Kissflow Work Platform:
- Small Business
- Corporate
- Enterprise
If a company's procurement manager wishes to examine purchase requests in terms of the number of requests raised, the type of request, and the items requested, they have to join two tables using lookups to obtain the report covering data from two tables.
With Kissflow Analytics, users can now create a dataview and then a report over that dataview to retrieve the list. This is a quick and straightforward method.
The following is a sample query for creating this dataview:
Select
pr."Purchase_Request_Number",
pr."Requester_name",
pr."Requester_department",
pr."Type_of_Purchase",
prcat."Item" as "Catalog_Item",
prcat."Quantity" as "Catalog_Item_Quantity",
prcat."Item_Amount.value" as "Catalog_Amount",
prncat."Item_1" as "Non_Catalog_Item",
prncat."Quantity_1" as "Non_Catalog_Item_Quantity",
prncat."Item_Amount_1.value" as "Non_Catalog_Amount",
pr."PR_Estimate.display_value" as "TOTAL_PR_VALUE"
from
"Purchase_Request" as "pr"
left outer join "Catalog_Items" prcat on pr."_id" = prcat."_instance_id"
left outer join "Non_Catalog_Items" prncat on pr."_id" = prncat."_instance_id"
In the above query, Purchase_Request is the process table, and Catalog_Items and Non_Catalog_Items are its child tables. An SQL join is performed between the process table and its child tables.
After the dataview is created, the procurement manager can use the dataview to create a pivot report with the following configuration.
