Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Mathematical Functions...
Microsoft Dynamics RMS (Archived)

Mathematical Functions within Reports

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

I have a report created that helps me see what I need to order. The only problem is that some of my suppliers have minimum order values and in order to see the total value of what I need to order, I have to export the report to excel to do the math. (I could create a PO, but the boss does not want that because it causes skipped PO numbers)

I would like an extended total that will multiply the quantity to order by the cost.

I found another post where the following code was given:

 

Begin Column

 FieldName = "ExtendedCost"

 DrillDownFieldName = ""

 DrillDownReportName = ""

 Title = "Extended Cost"

 VBDataType = vbCurrency

 Formula = "Item.Cost * Item.Quantity"

 ColHidden = False

 ColNotDisplayable = False

 FilterDisabled = False

 ColWidth = 1335

 GroupMethod = groupmethodSum

 ColFormat = ""

End Column

 

I tried replacing "Item.Quantity" (in the formula) with the Field Name  "QuantityToOrderMPQ" but I got an error.

 

I can get the report to work using other fields (on hand and MPQ), but not the quantity to order.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Mathematical Functions within Reports

    I know item.quantity is not correct, I was just seeing if the math would work.

    I need it to multiply my most recent cost by the quantity to order MPQ

    Here's the code:

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

    Begin ReportSummary

      ReportType = reporttypeItems

      ReportTitle = "To Be Ordered List"

      PageOrientation = pageorientationLandscape

      WordWrap = False

      ShowDateTimePicker = False

      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 = "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 SupplierList ON SupplierList.SupplierID = Supplier.ID AND SupplierList.ItemID = Item.ID LEFT JOIN   ViewOnOrder WITH(NOLOCK) ON Item.ID = ViewOnOrder.ItemID "

      SelCriteria = ""

      GroupBy = ""

      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 = "Item.Inactive"

      FilterOp = reportfilteropEqual

      FilterLoLim = "0"

      FilterHilim = "0"

      FilterNegated = False

      FilterConnector = reportfilterbooleanconAND

    End Filter

    Begin Filter

      FieldName = "QuantityToOrder"

      FilterOp = reportfilteropGreater

      FilterLoLim = "0"

      FilterHilim = "0"

      FilterNegated = False

      FilterConnector = reportfilterbooleanconAND

    End Filter

    //--- Columns ---//

    Begin Column

      FieldName = "Supplier.SupplierName"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Supplier"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 3000

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.ItemLookupCode"

      DrillDownFieldName = "Item.ItemLookupCode"

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Part Number"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 2200

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.Description"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Description"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 2600

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.Quantity"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "On-Hand"

      VBDataType = vbDouble

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodNone

      ColFormat = "#"

    End Column

    Begin Column

      FieldName = "Item.QuantityCommitted"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Commit."

      VBDataType = vbDouble

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodNone

      ColFormat = "#"

    End Column

    Begin Column

      FieldName = "Item.RestockLevel"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Restock"

      VBDataType = vbDouble

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodNone

      ColFormat = "#"

    End Column

    Begin Column

      FieldName = "ViewOnOrder.OnOrder"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "On Order"

      VBDataType = vbDouble

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

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodNone

      ColFormat = "#"

    End Column

    Begin Column

      FieldName = "QuantityToOrder"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

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

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodNone

      ColFormat = "#"

    End Column

    Begin Column

      FieldName = "QuantityToOrderMPQ"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Order Qty"

      VBDataType = vbDouble

      Formula = "CASE WHEN Item.Quantity - Item.QuantityCommitted <= Item.ReorderPoint AND SupplierList.MasterPackQuantity > 0 THEN CEILING ((Item.RestockLevel - Item.Quantity + Item.QuantityCommitted) / SupplierList.MasterPackQuantity) * SupplierList.MasterPackQuantity WHEN Item.Quantity - Item.QuantityCommitted <= Item.ReorderPoint THEN Item.RestockLevel - Item.Quantity + Item.QuantityCommitted ELSE 0 END"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodNone

      ColFormat = "#"

    End Column

    Begin Column

      FieldName = "Item.Cost"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Cost"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1000

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

     FieldName = "ExtendedCost"

     DrillDownFieldName = ""

     DrillDownReportName = ""

     Title = "Extended Cost"

     VBDataType = vbCurrency

     Formula = "Item.Cost * ViewOnOrder.OnOrder"

     ColHidden = False

     ColNotDisplayable = False

     FilterDisabled = False

     ColWidth = 1335

     GroupMethod = groupmethodSum

     ColFormat = ""

    End Column

    Begin Column

      FieldName = "SupplierList.MasterPackQuantity"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "MPQ"

      VBDataType = vbLong

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 705

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.ReorderPoint"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Reorder Pt."

      VBDataType = vbDouble

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1065

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Department.Name"

      DrillDownFieldName = "Department.Name"

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Department"

      VBDataType = vbString

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 2205

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Category.Name"

      DrillDownFieldName = "Category.Name"

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Category"

      VBDataType = vbString

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1290

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.WebItem"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Web Item"

      VBDataType = vbBoolean

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1005

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.BinLocation"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      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.MSRP"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "MSRP"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 945

      GroupMethod = groupmethodAverage

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "SupplierList.ReorderNumber"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Reorder No."

      VBDataType = vbString

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1305

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "Item.Price"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      StoreIDFieldName = ""

      Title = "Price"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 555

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: Mathematical Functions within Reports

    Hi Meralee.

    Are you sure You are using the correct JOINS in the report query? What is the error You are getting?? If You send me the report, I can have a look at it... Certais math can be done in the formula field, can be mathematical or logical, but always tSQL...

    Regards, A.

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