|
Posted by TomT on April 19, 2007, 12:34 pm
Please log in for more thread options Akber,
You need to exclude POType = 3 (transfer outs)
Where Status=0 and POType<>3
> HI Gass,
> its Akber again,
>
> Replace this code in Items-Item Price List.grp file
> Begin ReportSummary
> ReportType = reporttypeItems
> ReportTitle = "Item Price List"
> PageOrientation = pageorientationLandscape
> OutLineMode = True
> Groups = 1
> GroupDescription = "Average"
> DisplayLogo = True
> LogoFileName = "MyLogo.bmp"
> ProcedureCall = ""
> PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'Vw_POQty') DROP VIEW vw_POQty"
> PreQuery2 = <BEGIN>
> CREATE View vw_POQty AS
> SELECT ItemID,Sum(QuantityOrdered) OrderQty
> From PurchaseOrder
> INNER join PurchaseOrderEntry ON
> PurchaseOrder.ID=PurchaseOrderEntry.PurchaseOrderID
> Where Status=0
> Group by PurchaseOrderEntry.ItemID
> <END>
>
> TablesQueried = "FROM Item WITH(NOLOCK) LEFT JOIN vw_POQty WITH(NOLOCK)
> ON vw_POQty.ItemID=Item.ID 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 WITH (NOLOCK) ON
> Supplierlist.ItemID=Item.ID AND Supplier.ID=SupplierList.SupplierID"
> SelCriteria = ""
> GroupBy = ""
> SortOrder = ""
> End ReportSummary
>
> Now add anywhere in the same report the following code: remember you need
> to
> add in the //--- Columns ---// block
>
>
> Begin Column
> FieldName = "SupplierList.ReorderNumber"
> DrillDownFieldName = ""
> DrillDownReportName = ""
> Title = "Reorder Number"
> VBDataType = vbString
> Formula = ""
> ColHidden = false
> ColNotDisplayable = False
> FilterDisabled = False
> ColWidth = 2115
> GroupMethod = groupmethodNone
> ColFormat = ""
> ColAlignment = flexAlignLeftCenter
> End Column
>
>
> Begin Column
> FieldName = "vw_POQty.OrderQty"
> DrillDownFieldName = ""
> DrillDownReportName = ""
> Title = "On Ordered"
> VBDataType = vbDouble
> Formula = ""
> ColHidden = false
> ColNotDisplayable = False
> FilterDisabled = False
> ColWidth = 1050
> GroupMethod = groupmethodNone
> ColFormat = ""
> End Column
>
> Now rate me.
>
>
> "Philip Gass" wrote:
>
>> I've asked about this before, so sorry to repeat! but it would be mega
>> helpfull if somebody could help me with customising a report as follows:
>>
>> I need to have the following availalbe for filter and columns on the
>> report.
>> I have attached the price list store report that i use, it is missing the
>> following though and i have tried adding them, but i dont know how to
>> join
>> tables etc...
>>
>> Qty on Order (from current section of inventory tab)
>> Alias
>> Reorder No (From supplier tab)
>>
>> If somebody could ammend the attached report and post it back up it would
>> be
>> a huge help!
>>
>> Philip
>>
>>
>>
>>
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap