web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / RMS Profit Margin into...
Microsoft Dynamics RMS (Archived)

RMS Profit Margin into Inventory Report

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I am trying to create a report to view all of my stock. I wanted to see the following:

Department
Category
Supplier
Brand (Item.SubDescription1)
Description
Cost
Price
Profit
Profit Margin
If the item has tax of not.

I have been able to get the report to show all but the last two items (Profit Margin and if the item is taxable). Below is the code that I am trying to use to show the profit margin field but I have no idea how to grab the info to see if the item is taxable or not.

Begin Column
   FieldName = "ProfitMargin"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Profit Margin"
   VBDataType = vbDouble
   Formula = "[(Item.Price - Item.Cost) / Item.Price]"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 900
   GroupMethod = groupmethodAverage
   ColFormat = "0.00%"
End Column

Can anyone help?

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    I just realised something that I should have included.

    I have alot of products at a zero cost and a few products at a negative profit margin. This report will help me find these products and fix them but ill need the formula to take this in to consideration.

  • Verified answer
    mcons Profile Picture
    mcons 390 on at

    I am assuming you are modifying the standard Item Movement report.   If not, you can still modify this to work for your report.     This will had the TaxID field to your report.   It is nothing fancy, but if that TaxID field shows 0, the item will not have a tax associated to it.   If it has a # there, like 1, 2, etc then that item does have tax associated to it.    There is an internal field called "Taxable", but from what I have seen it is always set to "Yes".

    If you were to modify the Item Movement report it would need something like this:

    -At the top, under the first "SELECT..." query, add a new line Item.TaxID As TaxID,
    -Do the same to the second "SELECT" query.

    -Next you will see "GroupBy..."  Add a comma after the last one then add viewItemMovement.TaxID to the end (just before the ending ")

    -Finally at the bottom of your report add this:

    Begin Column
       FieldName = "ViewItemMovement.TaxID"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Tax ID"
       VBDataType = vbDouble
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 900
       GroupMethod = groupmethodNone
       ColFormat = ""
     End Column

    You should now see a TaxID field at the far right of your report.   It will have 0 or 1, or 2, etc.   0 = not taxed.

    This is a quick and dirty way to get the job done.  You can add the Tax description if you want to go through the table join methods.  

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Begin Column

      FieldName = "ProfitMargin"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Profit Margin"

      VBDataType = vbDouble

      Formula = "CASE WHEN Item.Price <> 0 THEN ((Item.Price - Item.Cost) / Item.Price) ELSE 0 END"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodAverage

      ColFormat = "0.00%"

    End Column

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi Josh,

    Thank you - this was exactly what I was looking for and did the job.

  • Community Member Profile Picture
    Community Member on at

    GFG - which report did you add this column to exactly? Or would you mind posting the full report?

    Thanks.

  • Community Member Profile Picture
    Community Member on at

    NM: Figured it out. Added it as the first column to the table "Item Movement Report":

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

Begin ReportSummary
   ReportType = reporttypeItems
   ReportTitle = "Item Movement Report With Profit Margins"
   PageOrientation = pageorientationLandscape
   ShowDateTimePicker = False
   OutLineMode = True
   Groups = 0
   GroupDescription = ""
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewItemMovement') DROP VIEW ViewItemMovement"
   PreQuery2 = <BEGIN>
   
      CREATE 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.Price as Price,
             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.Price AS Price,
             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

<END>
   TablesQueried = "FROM ViewItemMovement"
   SelCriteria = ""
   GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.Price,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription"
   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 = "ViewItemMovement.DateTransferred"
   FilterOp = reportfilteropGreaterEqual
   FilterLoLim = "<MonthStart>"
   FilterHilim = "<MonthStart>"
End Filter

//--- Columns ---//

Begin Column
  FieldName = "ProfitMargin"
  DrillDownFieldName = ""
  DrillDownReportName = ""
  Title = "Profit Margin"
  VBDataType = vbDouble
  Formula = "CASE WHEN Price <> 0 THEN ((Price - Cost) / Price) ELSE 0 END"
  ColHidden = False
  ColNotDisplayable = False
  FilterDisabled = False
  ColWidth = 900
  GroupMethod = groupmethodAverage
  ColFormat = "0.00%"
End Column


Begin Column
   FieldName = "ViewItemMovement.ItemLookupCode"
   DrillDownFieldName = "Item.ItemLookupCode"
   DrillDownReportName = ""
   Title = "Item Lookup Code"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2160
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "ViewItemMovement.DepartmentName"
   DrillDownFieldName = "Department.Name"
   DrillDownReportName = ""
   Title = "Department"
   VBDataType = vbString
   Formula = "ViewItemMovement.DepartmentName"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2160
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "ViewItemMovement.CategoryName"
   DrillDownFieldName = "Category.Name"
   DrillDownReportName = ""
   Title = "Category"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2160
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

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

Begin Column
   FieldName = "ViewItemMovement.ItemDescription"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Description"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2160
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "QtySold"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Quantity Sold"
   VBDataType = vbDouble
   Formula = "SUM(ViewItemMovement.QuantitySold)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1200
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "Sales"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Sales"
   VBDataType = vbCurrency
   Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.PriceSold)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "ViewItemMovement.LastSold"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Last Sold"
   VBDataType = vbDate
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodMax
   ColFormat = ""
End Column

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

Begin Column
   FieldName = "CostSold"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Cost Sold"
   VBDataType = vbCurrency
   Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.CostSold)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "ViewItemMovementQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Non Sale Movement"
   VBDataType = vbDouble
   Formula = "SUM(ViewItemMovement.Quantity)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1800
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "ViewItemMovement.LastReceived"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Last Received"
   VBDataType = vbDate
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1400
   GroupMethod = groupmethodMax
   ColFormat = ""
End Column

Begin Column
   FieldName = "PurchaseOrder"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "PO Movement"
   VBDataType = vbDouble
   Formula = "SUM(CASE WHEN Type = 1 THEN ISNULL(Quantity, 0) ELSE 0 END) "
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1710
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "OfflineMovement"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Offline Movement"
   VBDataType = vbDouble
   Formula = "SUM(CASE WHEN Type = 2 OR Type = 3 OR Type = 4 THEN ISNULL(Quantity, 0) ELSE 0 END) "
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1710
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "Adjusted"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Adjusted"
   VBDataType = vbDouble
   Formula = "SUM(CASE WHEN Type = 5 OR Type = 6 THEN ISNULL(Quantity, 0) ELSE 0 END) "
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1710
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "ViewItemMovement.DateTransferred"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = True
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "ViewItemMovement.Moved"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Moved"
   VBDataType = vbBoolean
   Formula = ""
   ColHidden = True
   ColNotDisplayable = True
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans