Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Profit report with Ten...
Microsoft Dynamics RMS (Archived)

Profit report with Tender type

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

Hi,

I have the following profit report which appears to be working fine but I wish to add another field to show the Tender type.  I have spent a little time on it but really need help to get it to work.

Any help would be greatly appreciated.

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

Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Profit Report (Item Sales)"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber
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 Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID
LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID
LEFT JOIN SalesRep WITH(NOLOCK) ON TransactionEntry.SalesRepID = SalesRep.ID
LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID
<END>
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

Begin TitleRow
Text = "© 2004-2007 Digital Retail Solutions Inc. All rights reserved."
Font = "Arial"
FontBold = False
FontSize = 6
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 = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2205
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Supplier.SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1770
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 = 3450
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 = 1200
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 = 1200
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 = 1200
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = ""
End Column

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

Begin Column
FieldName = "Total"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total Sales"
VBDataType = vbCurrency
Formula = "TransactionEntry.Price * TransactionEntry.Quantity"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1275
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "TransactionEntry.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total Cost"
VBDataType = vbCurrency
Formula = "TransactionEntry.Cost * TransactionEntry.Quantity"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1080
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "TransactionEntry.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sold Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 975
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Profit"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total Profit"
VBDataType = vbCurrency
Formula = "(TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1140
GroupMethod = groupmethodSum
ColFormat = ""
End Column

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

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

Begin Column
FieldName = "Item.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1380
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.WebItem"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Web Item"
VBDataType = vbBoolean
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1050
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column

Begin Column
FieldName = "Item.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "On Hand"
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 810
GroupMethod = groupmethodNone
ColFormat = ""
End Column

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

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

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

Begin Column
FieldName = "Item.SalePrice"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sale Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Item.SaleStartDate"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
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 = ""
StoreIDFieldName = ""
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 = ""
StoreIDFieldName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "[Transaction].TransactionNumber"
DrillDownFieldName = "[Transaction].TransactionNumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Transaction"
VBDataType = vbLong
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1245
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "[Transaction].Time"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date Sold"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "ReasonCodeDiscount.Description"
DrillDownFieldName = "Reasoncode.Description"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Discount Reason Code"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2280
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "ReasonCodeTaxChange.Description"
DrillDownFieldName = "Reasoncode.Description"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Tax Change Reason Code"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2280
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "ReasonCodeReturn.Description"
DrillDownFieldName = "Reasoncode.Description"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Return Reason Code"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2280
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "PriceSource"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Price Source"
VBDataType = vbString
Formula = "CASE TransactionEntry.PriceSource WHEN 1 THEN 'Regular Price' WHEN 2 THEN 'Quantity Discount' WHEN 3 THEN 'Buydown Discount' WHEN 4 THEN 'Price Level Disc.' WHEN 5 THEN 'Sale Price' WHEN 6 THEN 'Disc. from Reg. Price by Cashier' WHEN 7 THEN 'Disc. from Cur. Price by Cashier' WHEN 8 THEN 'Cost Markup Disc. by Cashier' WHEN 9 THEN 'Profit Margin Disc. by Cashier' WHEN 10 THEN 'Cashier Set' WHEN 11 THEN 'Component' ELSE 'Unknown' END"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2280
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Register.Number"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Register"
VBDataType = vbLong
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1245
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "CustomerName"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Customer"
VBDataType = vbString
Formula = "Customer.LastName + ', ' + Customer.FirstName"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1350
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Customer.AccountNumber"
DrillDownFieldName = "Customer.AccountNumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Account #"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1035
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

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

Begin Column
FieldName = "SalesRep.Name"
DrillDownFieldName = "SalesRep.Name"
DrillDownReportName = ""
Title = "Sales Rep"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1350
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Thanks in advance,

Anthony

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Profit report with Tender type

    Hi Ed,

    Thank you very much, it has worked a treat!

    I understand your warning but in this case I only want to filter out one Tender type for our web orders.

    Thanks again,

    Anthony

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Profit report with Tender type

    you could try by joining the tenderentry table to the query.  Then adding the tender.description column

    Left Join TenderEntry with(nolock) on [Transaction].TransactionNumber = [tenderentry].TransactionNumber

    Left join Tender with(NoLock) on [tenderentry].tenderid = [tender].ID

    Begin Column

    FieldName = "Tender.Description"

    DrillDownFieldName = ""

    DrillDownReportName = ""

    Title = "Tender Desc"

    VBDataType = vbString

    Formula = ""

    ColHidden = False

    ColNotDisplayable = False

    FilterDisabled = False

    ColWidth = 1350

    GroupMethod = groupmethodNone

    ColFormat = ""

    ColAlignment = flexAlignLeftCenter

    End Column

    I warn you though, this will not always work correctly.  Transactions can optional have multiple tenders and since your starting table is the transaction table you will duplicate transactionentry items for a transaction if you have multiple tenders (this is because the one to many relationship transaction -> tenderentry)

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