Creating Procurement spend analysis report by department and product category
When a company's procurement manager wants to understand procurement patterns from purchase requests, they can measure total spending by department and product category. This analysis can optimize procurement processes, negotiate better pricing with suppliers, and identify potential cost-saving opportunities.
Creating a dataview for purchase request analysis
To begin this analysis, we need to construct a dataview that combines data from - the Purchase_Request process table and its two child tables, Catalog_Items and Non_Catalog_Items. The following SQL query demonstrates how to create this dataview:
SELECT
pr."Purchase_Request_Number"AS "Purchase request number",
pr."Requester_Name" AS "Requester name",
pr."Department" AS "Department",
prcat."Catalog_Item":Product_Name:: string AS Catalog_Item,
prcat."Catalog_Item_Quantity" AS Catalog_Item_Quantity,
prcat."Catalog_Item_Amount.value" AS Catalog_Amount,
prncat."ProductService_Name" AS Non_Catalog_Item,
prncat."Quantity" AS Non_Catalog_Item_Quantity,
prncat."Item_Amount.value" AS Non_Catalog_Amount,
pr."Total_PR_amount.value" AS TOTAL_PR_VALUE
FROM
"Purchase_Request" AS pr
LEFT OUTER JOIN "Purchase_Request.Catalog_Items_table" prcat ON pr."_id"=prcat."_instance_id"
LEFT OUTER JOIN "Purchase_Request.Model_7MyXBt9-2u" prncat ON pr."_id"=prncat."_instance_id"In this query, we select specific fields from the Purchase_Request table and its child tables, Catalog_Items and Non_Catalog_Items. The SQL JOIN operation connects the data from these tables, enabling us to consolidate relevant purchase request information.
Note:
You must use the respective table ID of the process while joining the child tables in your account. In this case, Purchase_Request is the table in the account. Purchase_Request.Catalog_Items_table is the child table containing the list of catalog items and Purchase_Request.Model_7MyXBt9-2u is the child table containing the list of non-catalog items. These table IDs will vary in each account.
Query breakdown
The following table describes each part of the query. The LEFT JOIN operation brings together data from multiple tables, with a primary focus on the Purchase_Request table.
Actual column name |
Alias name used in the query |
Description |
pr."Purchase_Request_Number |
Purchase request number |
The purchase request number for each item request raised in the process. |
pr."Requester_Name" |
Requester name |
The name of the requester. |
pr."Department" |
Department |
The department of the requester who requested the item. |
prcat."Catalog_Item":Product_Name:: string |
Catalog_Item |
The purchase request of all the items under the catalog list. Since it is a JSON column, this query is used to parse it. |
prcat."Catalog_Item_Quantity" |
Catalog_Item_Quantity |
The number of catalog items. |
prcat."Catalog_Item_Amount.value" |
Catalog_Amount |
The amount of each item requested under catalog items. |
prncat."ProductService_Name" |
Non_Catalog_Item |
The purchase request of all the items under the non-catalog list. |
prncat."Quantity" |
Non_Catalog_Item_Quantity |
The quantity of non-catalog items. |
prncat."Item_Amount.value" |
Non_Catalog_Amount |
The amount of each item requested under non-catalog items. |
pr."Total_PR_amount.value" |
TOTAL_PR_VALUE |
The estimated total amount of the items requested for purchase in the process. |
Purchase_Request |
pr |
The "Purchase_Request" table. |
LEFT OUTER JOIN "Purchase_Request.Catalog_Items_table" prcat ON pr."_id"=prcat."_instance_id" |
prcat |
This left outer join links the "Purchase_Request" table to the "Catalog_Items" table using the unique IDs to associate purchase requests with catalog items. |
LEFT OUTER JOIN "Purchase_Request.Model_7MyXBt9-2u" prncat ON pr."_id"=prncat."_instance_id" |
prncat |
This left outer join links the "Purchase_Request" table to the "Non_Catalog_Items" table using the unique IDs to associate purchase requests with non-catalog items. |
_instance_id |
Item id |
The unique ID of the items in the process. |
After writing the query, run it and view your data. Once done, publish the query. Once the dataview is created, the procurement manager can proceed to create a meaningful report for purchase request analysis.
Creating spend analysis report
Follow the below steps to create a purchase request by purchase type report:
Navigate to Analytics and click the Reports tab.
Click Create report.
Choose the above created dataview in the dropdown given while creating a report.
Provide a name for your report.
Select the report type - Pivot or Chart according to your preference and click Create.
Configuring the pivot table
Values: Total_PR_Value
Rows: Department, Requester_Name
Grand Totals: Show grand totals
Value Calculations: Percent of grand total
After configuration, click Save and view the report. In this report, procurement managers can analyze the spend pattern across departments. This analysis can be further drilled down to the user level to see the percentage of spend by each user who raised purchase requests.
Configuring the chart report
In the report configuration page, select the Stacked vertical bar chart. Add the following settings to get a detailed report on the number of purchase requests in each department.
Dimension: Department
Measure: Catalog_Amount, Non_Catalog_Amount
Sort: Department (Ascending)
This report compares the catalog item spend value, non-catalog item spend value, and total spend value by department.
