1. Home
  2. Point-Of-Sale Software
  3. stock on hand sheet

stock on hand sheet

I want to make a stock on hand sheet formatted in excel for a range of product, i know i can use the SQL database as a data source and pull information into excel, but can anybody suggest how i could do this in more detail?

I want to write my description in excel and then put the ilc and have a colum with rms price, stock on hand and stock on order in colums, and then this would update live from the sql database and i could print it reqularly for staff use

is this possible?> and is there any sort of step by step anyone can reccomend?

Reply to
Philip Gass
Loading thread data ...

Will the RMS Quantity List Report not meet your needs? A couple of those columns may be missing, but check Show/Hide Columns as most should be available. Once you have the report showing the columns you want, just Export it to a comma separated file - you will be able to open it in Excel if that's what you want. If the Quantity List doesn't have all of the columns you need, log into Customer Source and check out the Reports Library.

Glenn Adams Tiber Creek C> I want to make a stock on hand sheet formatted in excel for a range of

Reply to
Glenn Adams [MVP - Retail Mgmt

HI Philip, you have already one report called Physical Inventory Report (Detailed) use it and do whatever customization you wanted to do. it is an active report with .grp extension, Remember whatever you wana do you can in this report so go ahead and no worries at all.

"Philip Gass" wrote:

Reply to
Akber Alwani

thanks guys - i will give these reports another bash..

I fancied the idea of formatting it nicely in excel and just having the fields update as required, but i suppose its going to be a lot more work than its proably worth!

Thanks!

Reply to
Philip Gass

My Quantity Report looks like this - i cannot find a couple of the columns i need, can somebody help with how i should customise this? I have tried to the best of my ability but it just results in errors!

I need the 'QTY on Order' 'Last ordered' 'Last Sold' and'Last Received' columns to be on, any pointers would be hugely helpfull!

//--- Report Summary --- //

Begin ReportSummary ReportType = reporttypeItems ReportTitle = "Item Quantity List" PageOrientation = pageorientationLandscape OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'View_DRS_Item_Sales') DROP VIEW View_DRS_Item_Sales" PreQuery2 CREATE View View_DRS_Item_Sales AS SELECT Item.ID AS ItemID, (SELECT SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE [Transaction].Time > '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND A.ID=Item.ID) AS YTD_QTY, (SELECT SUM(TransactionEntry.Price * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE [Transaction].Time > '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND A.ID=Item.ID) AS YTD_Sales, (SELECT SUM(TransactionEntry.Cost * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE [Transaction].Time > '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND A.ID=Item.ID) AS YTD_Cost, (SELECT SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND GETDATE()) AND A.ID=Item.ID) AS MTD_QTY, (SELECT SUM(TransactionEntry.Price * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND GETDATE()) AND A.ID=Item.ID) AS MTD_Sales, (SELECT SUM(TransactionEntry.Cost * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND GETDATE()) AND A.ID=Item.ID) AS MTD_Cost, (SELECT SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR)) AND DATEADD(MONTH,0,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID=Item.ID) AS LM_QTY, (SELECT SUM(TransactionEntry.Price * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR)) AND DATEADD(MONTH,0,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID=Item.ID) AS LM_Sales, (SELECT SUM(TransactionEntry.Cost * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR)) AND DATEADD(MONTH,0,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID=Item.ID) AS LM_Cost, (SELECT SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN DATEADD(YEAR,-1,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR)) AND DATEADD(YEAR,0,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID Item.ID) AS LY_QTY, (SELECT SUM(TransactionEntry.Price * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN DATEADD(YEAR,-1,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR)) AND DATEADD(YEAR,0,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID Item.ID) AS LY_Sales, (SELECT SUM(TransactionEntry.Cost * TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction] WITH (NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Item A WITH (NOLOCK) ON TransactionEntry.ItemID = A.ID WHERE ([Transaction].Time BETWEEN DATEADD(YEAR,-1,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR)) AND DATEADD(YEAR,0,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID Item.ID) AS LY_Cost

FROM Item

TablesQueried = "FROM Item WITH (NOLOCK) LEFT JOIN View_DRS_Item_Sales WITH (NOLOCK) ON Item.ID = View_DRS_Item_Sales.ItemID LEFT JOIN Department WITH (NOLOCK) ON Item.DepartmentID = Department.ID LEFT JOIN Category WITH (NOLOCK) ON Item.CategoryID = Category.ID LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID LEFT JOIN SupplierList ON SupplierList.SupplierID = Supplier.ID AND SupplierList.ItemID = Item.ID" SelCriteria = "" GroupBy = "" SortOrder = "" End ReportSummary

//--- Title Rows ---//

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow

Begin TitleRow Text = "Generated On " Font = "Arial" FontBold = True FontSize = 10 Color = "Black" End TitleRow

Begin TitleRow Text = "© 2004-2005 Digital Retail Solutions Inc. All rights reserved." Font = "Arial" FontBold = False FontSize = 6 Color = "Black" End TitleRow

//--- Filters ---//

Begin Filter FieldName = "Item.Inactive" FilterOp = reportfilteropEqual FilterLoLim = "0" FilterHilim = "0" End Filter

//--- Columns ---//

Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1600 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Category.Name" DrillDownFieldName = "Category.Name" DrillDownReportName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1300 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" Title = "Item Lookup Code" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1665 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.Description" DrillDownFieldName = "" DrillDownReportName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1830 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.SubDescription1" DrillDownFieldName = "" DrillDownReportName = "" Title = "!CaptionItemSubDescription1" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.SubDescription2" DrillDownFieldName = "" DrillDownReportName = "" Title = "!CaptionItemSubDescription2" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1200 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.SubDescription3" DrillDownFieldName = "" DrillDownReportName = "" Title = "!CaptionItemSubDescription3" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1200 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.WebItem" DrillDownFieldName = "" DrillDownReportName = "" Title = "Web Item" VBDataType = vbBoolean Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.PictureName" DrillDownFieldName = "Item.PictureName" DrillDownReportName = "" Title = "Picture" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1500 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Item.BinLocation" DrillDownFieldName = "" DrillDownReportName = "" Title = "Bin Location" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1140 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "On-Hand" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "#.##" End Column

Begin Column FieldName = "Item.QuantityCommitted" DrillDownFieldName = "" DrillDownReportName = "" Title = "Committed" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1050 GroupMethod = groupmethodSum ColFormat = "#.##" End Column

Begin Column FieldName = "Available" DrillDownFieldName = "" DrillDownReportName = "" Title = "Available" VBDataType = vbDouble Formula = "Item.Quantity - Item.QuantityCommitted" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "#.##" End Column

Begin Column FieldName = "Item.ReorderPoint" DrillDownFieldName = "" DrillDownReportName = "" Title = "Reorder Pt." VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1065 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Item.RestockLevel" DrillDownFieldName = "" DrillDownReportName = "" Title = "Restock Lvl." VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1215 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "QuantityToOrder" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty to Order" VBDataType = vbDouble Formula = "CASE WHEN Item.Quantity - Item.QuantityCommitted

Reply to
Philip Gass

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.