Business Intelligence
Alan Gilchrist
Shon Joy (Unlicensed)
Anisha P M (Unlicensed)
Sarah Stalley (Unlicensed)
The business intelligence options can be opened from the main menu, most menus contain the two icons on the far right in the ‘Biz intelligence’ group
Enquiries (Business Intelligence Enquiry)
Clicking on the Enquiry icon which is on many of the transactional menus will open a separate menu structure allowing you access to the Business Intelligence Enquiries.
These enquiries provide you with a powerful search facility and the ability to export the data to CSV or Excel, however, you can simply use them in a live environment all the time by refreshing the output.
This option opens a new window on your desktop, when you click back to your main application window the enquiry menu will be located on the bottom task bar, you do not need to re-open a further window unless you need it.
The enquiries screen opens to a simple menu structure where you choose the report you wish to run by selecting from the drop-down menus.
Each report provides a unique number and that number starts with the area in which the report is based e.g. 3xx is Stock Holding.
List of Enquiries
Sub Option | Column Names |
|---|
Sub Option | Column Names |
|---|---|
100 Customer List | Account AccountName Address1 Address2 Address3 Address4 PostCode TelephoneNumber Fax
|
101 First / Last Invoice Raised | First Invoice Number First Invoice Date Last Invoice Number Last Invoice Date Account AccountName Account Manager
|
102 Mailing List Summary | Mailing List Name Date Created Title First Name Sur Name Full Name Account Account Name Address1 Address2 Address3 Address4 PostCode Country Name Telephone Home Telephone No Mobile Telephone No Fax
|
103 Mailing List Detailed | MailingListName DateCreated Title FirstName SurName FullName Account AccountName Address1 Address2 Address3 Address4 PostCode Country_Name Telephone HomeTelephoneNo MobileTelephoneNo Fax Account Manager Area Type Outlet Source Division Classification Preferred_Contact Last_Sale_Date Spend_This_Year Spend_Last_Year Dear Memo International_Client MailingList_Member Attend_Tastings Archived Currency_Sign Currency_Name Pricelist_Group_Name
|
104 Customer List - Analysis | Account AccountName Street Building Locality City PostCode Country_Name Account_Contact Account_Email TelephoneNo MobileNo Account Manager Area Type Outlet Source Division Classification Last_Sale_Date Spend_This_Year Spend_Last_Year Memo International_Client Currency_Sign Currency_Name Pricelist_Group_Name DateCreated LastUpdated StatusDescription
|
Sub Option | Column Names |
|---|
Sub Option | Column Names |
|---|---|
200 Product Details | ProductCode |
201 Product Prices | Country |
202 Customer Special Prices | Account AccountName Sales Person Country Region ProductCode ProductDescription NonEnglishDescription Year SPC SS Producer Name Price List Qualifying Qty DP Case Price DP Bottle Price UB Case Price UB Bottle Price Expires Last Product Receipt Last Movement
|
203 Standard Cost Comparison | ProductCode Description NonEnglishDescription Year SPC SS Producer Name Currency Last Buying Price Disc% ExchangeRate Sterling Buying Price Standard Cost Last Product Receipt Last Movement
|
204 Sales Price List | UID_PL PL_Name PL_Description PL_GroupName PL_Currency PL_Starts PL_Ends PriceListType Live Promo EP incTAX PCode Description Descriptions2 Vintage SPC SS Band_From Band_To DP_Case DP_Single UB_Case UB_Single TSingles_Available
|
205 Price Lists inc Availabe Stock In Std Whse | UID_PL PL_Name PL_Description PL_GroupName PL_Currency PL_Starts PL_Ends PriceListType Live Promo EP incTAX PCode Description Descriptions2 Vintage SPC SS Band_From Band_To DP_Case DP_Single UB_Case UB_Single WareHouseCode TSingles_Available
|
206 Product Buy Prices | ProductCode ProductDescription NonEnglishDescription Year SPC SS UBCost/Unit UBCost/Sgl DPCost/Unit DPCost/Sgl Delisted Main Supplier AC Main Supplier SupCurr SupUB/Unit SupUB/Sgl Producer PGrp_Code PGrp_Name Min_Stk Lead_Time
|
997 Pivot | productcode productgroup year month warehousecode 202104 202105 202106 202107 202108 202109 202110 202111 202112 202201 202202 202203 202204
|
998 Prod Sale Q | ProductCode TSgl_Stock SPC ProductDescription ProductGroup CountryOfOrigin Year Month TSgl_Sold WareHouseCode yyyymm
|
Sub Option | Column Names |
|---|
Sub Option | Column Names |
|---|---|
300 Stock Record Card | Warehouse Group Code Product Group Product Description AltLanguage_Description Producer Vintage SS SPC Rotation Opening Stock Closing Balance CasePrice Movement Type Movement Date Account Reference OrderNo Total Singles ProductCost Insurance Cost Freight Cost ExciseDuty Customs Duty RH & D Rent Delivery IsConsignmentStock Rotation Notes Rotation Warehouse Notes
|
301 Frozen Stock | User Product Description AltLanguage_Description ProducerName Year SPC SS Date Frozen Reason Rotation Bottles Frozen
|
302 Summary By Region | RegionID Region Cases IBCost DPCost EPCases EPCost ProductCost(non EP) CoreCost CoreForecast FineWineCost FineWineForecast
|
303 Stock Status Report with Whse | ProductCode Description AltLang_Description ProducerName Vintage SPC SS PhysicalStock MinStockLevel MaxStockLevel MinPurchaseOrderQuantity LeadTime Account AccountName Delisted WarehouseName
|
304 Customer Unpaid Reserve | Rep Name Customer Code Customer Name Product Code Product Description Vintage Bottles
|
305 Total Stock Available with Whse | Product Code |
306 Warehouse Product Stock | WareHouse Code |
307 Audit TrailYou must enter a Product Code to view the report | Warehouse GroupCode ProductGroup ProductCode Description SinglesPerUnit Rotation OpeningStock ClosingBalance CasePrice MovementType MovementDate Account Reference OrderNo TotalSingles ProductCost InsuranceCost FreightCost ExciseDuty CustomsDuty RH&D Rent Delivery IsConsignmentStock
|
308 Stock Turn Last X daysThis has a filter of No of days | ProductCode ProductDescription RotationNumber Singles SinglesPerUnit SingleSize RegionCode RegionDescription AnalysisCode AnalysisDescription
|
309 Valuation By Product | WarehouseName CountryName RegionDescription ProductCode Producer ProductDescription AnalysisDescription ProductGroupCode SinglesPerUnit PhysicalStock UnderBondValue DutyPaidValue EnPrimeurStock EnPrimeurValue ActualStockValue Duty Shipping+Insurance RH&D Rent UnderBondPricePerCase UnderBondPricePerBottle DutyPaidPricePerCase DutyPaidPricePerBottle StockLanded
|
310 Valuation By Rotation | WarehouseName CountryName RegionDescription ProductCode RotationNumber Producer ProductDescription NonEnglishDescription Year SPC SS AnalysisDescription ProductGroupCode PhysicalStock UnderBondValue DutyPaidValue EnPrimeurStock EnPrimeurValue ActualStockValue Duty Shipping+Insurance RH&D Rent UnderBondPricePerCase UnderBondPricePerBottle DutyPaidPricePerCase DutyPaidPricePerBottle StockLanded
|
311 Rotation Stock with Whse | Warehouse Rotation Code Description AltLanguage_Description ProducerName Year SPC SS Date Received TSingle Received TSingle Cases Singles
|
312 Paid Reserve Rotation Stock with Whse | Warehouse Rotation Code Description AltLanguage_Description ProducerName Vintage SPC SS Date Received TSingle Received TSingle Cases Singles ReserveCode ReserveName Cust PP per Case
|
313 Mixed Case Availability with Whse | Product Code Product Description NonEnglishDescription Year SPC SS Warehouse Singles Per Unit Total Singles Total On Order Total Frozen Total SOP Allocated Available Singles Delisted
|
314 Order Available Stock by Whse | Product Code Product Description NonEnglishDescription Year SPC SS Warehouse Total Singles Total On PO Total Frozen Total SOP Total SOP_BOM Allocated Available Singles
|
315 Stock Turn Last X days by Product | ProductCode ProductDescription Singles SinglesPerUnit SingleSize RegionCode RegionDescription AnalysisCode AnalysisDescription
|
316 Total In Stock by Product | Code Description NonEnglishDescription Year SPC SS FirstReceived LastReceived TSingleReceived TSingle Cases Singles
|
317 BOM Product Exploded Products List | BOM_Code BOM_Description Product_Code Product_Description Product_Qty_InBOM Product_TSingles
|
318 Duty Paid Stock List | Whse_Grp_Code Whse_Grp_Name Whse_Code Whse_Name Product Rotation Recd_Date Recd_TSingles DP FC Stock_TSingles SPC
|
319 Underbond Stock List | Whse_Grp_Code Whse_Grp_Name Whse_Code Whse_Name Product Rotation Recd_Date Recd_TSingles DP FC Stock_TSingles SPC
|
320 Reserve Stock D+V Value Check | Whse_Code Whse_Name CustomerAccount Product Rotation Recd_Date Recd_TSingles DP FC Stock_TSingles SPC DV_Value Value_Check
|
321 Product Stocks Avg Costs | Product ID ProductCode Description AltLanguage_Description Producer Year SingleSize SinglesPerUnit Cases Singles TSingles AverageCaseCost EarliestMovement LatestMovement
|
323 Consignment Warehouse Stocks | WareHouse Code Product Code Product Description NonEnglishDescription Year SPC SS Producer Name Singles Per Unit Single Size Vintage Cases Singles Total Singles Last Movement Last Product Receipt TSingles Moved Last 90d TSingles Moved Last 180d TSingles Moved Last 260d Price Lists Product Appears On
|
324 Total Stock Available with Std Price | Product Code Product Description AltLanguage_Description ProducerName Year SPC SS Warehouse Singles Per Unit Total Singles Total On Order Total Frozen Total SOP Total SOP_BOM Allocated Available Singles Delisted Std_Price_Case Std_Price_Single CountryName RegionDescription SubRegion ProductStyle ProductTypeAnalysisDescription
|
325 Stock Purchase Cost(AvailStk) | Product Code Rotation Number Date TSingle Recd Base Case Cost Producer Name Product Description Non English Description SPC SS Vint WCode WGCode
|
326 Stock Value Snap by Warehouse | WarehouseName UBStock_Value DPStock_Value EPStock_Value ActualStockValue Duty_Liability ShipInsurance_Liability RHDLiability RentLiability
|
327 Total Stock available with Average costs | Product Code Product Description AltLanguage_Description ProducerName Year SPC SS Warehouse Singles Per Unit Total Singles Total On Order Total Frozen Total SOP Total SOP_BOM Allocated Available Singles Delisted AverageCaseCost Std_Price_Case Std_Price_Single CountryName RegionDescription SubRegion ProductStyle ProductTypeAnalysisDescription
|
328 Stock with Landed Costs | WareHouseCode RotationNumber ProductCode ProductDescription PO_No Date_Recd Recd_Tsingles Stock_TSingles SPC Cost/Case Ins/Case Freight/Case RHD/Case Landed/Case ProductCode
|
329 Product Summary Despatched (last 7d) | ProductCode ProductDescription NonEnglishDescription ProducerName Vintage SPC SS(cl) MDate TSingles Cases Singles
|
330 Products Allocated | ProductCode ProductDescription NonEnglishDescription ProducerName SPC SS(cl) Vintage Alloc_Cse Alloc_Sgl EAN_Case EAN_Sgl UPC_Case UPC_Sgl
|
331 Rotation Stock with BBD / Last Movement Movement Type is Stock Despatch only and only showing rotations where there is still stock recorded on the stock record table and will only display if the rotation has a BBD recorded. | Product Code |
Sub Option | Column Names |
|---|
Sub Option | Column Names |
|---|---|
400 PO Lines | OrderType SupplierAccount SupplierName OrderDate PONo DateRequired ProductCode Description UnitsOrdered SinglesOrdered BuyingPricePerSingle TradeDiscount(%) Currency ExchangeRate
|
401 POs By Supplier | Account AccountName PurchaseOrderNumber SupplierRef OrderType OrderDate DateRequired DutyPaid IsFreeCirculation ProductCode Description SinglesPerUnit UnitsOrdered SinglesOrdered TotalSinglesOrdered BuyingPricePerSingle UnitORSingle GrossValue TradeDiscount(%) DiscountValue NetValue CurrencySign CurrencyName ExchangeRate FixedER GBPNetValue
|
402 POs By Product | ProductCode Description Account AccountName PurchaseOrderNumber SupplierRef OrderType OrderDate DateRequired DutyPaid IsFreeCirculation SinglesPerUnit UnitsOrdered SinglesOrdered TotalSinglesOrdered BuyingPricePerSingle UnitORSingle GrossValue TradeDiscount(%) DiscountValue NetValue CurrencySign CurrencyName ExchangeRate FixedER GBPNetValue
|
403 Goods Received (Multi Currency) | PO_UID PO No Product_Code VATCode Status SPC Recd_Date OrderedCases OrderedSingles ReceivedCases ReceivedSingles InvoicedCases InvoicedSingles CreditedCases CreditedSingles Returnedcases ReturnedSingles OrderLineValue TradeDiscount% BuyingPrice InvoiceBuyingPrice InvoiceDiscount% InvoiceSaved CreditSaved ReturnChanged ExchangeRate GRNAccrual BuyingPriceperSingle BuyingPriceperUnit InvoiceBuyingPricePerSingle InvoiceBuyingPricePerUnit VATRate Warehouse IsExciseDutyPaidInUK ProductDescription AltLangDescription Currency Supp_AC SuppName
|
404 Product Reorder Estimate | Code Description Year SS SPC Country Region Total_Singles Total_On_PO Total_Frozen Total_SOP Total_SOP_BOM Allocated Available_Singles CY30 CY90 PY30 PY90 Main_Supplier Lead_Time Min_Stk Max_Stk MOQ Days_to_End Order_By
|
405 Consignment Stock Despatches | MovementDate TSingles ProductCode P.Description P.Description2 SPC SS Vintage RotationNumber DateReceived
|
Sub Option | Column Names |
|---|
Sub Option | Column Names |
|---|---|
500 SO Lines | OrderType OrderDate OrderNo CustCode CustName CustRef ReleaseDate Proforma OnHold ProductCode ProductDescription Units Singles GrossValue DiscountValue NetValue GBPValue
|
501 SO Lines By Customer | Account AccountName SalesOrderNumber CustomerReference SalesOrderDate RequiredDate ReleaseDate IsOnHold ProductCode ProductDescription NonEnglishDescription Year SPC SS UnitsOrdered SinglesOrdered PricePerUnit PricePerSingle TotalSingles GrossValue TradeDiscount DiscountValue NetValue CurrencySign CurrencyName GBPValue
|
502 SO Lines By Product | ProductCode ProductDescription NonEnglishDescription Year SPC SS Account AccountName SalesOrderNumber CustomerReference SalesOrderDate RequiredDate ReleaseDate IsOnHold UnitsOrdered SinglesOrdered PricePerUnit PricePerSingle TotalSingles GrossValue TradeDiscount DiscountValue NetValue CurrencySign CurrencyName GBPValue
|
503 General Sales Reports | Trans Date Doc No Order No Type Of Sale Type Customer Country Code Customer Country Account Manager Code Account Manager Name Type Code Type Name Outlet Code Outlet Name Area Code Area Name Source Code Source Name Division Code Division Name Classification Code Classification Name Group Account Group Name Account No Account Name Supplier Supplier Name Producer Producer Name Country Code Country of Origin Region Code Region Name EP Campaign IsDutyPaid Prod Type Prod Type Description Product Group Prod Grp Description ProdAnalysisCode1 ProdAnalysisName1 ProdAnalysisCode2 ProdAnalysisName2 ProdAnalysisCode3 ProdAnalysisName3 Promotion Location Product Code Product Description Vintage Singles Actual Cases Cases @x Litres Sales Value Cost Value Duty CCT Rent RH&D Shipping Delivery RepGroup Division OutletCode OutletName OutletGroup TotalCost Margin GP%
|
504 Day Book | DaybookDate DaybookPrinted AccountNumber AccountName InvoiceDate InvoiceNumber Details Type ExchangeRate CurrencyName IsForeignCurrency NominalCode NetAmount VATCode VATAmount BaseCurrency_NetAmount BaseCurrency_TaxAmount
|
505 Salesman Report | Salesman ProductCode ProductDescription RotationNumber StockMovementDescription MovementDate Account No Reference Warehouse MovedQuantity ProductCostPerUnit Debit Credit InsuranceCostPerUnit FreightCostPerUnit DutyCostPerUnit CCTCostPerUnit RHDCostPerUnit RentCostPerUnit DeliveryCostPerUnit PostedBatchNo PostedDate
|
506 Sales By Product | ProductCode ProductDescription Case @x Litres CMonth/ProdTotalCases CMonth/ProdTotalSales CMonth/ProdTotalCost FYear/Start FYear/ProdTotalCases FYear/ProdTotalSales FYear/ProdTotalCost RPeriodStart RPeriodEnd RPeriod/ProdTotalCases RPeriod/ProdTotalSales RPeriod/ProdTotalCost NonTradeTotalSales TradeTotalSales TotalSales
|
507 Sales By Customer Source | AnalysisCode AnalysisGroupCode TotalSpend AverageOrderValue NoOfOrders_OrderTypeAnalysisMatchesCustomerSource NoOfOrders_OrderTypeAnalysisNotMatchesCustomerSource NoOfOrders_CustomersWithThisAsTheirSource NoOfRepeatCustomers_CustomersWithThisAsTheirSource NoOfNonRepeatCustomers_CustomersWithThisAsTheirSource
|
508 Credit Note / Invoice | OrderType InvOrCred OrderDate OrderNo CustCode CustName CustRef ReleaseDate Proforma OnHold ProductCode ProductDescription Units Singles GrossValue DiscountValue NetValue GBPValue
|
509 Stock Not Landed | Campaign ProductCode ProductDescription ProductType SinglesPerUnit NumPOs QtyLanded QtyNotLanded InvoicedQty CreditedQty TotalSinglesDespatched NotSold NotSoldValue£
|
510 Duty VAT Invoices | AnalysisCode AnalysisDescription SalesOrderNumber InvoiceNo InvoiceDate ExciseDutyPerSingle SageVATCode VATRate TotalVAT
|
511 Customers | Month MonthEnd MonthStartDate MonthEndDate AccountNo AccountName MonthCases @x Litres MonthSalesValue MonthTotalCost MonthMargin MonthTotalSales
|
512 Products | ProductCode ProductDescription Case @x Litres CMonth/ProdTotalCases CMonth/ProdTotalSales CMonth/ProdTotalCost FYear/Start FYear/ProdTotalCases FYear/ProdTotalSales FYear/ProdTotalCost RPeriodStart RPeriodEnd RPeriod/ProdTotalCases RPeriod/ProdTotalSales RPeriod/ProdTotalCost NonTradeTotalSales TradeTotalSales TotalSales
|
513 By Customer | Account Name OrderNo QtyOnOrder Campaign ProductCode ProductDescription PhysicalStock TotalEPOrderQty EPOrderQtyYetToBeReceived BalanceOfStockAvailableForSale DateStockLanded WareHouseName WareHouseCode SalesPricePerUnit PurchasePricePerUnit
|
514 By Product | Campaign ProductCode ProductDescription PhysicalStock TotalEPOrderQty EPOrderQtyYetToBeReceived BalanceOfStockAvailableForSale Account Name OrderNo QtyOnOrder DateStockLanded WareHouseName WareHouseCode SalesPricePerUnit PurchasePricePerUnit
|
515 Product Credits | Account AccountName SalesOrderNumber ProductCode ProductDescription SinglesCredited InvoiceNumber DateCredited
|
516 Discount on Orders | Order No Account Customer Ref Order Date Product Prod Description Total Converted Bottles Bottle Price Case Price Gross Line Discount Net
|
517 Surcharges on Invoices | Account AccountName SalesOrderNumber SalesOrderDate SurchargeCode Description SurchargePrice Quantity TotalSurchargePrice InvoiceNumber InvoiceDate
|
518 Outstandsing SO Summary | OrderType OrderNo CustCode CustName OrderDate RequiredDate ReleaseDate HeldDate HeldTo EnteredBy LastAmendedBy OrderLastAmendDate SO_SMCode SO_SMName Proforma OnHold BaseValue
|
519 SO Line Audit - Last 400 days | SalesOrderDate SalesOrderNumber LineNumber ProductCode Description AltLang_Description Producer Vintage SPC SS Rotation Cases_Ordered Order_Price_Case Std_Price_Case Singles_Ordered Order_Price_Single Std_Price_Single Price_List
|
520 OS SO Summary - Allocated | OrderType OrderNo CustCode CustName OrderDate RequiredDate ReleaseDate HeldDate HeldTo EnteredBy LastAmendedBy OrderLastAmendDate SO_SMCode SO_SMName Proforma OnHold BaseValue
|
522 Simple Order Outstanding | OrderType OrderNo CustCode CustName OrderDate RequiredDate SO_SMName NetValue
|
523 Sales details by Customer, Product, Invoice Date | CompanyID Account AccountName Address4 PostCode Outlet AccountManagerCode AccountManager CurrencyISO ProductCode ProducerName ProductDescription ProductDescription2 OrderUID SalesOrderNumber InvoiceNo DateInvoiced SinglesInvoiced SinglesCredited SinglesNotCredited TotalExVAT TotalBaseExVAT
|
524 Sales summary by Customer, Product, Invoice Date | CompanyID Account AccountName Address4 PostCode Outlet AccountManagerCode AccountManager CurrencyISO ProductCode ProducerName ProductDescription ProductDescription2 DateInvoiced SinglesInvoiced SinglesCredited SinglesNotCredited TotalExVAT TotalBaseExVAT
|
525 Sales summary by Customer, Invoice Date | CompanyID Account AccountName Address4 PostCode Outlet AccountManagerCode AccountManager CurrencyISO DateInvoiced SinglesInvoiced SinglesCredited SinglesNotCredited SubTotalBaseExVAT Surcharge TotalBaseExVAT
|
Reports
Reports are pre-formatted outputs from the system, usually, these are PDF reports which are printed for management information. The information in these comes from the BI Enquiries, and we can produce more reports here as and when customers request them.
Report Name | Description |
|---|
Report Name | Description |
|---|---|
Day Sales | Daily sales report |
Held Orders |
|
Product Stock Replenishment | Report based on the replenishment information on the BI enquiry. |
Customer Product Sales Period Comparison |
|
Customer Sales Period Comparison |
|
There is also an option to use the Report Designer, see the tab on Report Designer.
Report Designer
Report Designer is not included with standard Vision Commerce, this is an additional module and you need to pay for this module.
It is complex enough that we have to provide training and then there is a monthly charge.
For more information, there is an informational video located here -
Business Intelligence Module (BI)
Copyright Ontech Solutions 2017-2024. All rights reserved, no part may be replicated or distributed without the express permission of the owner.