Skip to main content

Notifications

Microsoft Dynamics RMS forum

Profit report with Tender type

Posted on by 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

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,129 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans