Using out-of-the-box queries: Commerce
The following are out-of-the-box queries related to commerce, organized by category. Use these predefined queries to easily retrieve commerce data.
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| PRODUCTS | |||
| All products | Lists the 10 products with the highest quantities ordered in the current calendar year. | $/Samples/Dashboards/ Commerce/Product Sales |
Commerce dashboard |
| Web product details | Returns all web products with links to the product details. | ||
| Popular products | Lists the 10 products with the highest quantities ordered in the last 30 days. | $/Common/Tasks/Queries/ Staff dashboard alerts |
Staff popular products this month alert (Commerce > Dashboard) |
| Commerce products | Returns all sales type products with details like status, publish date, income account, and organization. | $/Common/Query Sources/Commerce |
Data source for various Report Writer reports |
| Sales Type Products | Returns active sales type products, with pricing tiers, kit/premium status, and whether the price comes from components. | $/Commerce/DefaultSystem/ Queries/Product/ProductsByType |
|
| Qualifying products | Lists products which qualify purchasers for product purchase groups and their post-purchase information. |
$/ContactManagement/DefaultSystem/ Queries/Groups/Product purchase groups |
Groups (Community > Groups) |
| PURCHASES | |||
| Progress Tracker - Sales Revenue This Year | Counts the product sales revenue for the current calendar year and compares it to the revenue from last year as a goal. | $/Samples/Dashboards/ Commerce/Product Sales |
Commerce dashboard
|
| Progress Tracker - Item Sales This Year | Displays products sold, each with a dropdown showing buyers and quantities. | ||
| Progress Tracker - Unique customers this year | Displays unique customers this year with their total orders, quantity purchased, and revenue generated. | ||
| Revenue by source code | Displays total revenue by campaign source code, along with descriptions and response counts. | $/Samples/Dashboards/Commerce | |
| Open credits | Displays all current open credits with names and total credit amounts. | ||
| All customers | Displays top 10 customers with the quantity purchased and corresponding revenue generated. | $/Samples/Dashboards/Commerce/ Product Sales/Top customers |
|
| By organization | Displays top 10 customers by organization, showing the quantity purchased and corresponding revenue. | ||
| Qualifying products | Lists products which qualify purchasers for product purchase groups and their post-purchase information. | $/ContactManagement/DefaultSystem/ Queries/Groups/Product purchase groups |
|
| ORDERS | |||
| Progress Tracker - Orders This Year | Tracks the number of orders placed this year and compares it to a particular goal. | $/Samples/Dashboards/ Commerce/Product Sales |
Commerce dashboard
|
| Orders by Member Type This Year | Compares the number of product orders placed by various member types within the current calendar year. | ||
| Orders By Product Category This Year | Compares quantity of products ordered for the current calendar year by product category. | ||
| Open orders | Displays open orders with order number, date, customer, stage, charges, and balance. | ||
| Find orders | Allows users to search orders by number, type, date, customer, item, stage, and organization. | $/OrderManagement/DefaultSystem/ Queries/Find orders |
Order search (Commerce > Find orders) |
| Find premium orders | Allows users to search for premium orders using filters such as order number, type, date, customer name, and item name. | ||
| Pro-forma orders ready to convert | Filters pro forma orders by order number, date, and customer details, displaying order information. | $/OrderManagement/DefaultSystem/ Queries/Convert quotes |
Convert quotes (Commerce > Process orders > Convert quotes) |
| Orders in BACKORDER stage | Filters backorders by order number, date, and customer details, displaying order information. | $/OrderManagement/DefaultSystem/ Queries/Release backorders |
Release backorders (Commerce > Process orders > Release backorders) |
| Orders in SHIPMENT stage | Allows users to search for orders by number, date, and customer details to generate packing slips. | $/OrderManagement/DefaultSystem/ Queries/Shipping papers |
Ship orders (Commerce > Process orders > Ship orders) |
| Orders in SHIPPAPER stage | Filters orders ready to ship by order number, date, and customer details, displaying relevant shipping information. | $/OrderManagement/DefaultSystem/ Queries/Ship orders |
Print shipping papers (Commerce > Process orders > Print shipping papers) |
| Order data | Lists orders with details such as order date, number, type, total amount, balance, and shipping information. | $/Common/Query Sources/Commerce | Data source for various Report Writer reports |
| Order lines | Displays order line details, including item, price, quantity, and billing and shipping information. | ||
| Recent orders | Lists all orders placed in the last 7 days. | $/Common/Tasks/ Queries/Staff dashboard alerts |
Staff recent orders alert (Commerce > Dashboard) |
| PROMOTIONS | |||
| Top promotion codes | Displays top promotions with promo code, usage count, and revenue after discount. | $/Samples/Dashboards/ Commerce/Product Sales |
Commerce dashboard |
| Promotion usages | Displays promotion usage with order date, number, promo code, discount, and total before discount. | $/Common/Query Sources/Commerce | Promotion analysis report |
| Promotions | Displays promotions with their code, begin and end dates, and whether they apply automatically. |
Promotions search (Commerce > Promotions) |
|
| INVENTORY | |||
| Low inventory products | List of products with low inventory. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff low inventory alert (Commerce > Dashboard) |
| Inventory receipts | Allows users to search inventory receipts by date, transaction, reference number, and description. | $/Commerce/DefaultSystem/ Queries/Inventory |
Inventory receipt search (Commerce > Inventory receipts) |
| Orders affected by posting inventory receipts | Displays orders impacted by posting inventory receipt with order and item details. |
Inventory receipt detail (Commerce > Inventory receipts > select Transaction) |
|
| INVOICES | |||
| Due soon | Displays invoices due in the next 15 days with invoice number, date, customer, and balance. | $/Samples/Dashboards/ Commerce/Product Sales/Invoices |
Commerce dashboard |
| Past due | Displays past due invoices with invoice number, due date, customer, description, and balance. | ||
| PRICING GROUPS | |||
| Product pricing usages | Lists all products that use the pricing group specified in the URL parameter. | $/Common/Queries/ Commerce/PricingGroup |
Pricing group detail |
| Event pricing usages | Lists all event functions that use the pricing group specified in the URL parameter. | ||