Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Quantity Sold/Quantity...
Microsoft Dynamics RMS (Archived)

Quantity Sold/Quantity on Hand Report with "Extended Description" field

(0) ShareShare
ReportReport
Posted on by extel 370

Hi,

How do I pull a single report with the following criteria: 1) Supplier 2) Total quantity of items sold 3) Total quantity of items on hand 4) Specific Date range 5) "Ext'd Description" column.

 The detailed sales report provides the information but it displays a line item for each item sold, so if you sold 6 of item ABC there would be 6 individual line items for each time it sold, I need just a total for the subject item(s) sold.

A custom report was suggested/provided by a member of the forum but it does not include the "Ext'd Description" column.

Any help appreciated.

*This post is locked for comments

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Hi Upendra.

    You are refering to the report posted by extel? As I saed, I need to dive into my backups and see...

    BR, A.

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Danny, this was long ago, I have to go through my backups and see if I can find it...

    Will try, OK?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Hi Antonijo,

    The On Hand qty is showing the HQ qty not the respective store on hand qty.

    Thanks & Regards,

    Upendra Nath M.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Hi Antonijo, did you ever make this report work? if yes, could you share how you made this work???

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Send me you e-mail on antonijo_todorovik@hotmail.com I think i got it. There may be some minor errors, but i think it should be OK...

    Kind regards, A.

  • extel Profile Picture
    extel 370 on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Thank you for the reply.  In regards to the supplier, we always use one supplier.  The following was provided by a member of the forum.  It works; however, it does not provide the extended description.  If this could be adjusted to include the extended description, I believe this would solve our problem:

    Copy this info into Notepad and save as .QRP in the Reports folder.

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

    Begin ReportSummary

     ReportType = reporttypeSales

     ReportTitle = "Summary Sales Report"

     PageOrientation = pageorientationPortrait

     OutLineMode = True

     Groups = 1

     GroupDescription = ""

     DisplayLogo = True

     LogoFileName = "MyLogo.bmp"

     ProcedureCall = ""

     PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewOnOrder') DROP VIEW ViewOnOrder"

     PreQuery2 = <BEGIN>

       CREATE VIEW ViewOnOrder AS

           SELECT itemid, sum(quantityordered - quantityreceivedtodate) AS OnOrder

           FROM PurchaseOrderEntry

           LEFT JOIN PurchaseOrder on PurchaseOrderEntry.purchaseorderid = PurchaseOrder.id

           WHERE potype < 2

           GROUP BY itemid

       <END>

     TablesQueried = <BEGIN>

        FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber

           LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.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   ViewOnOrder WITH(NOLOCK) ON Item.ID = ViewOnOrder.ItemID

    <END>

     SelCriteria = ""

     GroupBy = "Department.Name, Category.Name, Item.ItemLookupCode, Item.Description, Item.Price, Item.Quantity, Supplier.SupplierName, Item.Cost, ViewOnOrder.OnOrder, Item.SubDescription1, Item.SubDescription2, Item.SubDescription3, Item.BinLocation, Item.PriceA, Item.PriceB, Item.PriceC, Item.MSRP, Item.LastSold, Item.SalePrice, Item.SaleStartDate, Item.SaleEndDate"

     SortOrder = ""

    End ReportSummary

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

    Begin TitleRow

     Text = "<Store Name>"

     Font = "Arial"

     FontBold = True

     FontSize = 16

     Color = "Blue"

    End TitleRow

    Begin TitleRow

     Text = "<Report Title>"

     Font = "Arial"

     FontBold = True

     FontSize = 12

     Color = "Black"

    End TitleRow

    Begin TitleRow

     Text = "Generated On <Report Date>"

     Font = "Arial"

     FontBold = True

     FontSize = 10

     Color = "Black"

    End TitleRow

    //--- Filters ---//

    Begin Filter

     FieldName = "[Transaction].Time"

     FilterOp = reportfilteropBetween

     FilterLoLim = "<Today>"

     FilterHilim = "<Today>"

    End Filter

    //--- Columns ---//

    Begin Column

     FieldName = "Department.Name"

     DrillDownFieldName = "Department.Name"

     DrillDownReportName = ""

     Title = "Department"

     VBDataType = vbString

     Formula = ""

     ColHidden = False

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 2205

     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 = 1515

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Supplier.SupplierName"

     DrillDownFieldName = "Supplier.SupplierName"

     DrillDownReportName = ""

     Title = "Supplier"

     VBDataType = vbString

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1725

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Item.ItemLookupCode"

     DrillDownFieldName = "Item.ItemLookupCode"

     DrillDownReportName = ""

     Title = "Item"

     VBDataType = vbString

     Formula = ""

     ColHidden = False

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1380

     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 = 2115

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Item.SubDescription1"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "!CaptionItemSubDescription1"

     VBDataType = vbString

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1600

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Item.SubDescription2"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "!CaptionItemSubDescription2"

     VBDataType = vbString

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1600

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Item.SubDescription3"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "!CaptionItemSubDescription3"

     VBDataType = vbString

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1600

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Item.Quantity"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "On Hand"

     VBDataType = vbDouble

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 810

     GroupMethod = groupmethodSum

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "ViewOnOrder.OnOrder"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "On Order"

     VBDataType = vbDouble

     Formula = "ISNULL(ViewOnOrder.OnOrder, 0)"

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 950

     GroupMethod = groupmethodSum

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.Price"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Price"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 720

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.PriceA"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Price A"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 750

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.PriceB"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Price B"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 750

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.PriceC"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Price C"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 750

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.Cost"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Cost"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 750

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "TransactionEntry.Quantity"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Qty Sold"

     VBDataType = vbDouble

     Formula = "SUM(TransactionEntry.Quantity)"

     ColHidden = False

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 810

     GroupMethod = groupmethodSum

     ColFormat = "##########"

    End Column

    Begin Column

     FieldName = "Total"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Total Sales"

     VBDataType = vbCurrency

     Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"

     ColHidden = False

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1275

     GroupMethod = groupmethodSum

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "TransactionEntry.Cost"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Total Cost"

     VBDataType = vbCurrency

     Formula = "SUM(TransactionEntry.Cost * TransactionEntry.Quantity)"

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1000

     GroupMethod = groupmethodSum

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Profit"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Profit"

     VBDataType = vbCurrency

     Formula = "SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)"

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 950

     GroupMethod = groupmethodSum

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "ProfitMargin"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Profit Margin"

     VBDataType = vbDouble

     Formula = "CASE WHEN SUM(TransactionEntry.Price * TransactionEntry.Quantity) <> 0 THEN SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)/SUM(TransactionEntry.Price * TransactionEntry.Quantity) ELSE 0 END"

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1300

     GroupMethod = groupmethodNone

     ColFormat = "0.00%"

    End Column

    Begin Column

     FieldName = "TotalDiscount"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     StoreIDFieldName = ""

     Title = "Discount"

     VBDataType = vbCurrency

     Formula = "SUM((TransactionEntry.FullPrice - TransactionEntry.Price) * TransactionEntry.Quantity)"

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1220

     GroupMethod = groupmethodsum

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.BinLocation"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     StoreIDFieldName = ""

     Title = "Bin Location"

     VBDataType = vbString

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1440

     GroupMethod = groupmethodNone

     ColFormat = ""

     ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

     FieldName = "Item.MSRP"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "MSRP"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 750

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.SalePrice"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Sale Price"

     VBDataType = vbCurrency

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1000

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.SaleStartDate"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Sale Starts"

     VBDataType = vbDate

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1020

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.SaleEndDate"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Sale Ends"

     VBDataType = vbDate

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 930

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "Item.LastSold"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Last Sold"

     VBDataType = vbDate

     Formula = ""

     ColHidden = True

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1000

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

    Begin Column

     FieldName = "[Transaction].Time"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Date Sold"

     VBDataType = vbDate

     Formula = ""

     ColHidden = True

     ColNotDisplayable = True

     FilterDisabled = False

     ColWidth = 900

     GroupMethod = groupmethodNone

     ColFormat = ""

    End Column

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: Quantity Sold/Quantity on Hand Report with "Extended Description" field

    Hi there. The problem I see in this report is that you want to get the total quantity sold, so you need to group the transactionEntry lines, but if you add ExtendedDescripiton, which is a nText field you can't do that... On other side, items can be delivered by more then one Supplier and in that case you can't know if the item sold was delivered from one or other supplier..., so you have to assure that one item is always delivered from one and always the same supplier....

    And if you do this report in external Excel file, by using VBA or similar???

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,431 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,503 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans