Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Adding Qty Sold on Ite...
Microsoft Dynamics RMS (Archived)

Adding Qty Sold on Item Quantity List Custom Report

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi,

I would like to add the Qty Sold column on this report, but I always get syntax errors. I tried to manipulate but I need and expert help. Can you help me correct , remove or add anything that is missing from this;

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

Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Item Quantity List"
PageOrientation = pageorientationLandscape
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = <BEGIN>

FROM Item WITH(NOLOCK) 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 TransactionEntry
LEFT JOIN [Transaction] WITH(NOLOCK)
ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID
LEFT JOIN Batch WITH(NOLOCK)
ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID = Batch.StoreID
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 ReasonCode AS ReasonCodeDiscount WITH(NOLOCK)
ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID
LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK)
ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID
LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK)
ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID
LEFT JOIN Store ON [Transaction].StoreID = Store.ID

<END>
SelCriteria = ""
GroupBy = ""
SortOrder = "Item.LastReceived ASC"
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 = "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 = 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 = 1290
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.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 = 1080
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column

Begin Column
FieldName = "Item.QuantityCommitted"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Committed"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1140
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 <= Item.ReorderPoint) THEN Item.RestockLevel - Item.Quantity + Item.QuantityCommitted ELSE 0 END"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1215
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column

Begin Column
FieldName = "Supplier.SupplierName"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1350
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Item.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 525
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 555
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.PriceA"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price A"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 720
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.PriceB"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price B"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 660
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.PriceC"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price C"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 720
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.PriceLowerBound"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Lo Bound"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 915
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.PriceUpperBound"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Hi Bound"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.SalePrice"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sale Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodSum
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.MSRP"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "MSRP"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Item.LastSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "TransactionEntry.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Qty Sold"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1300
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column

Begin Column
FieldName = "Item.Inactive"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Inactive"
VBDataType = vbBoolean
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1057
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column

Begin Column

FieldName = "Item.LastReceived"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Received"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 990
GroupMethod = groupmethodNone
ColFormat = ""

End Column

Thank you,

Koymackoy

*This post is locked for comments

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Qty Sold on Item Quantity List Custom Report

    every field not included in GroupMethod = groupmethodSum

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding Qty Sold on Item Quantity List Custom Report

    Hi Ramy,

    My GroupMethod for Qty Sold column is already set to groupmethodSum. Please see below:

    Begin Column
    FieldName = "TransactionEntry.Quantity"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "Qty Sold"
    VBDataType = vbDouble
    Formula = ""
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = False
    ColWidth = 1300
    GroupMethod = groupmethodSum
    ColFormat = "#.##"
    End Column

    Forgive me for this next (naive) question. What do I need to put after GroupBy = ?

    Thank you,

    Koymackoy

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Qty Sold on Item Quantity List Custom Report

    if you need to get sum of sold item you must use group by

    GroupMethod = groupmethodSUM

    in aggregated fields

    also using

    GroupBy = "" add grouped fields.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding Qty Sold on Item Quantity List Custom Report

    Hi Ramy!

    I think something is wrong with the output. Instead of displaying the total number of quantity sold, it only shows the highest number of quantity sold.

    Thanks,

    Koymackoy

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding Qty Sold on Item Quantity List Custom Report

    Thank you Ramy!

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Qty Sold on Item Quantity List Custom Report

    To solve syntax errors change query to be :

    TablesQueried = <BEGIN>

    FROM Item WITH(NOLOCK)

    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 TransactionEntry WITH(NOLOCK) on TransactionEntry.ItemID = item.ID

    LEFT JOIN [Transaction] WITH(NOLOCK)

    ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID

    LEFT JOIN Store ON [Transaction].StoreID = Store.ID

    LEFT JOIN Batch WITH(NOLOCK)

    ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID = Batch.StoreID

    LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK)

    ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID

    LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK)

    ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID

    LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK)

    ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID

    <END>

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans