Purchase Request (PR) raised by purchase type

Modified 2 weeks ago

Rajesh Narayanan

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.

Did you find the article helpful?

Powered by HelpDocs (opens in a new tab)