1. Home
  2. Point-Of-Sale Software
  3. Customising HELP!!

Customising HELP!!

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!

Is there such thing as one report which has every single colum/possibility turned on so you dont need so many different reports all the time!!!?

//--- 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
Loading thread data ...

HI Philip, from which report you copy this let me know the name i know that I done some of the require changes in such report but forgot the name.

"Philip Gass" wrote:

Reply to
Akber Alwani

Report is from SO and is called "Items - Item Quantity List.qrp" I've attached it here for you to have a look. Thanks for all your help

begin 666 Items - Item Quantity List.qrp M+R\M+2T@4F5P;W)T(%-U;6UA

Reply to
Philip Gass

Here is your report. You have forgetted the group by clause, I added, please rate me. //--- 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, (SELECT Sum(QuantityOrdered) from PurchaseOrder INNER JOIN PurchaseOrderEntry ON PurchaseOrder.ID=PurchaseOrderEntry.PurchaseOrderID WHERE PurchaseOrderEntry.ItemID=Item.ID AND PurchaseOrder.Status=0) AS LM_Order FROM Item Group BY Item.ID

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
Akber Alwani

Akber- thanks very much for your help, but im getting an error when i try to run this report....

It says error at line 13????

Reply to
Philip Gass

HI Philip, open the file in notepad and go the the line 13 then make sure that there is no line break you should not have word wrap enable in notepad this won't give you clear picture that text is cut. Make sure the whole text should be in one line. When I copied your text for verification I done the similar job and spend so much time.

Do this first and it will solve your issue else send me your email address I will forward you this report which works at my side. Regards Akber

"Philip Gass" wrote:

Reply to
Akber Alwani

Thanks for all your help as usual Akber!

email address is snipped-for-privacy@creativegardens.net

Thanks!

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.