Report Generating Question

Search the group. There has been a lot of discussion about this in the last month or two. Craig

HI chris, here is item movememnt report modified make your existing backup of item movement report and past the below query:

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

Begin ReportSummary ReportType = reporttypeItems ReportTitle = "Item Movement Report" PageOrientation = pageorientationLandscape ShowDateTimePicker = False OutLineMode = True Groups = 0 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewItemMovement') DROP VIEW ViewItemMovement" PreQuery2 CREATE VIEW ViewItemMovement AS SELECT Department.Name as DepartmentName, Category.Name as CategoryName, Supplier.SupplierName as SupplierName, Item.ItemLookupCode AS ItemLookupCode, SupplierList.ReorderNumber as ReorderNumber, Item.Description AS ItemDescription, Item.LastSold as LastSold, Item.LastReceived as LastReceived, Item.Cost as Cost, InventoryTransferLog.Type AS Type, 0 as QuantitySold, ISNULL (InventoryTransferLog.Quantity, 0) AS Quantity, 0 as PriceSold, 0 as CostSold, InventoryTransferLog.DateTransferred AS DateTransferred, 1 AS Moved, CASE InventoryTransferlog.Type WHEN 2 THEN InventoryTransferLog.ReferenceID ELSE NULL END AS TransactionNumber FROM InventoryTransferLog LEFT JOIN Item ON InventoryTransferLog.ItemID = Item.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID LEFT JOIN Category ON Item.CategoryID = Category.ID LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID LEFT JOIN SupplierList On Item.SupplierID=SupplierList.SupplierID and Item.ID=SupplierList.ItemID UNION ALL

SELECT Department.Name as DepartmentName, Category.Name as CategoryName, Supplier.SupplierName as SupplierName, Item.ItemLookupCode AS ItemLookupCode, SupplierList.ReorderNumber as ReorderNumber, Item.Description AS ItemDescription, Item.LastSold AS LastSold, Item.LastReceived as LastReceived, Item.Cost AS Cost, 99 AS Type, ISNULL (TransactionEntry.Quantity, 0) AS QuantitySold, 0 as QuantityTransferred, ISNULL (TransactionEntry.Price, 0) as PriceSold, ISNULL (TransactionEntry.Cost, 0) as CostSold, [Transaction].Time AS DateTransferred, CASE WHEN [Transaction].Time IS NULL THEN 0 ELSE 1 END AS Moved, [Transaction].TransactionNumber AS TransactionNumber FROM Item LEFT JOIN TransactionEntry ON TransactionEntry.ItemID = Item.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID LEFT JOIN Category ON Item.CategoryID = Category.ID LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID LEFT JOIN SupplierList On Item.SupplierID=SupplierList.SupplierID and Item.ID=SupplierList.ItemID

TablesQueried = "FROM ViewItemMovement" SelCriteria = "" GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.ReorderNumber, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription" 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

//--- Filters ---//

Begin Filter FieldName = "ViewItemMovement.DateTransferred" FilterOp = reportfilteropGreaterEqual FilterLoLim = "" FilterHilim = "" End Filter

The filter will help you to bring the data for specific dates. you can select Date as your filter and give date from and to. "chris" wrote:

Having a problem with the report. When I replace the existing report it comes up with errors in several locations...Line 15...I fix that one by adjusting the spacing. Then it come up with an error on line 19...i fix that one again, appears to be a spacing thing. Then it says that it is not an active report....?????

"Akber Alwani" wrote:

How do I save this report and where should I save it too?

Thanks

new to this sorry

"Akber Alwani" wrote:

hi Chris,

copy and paste it in the notepad and save as extension xxx.grp . remeber the below is not complete report if u need complete send me ur email.

"chris" wrote:

snipped-for-privacy@andriaboyd.com

thanks aga> hi Chris,