Warning: iconv_mime_decode() [function.iconv-mime-decode]: Malformed string in /home/rockryno/public_html/lib/standard.lib.php on line 2258

Warning: iconv_mime_decode() [function.iconv-mime-decode]: Malformed string in /home/rockryno/public_html/lib/standard.lib.php on line 2258

Warning: iconv_mime_decode() [function.iconv-mime-decode]: Malformed string in /home/rockryno/public_html/lib/standard.lib.php on line 2258
quantity on hand needs to be included in item movement report
Home Page link  

quantity on hand needs to be included in item movement report

  
Groups | Search | About
User Password
Register Now! Lost Password?

Point-Of-Sale Software - - MS Point Of Sale software discussed here

 Post an article  get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content  add this group's latest topics to your Google content  YahooMyWeb Yahoo!  Google Google  Windows Live Favorites Windows Live  del.icio.us del.icio.us  digg digg  Add to Netscape Netscape
Subject Author Date
quantity on hand needs to be included in item movement report jlg 02-04-2008
Posted by jlg on February 4, 2008, 1:54 pm
Please log in for more thread options
 i use item movement as for buying info- qty on hand seems not included in the
allowed fields. this would be very helpfull

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=149897da-339c-4c22-b194-2af4f7a7329f&dg=microsoft.public.pos

Posted by Akber Alwani on February 5, 2008, 12:49 am
Please log in for more thread options
 hi Jlg,
you can do so by customizing the existing report of item movement. follow this
1. open the Items - Item Movement Report.qrp under C:Program
FilesMicrosoft Retail Management SystemStore OperationsReports folder or
whereever you installed the RMS
2. Update the preQuery2 view procedure by below:
REATE VIEW ViewItemMovement AS
SELECT Department.Name as DepartmentName,
         Category.Name as CategoryName,
         Supplier.SupplierName as SupplierName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Item.LastSold as LastSold,
Item.LastReceived as LastReceived,
Item.Cost as Cost,
Item.Quantity as OnHand,
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

UNION ALL

SELECT Department.Name as DepartmentName,
         Category.Name as CategoryName,
         Supplier.SupplierName as SupplierName,
         Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Item.LastSold AS LastSold,
Item.LastReceived as LastReceived,
Item.Cost AS Cost,
         Item.Quantity as OnHand,
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

//-- see above i have add Item.Quantity field and name it as on hand --//
3. Go down and update the Groupby clause as:

GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold,
ViewItemMovement.LastReceived,
ViewItemMovement.Cost,ViewItemMovement.onHand,ViewItemMovement.SupplierName,
ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName,
ViewItemMovement.ItemDescription"
4. Go down to whole code and add this:
Begin Column
FieldName = "ViewItemMovementonHand"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "On Hand"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.OnHand)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodSum
ColFormat = ""
End Column

5. save the report and run it , now it will show the qty on hand.
Once work please rate me.


"jlg" wrote:

> i use item movement as for buying info- qty on hand seems not included in the
> allowed fields. this would be very helpfull
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=149897da-339c-4c22-b194-2af4f7a7329f&dg=microsoft.public.pos

Posted by jlg on February 5, 2008, 12:09 pm
Please log in for more thread options
Akber,
thank you for your detailed and prompt response.
however my lack of skills makes me reluctant to try to implement these
changes. again thank you

"Akber Alwani" wrote:

> hi Jlg,
> you can do so by customizing the existing report of item movement. follow this
> 1. open the Items - Item Movement Report.qrp under C:Program
> FilesMicrosoft Retail Management SystemStore OperationsReports folder or
> whereever you installed the RMS
> 2. Update the preQuery2 view procedure by below:
> REATE VIEW ViewItemMovement AS
> SELECT Department.Name as DepartmentName,
>          Category.Name as CategoryName,
>          Supplier.SupplierName as SupplierName,
> Item.ItemLookupCode AS ItemLookupCode,
> Item.Description AS ItemDescription,
> Item.LastSold as LastSold,
> Item.LastReceived as LastReceived,
> Item.Cost as Cost,
> Item.Quantity as OnHand,
> 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
>
> UNION ALL
>
> SELECT Department.Name as DepartmentName,
>          Category.Name as CategoryName,
>          Supplier.SupplierName as SupplierName,
>          Item.ItemLookupCode AS ItemLookupCode,
> Item.Description AS ItemDescription,
> Item.LastSold AS LastSold,
> Item.LastReceived as LastReceived,
> Item.Cost AS Cost,
>          Item.Quantity as OnHand,
> 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
>
> //-- see above i have add Item.Quantity field and name it as on hand --//
> 3. Go down and update the Groupby clause as:
>
> GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold,
> ViewItemMovement.LastReceived,
> ViewItemMovement.Cost,ViewItemMovement.onHand,ViewItemMovement.SupplierName,
> ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName,
> ViewItemMovement.ItemDescription"
> 4. Go down to whole code and add this:
> Begin Column
> FieldName = "ViewItemMovementonHand"
> DrillDownFieldName = ""
> DrillDownReportName = ""
> Title = "On Hand"
> VBDataType = vbDouble
> Formula = "SUM(ViewItemMovement.OnHand)"
> ColHidden = False
> ColNotDisplayable = False
> FilterDisabled = False
> ColWidth = 1800
> GroupMethod = groupmethodSum
> ColFormat = ""
> End Column
>
> 5. save the report and run it , now it will show the qty on hand.
> Once work please rate me.
>
>
> "jlg" wrote:
>
> > i use item movement as for buying info- qty on hand seems not included in
the
> > allowed fields. this would be very helpfull
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> >
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=149897da-339c-4c22-b194-2af4f7a7329f&dg=microsoft.public.pos

Posted by Danny on February 5, 2008, 12:58 pm
Please log in for more thread options
Akber

I would like to know more about the following fields in the QRP file:

ProcedureCall = << would some internal RMS procedure - what are they????>>
PreQuery1 = ??
PreQuery2 = ??
TablesQueried =

What do they do... I want to create my own QRP reports -

Danny

> hi Jlg,
> you can do so by customizing the existing report of item movement. follow
> this
> 1. open the Items - Item Movement Report.qrp under C:Program
> FilesMicrosoft Retail Management SystemStore OperationsReports folder
> or
> whereever you installed the RMS
> 2. Update the preQuery2 view procedure by below:
> REATE VIEW ViewItemMovement AS
> SELECT Department.Name as DepartmentName,
> Category.Name as CategoryName,
> Supplier.SupplierName as SupplierName,
> Item.ItemLookupCode AS ItemLookupCode,
> Item.Description AS ItemDescription,
> Item.LastSold as LastSold,
> Item.LastReceived as LastReceived,
> Item.Cost as Cost,
> Item.Quantity as OnHand,
> 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
>
> UNION ALL
>
> SELECT Department.Name as DepartmentName,
> Category.Name as CategoryName,
> Supplier.SupplierName as SupplierName,
> Item.ItemLookupCode AS ItemLookupCode,
> Item.Description AS ItemDescription,
> Item.LastSold AS LastSold,
> Item.LastReceived as LastReceived,
> Item.Cost AS Cost,
> Item.Quantity as OnHand,
> 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
>
> //-- see above i have add Item.Quantity field and name it as on hand --//
> 3. Go down and update the Groupby clause as:
>
> GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold,
> ViewItemMovement.LastReceived,
> ViewItemMovement.Cost,ViewItemMovement.onHand,ViewItemMovement.SupplierName,
> ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName,
> ViewItemMovement.ItemDescription"
> 4. Go down to whole code and add this:
> Begin Column
> FieldName = "ViewItemMovementonHand"
> DrillDownFieldName = ""
> DrillDownReportName = ""
> Title = "On Hand"
> VBDataType = vbDouble
> Formula = "SUM(ViewItemMovement.OnHand)"
> ColHidden = False
> ColNotDisplayable = False
> FilterDisabled = False
> ColWidth = 1800
> GroupMethod = groupmethodSum
> ColFormat = ""
> End Column
>
> 5. save the report and run it , now it will show the qty on hand.
> Once work please rate me.
>
>
> "jlg" wrote:
>
>> i use item movement as for buying info- qty on hand seems not included in
>> the
>> allowed fields. this would be very helpfull
>>
>> ----------------
>> This post is a suggestion for Microsoft, and Microsoft responds to the
>> suggestions with the most votes. To vote for this suggestion, click the
>> "I
>> Agree" button in the message pane. If you do not see the button, follow
>> this
>> link to open the suggestion in the Microsoft Web-based Newsreader and
>> then
>> click "I Agree" in the message pane.
>>
>>
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=149897da-339c-4c22-b194-2af4f7a7329f&dg=microsoft.public.pos



Posted by Akber Alwani on February 5, 2008, 11:49 pm
Please log in for more thread options
hi Danny, I have noted ur email address, and sending you the customization
guide, this will help you in detail for every aspect of RMS and you will
really like it.

Remember rate me once you get.



"Danny" wrote:

> Akber
>
> I would like to know more about the following fields in the QRP file:
>
> ProcedureCall = << would some internal RMS procedure - what are they????>>
> PreQuery1 = ??
> PreQuery2 = ??
> TablesQueried =
>
> What do they do... I want to create my own QRP reports -
>
> Danny
>
> > hi Jlg,
> > you can do so by customizing the existing report of item movement. follow
> > this
> > 1. open the Items - Item Movement Report.qrp under C:Program
> > FilesMicrosoft Retail Management SystemStore OperationsReports folder
> > or
> > whereever you installed the RMS
> > 2. Update the preQuery2 view procedure by below:
> > REATE VIEW ViewItemMovement AS
> > SELECT Department.Name as DepartmentName,
> > Category.Name as CategoryName,
> > Supplier.SupplierName as SupplierName,
> > Item.ItemLookupCode AS ItemLookupCode,
> > Item.Description AS ItemDescription,
> > Item.LastSold as LastSold,
> > Item.LastReceived as LastReceived,
> > Item.Cost as Cost,
> > Item.Quantity as OnHand,
> > 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
> >
> > UNION ALL
> >
> > SELECT Department.Name as DepartmentName,
> > Category.Name as CategoryName,
> > Supplier.SupplierName as SupplierName,
> > Item.ItemLookupCode AS ItemLookupCode,
> > Item.Description AS ItemDescription,
> > Item.LastSold AS LastSold,
> > Item.LastReceived as LastReceived,
> > Item.Cost AS Cost,
> > Item.Quantity as OnHand,
> > 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
> >
> > //-- see above i have add Item.Quantity field and name it as on hand --//
> > 3. Go down and update the Groupby clause as:
> >
> > GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold,
> > ViewItemMovement.LastReceived,
> > ViewItemMovement.Cost,ViewItemMovement.onHand,ViewItemMovement.SupplierName,
> > ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName,
> > ViewItemMovement.ItemDescription"
> > 4. Go down to whole code and add this:
> > Begin Column
> > FieldName = "ViewItemMovementonHand"
> > DrillDownFieldName = ""
> > DrillDownReportName = ""
> > Title = "On Hand"
> > VBDataType = vbDouble
> > Formula = "SUM(ViewItemMovement.OnHand)"
> > ColHidden = False
> > ColNotDisplayable = False
> > FilterDisabled = False
> > ColWidth = 1800
> > GroupMethod = groupmethodSum
> > ColFormat = ""
> > End Column
> >
> > 5. save the report and run it , now it will show the qty on hand.
> > Once work please rate me.
> >
> >
> > "jlg" wrote:
> >
> >> i use item movement as for buying info- qty on hand seems not included in
> >> the
> >> allowed fields. this would be very helpfull
> >>
> >> ----------------
> >> This post is a suggestion for Microsoft, and Microsoft responds to the
> >> suggestions with the most votes. To vote for this suggestion, click the
> >> "I
> >> Agree" button in the message pane. If you do not see the button, follow
> >> this
> >> link to open the suggestion in the Microsoft Web-based Newsreader and
> >> then
> >> click "I Agree" in the message pane.
> >>
> >>
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=149897da-339c-4c22-b194-2af4f7a7329f&dg=microsoft.public.pos
>
>
>

Similar ThreadsPosted
Discrepancies between on-hand quantity and item movement report September 12, 2007, 12:24 pm
Item movement report should include quantity on-hand. September 17, 2007, 1:30 pm
Item Movement Report with On Hand and Reorder kudos to Akber - "Items - Item Movement Report.qrp.zip" yEnc (1/1) April 17, 2008, 2:02 am
Report on the item movement report should show qty on hand as we September 1, 2005, 10:37 am
Re: Item Movement with On Hand & Reorder November 17, 2007, 11:56 pm
quantity on hand & quantity sold in one report? January 30, 2007, 9:46 am
Display quantity updates through Inventory Wizard on Item Movement August 23, 2005, 10:50 am
Adding onhand quantity to inventory movement history report July 28, 2006, 3:05 am
Movement does not match On Hand October 26, 2007, 2:10 pm
Item Movement Report @ HQ December 20, 2005, 6:07 pm

Contact Us | Privacy Policy
This site is not affiliated with Intuit - makers of Quickbooks and Quicken software
This site is not affiliated with Sage Software - makers of Peachtree accounting software
XML SitemapXML Sitemap