RIS-SC-1019

Inventory Sales Analysis by Category

Description

This query retrieves data related to stock items, including their category, SKU, current and minimum levels, units sold, orders, and total sales within the last 6 months. It utilizes various tables such as StockItem, OrderItem, Order, StockItem_ExtendedProperties, StockLevel, and StockLocation.
image 13
male female graphic designers interacting computer
female web designer taking notes office
How to generate the report: a step-by-step guide
  1. Inventory Sales Analysis by Category Report in the Query type drop-down menu.
  2. Click the Generate Report button.
  3. Select the Time Zone and click the Download (CSV file) button when downloading the report.

Summary

This SQL query provides insights into stock item performance, including its category, SKU, current and minimum levels, units sold, orders, and total sales within the last 6 months. It calculates minimum level as half of the units sold in the last 6 months and retrieves data from various related tables. The query is designed to offer comprehensive analysis of stock item activity over a specified period.

Reported Values

The report returns the following values:
Column NameDescription
RNRow number over partition by PropertyValue ordered by sum of Quantity sold descending
pCategoryCategory of the stock item
SKUStock Keeping Unit (SKU) of the item
Current LevelCurrent level of the stock item in the default location
Minimum LevelMinimum level of the stock item (half of units sold in the last 6 months)
Units Sold in last 6 monthsTotal units sold for the item in the last 6 months
Orders in last 6 monthsNumber of orders containing the item in the last 6 months
Total Sales in 6 monthsTotal sales value for the item in the last 6 months
young team coworkers working project

Parameters

@Source: The source parameter for filtering inventory items.

@SubSource1: The first subsource parameter for filtering inventory items.

@SubSource2: The second subsource parameter for filtering inventory items.