Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Please help me to add ...
Microsoft Dynamics RMS (Archived)

Please help me to add additional columns in this report

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

Hello,

I have the following report from Microsoft Support Page (Detailed Sales Report by Hour HQ).  I wish to add additional columns if possibel: 1. tax column, 2. tax+total sale column, and 3. Discount Reason Code column.

Thank you in advance for your help.

Al

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

Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Detailed Sales Report"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 2
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>

FROM TransactionEntry
INNER JOIN [Transaction] WITH(NOLOCK)
ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID
INNER 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
LEFT JOIN Customer ON [Transaction].CustomerID = Customer.ID
LEFT JOIN SalesRep ON TransactionEntry.SalesRepID = SalesRep.ID
AND TransactionEntry.StoreID = SalesRep.StoreID

<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


//--- Filters ---//
Begin Filter
FieldName = "TransactionTime"
FilterOp = reportfilteropBetween
FilterLoLim = "<Today>"
FilterHilim = "<Today>"
End Filter


//--- Columns ---//

Begin Column
FieldName = "Store.Name"
DrillDownFieldName = "Store.Name"
DrillDownReportName = ""
Title = "Store Name"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "TimeSold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Time Sold"
VBDataType = vbString
Formula = "cast(DatePart(Hh,[Transaction].Time) as nvarchar)+ ':00'"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.ID"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store ID"
VBDataType = vbLong
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 870
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.StoreCode"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store Code"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.Region"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store Region"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1335
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.City"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store City"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1020
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.State"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store State"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1155
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.Zip"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store Zip"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Store.Country"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Store Country"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1560
GroupMethod = groupmethodNone
ColFormat = ""
End Column

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 = 1515
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

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

Begin Column
FieldName = "Item.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
Title = "Item"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1380
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.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Description"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2115
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

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

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

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

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

Begin Column
FieldName = "Item.PriceB"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "PriceB"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 660
GroupMethod = groupmethodNone
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 = groupmethodNone
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 = groupmethodNone
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 = groupmethodNone
ColFormat = ""
End Column

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

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

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

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

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

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

Begin Column
FieldName = "TransactionTime"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date Sold"
VBDataType = vbDate
Formula = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "ReasonCodeTaxChange.Description"
DrillDownFieldName = "Reasoncode.Description"
DrillDownReportName = ""
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 = ""
Title = "Return Reason Code"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2280
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

*This post is locked for comments

  • TheNeos Profile Picture
    TheNeos 1,335 on at
    RE: Please help me to add additional columns in this report

    Glad to hear that.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Please help me to add additional columns in this report

    Thank you for your help. That helped me a lot!

  • Verified answer
    TheNeos Profile Picture
    TheNeos 1,335 on at
    RE: Please help me to add additional columns in this report

    Hello,

    The following report is not for HQ but has the components of Tax, Totals Tax and without, and discount reason code. You may work around to meet your needs.

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

    Begin ReportSummary
       ReportType = reporttypeSales
       ReportTitle = "Detailed Sales Report (Tax Inc"
       PageOrientation = pageorientationPortrait
       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   Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID
                LEFT JOIN   QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.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


    //--- Filters ---//

    Begin Filter
       FieldName = "Item.ItemLookupCode"
       FilterOp = reportfilteropEqual
       FilterLoLim = "9326243002856"
       FilterHilim = "9326243002856"
       FilterNegated = False
       FilterConnector = reportfilterbooleanconAND
    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 = 1395
       GroupMethod = groupmethodNone
       ColFormat = ""
       ColAlignment = flexAlignLeftCenter
    End Column

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

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

    Begin Column
       FieldName = "Item.ItemLookupCode"
       DrillDownFieldName = "Item.ItemLookupCode"
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Item"
       VBDataType = vbString
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1305
       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.Description"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Description"
       VBDataType = vbString
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1395
       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.Price"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Price"
       VBDataType = vbCurrency
       Formula = ""
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 555
       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.MSRP"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "MSRP"
       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 = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 900
       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 = 795
       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 = 795
       GroupMethod = groupmethodNone
       ColFormat = ""
    End Column

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

    Begin Column
       FieldName = "TotalEx"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Total (Tax Ex)"
       VBDataType = vbCurrency
       Formula = "(TransactionEntry.Price * TransactionEntry.Quantity) - TransactionEntry.SalesTax"
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1200
       GroupMethod = groupmethodSum
       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 = "TransactionEntry.Cost"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Cost"
       VBDataType = vbCurrency
       Formula = ""
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 900
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "SalesTax"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Sales Tax"
       VBDataType = vbCurrency
       Formula = "TransactionEntry.SalesTax"
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1005
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

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

    Begin Column
       FieldName = "ProfitMargin"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "Profit Margin"
       VBDataType = vbDouble
       Formula = "CASE WHEN ((TransactionEntry.Price * TransactionEntry.Quantity) - TransactionEntry.SalesTax) <> 0 THEN ((TransactionEntry.Price * TransactionEntry.Quantity) - (TransactionEntry.Cost * TransactionEntry.Quantity)  - TransactionEntry.SalesTax) / ((TransactionEntry.Price * TransactionEntry.Quantity) - TransactionEntry.SalesTax) ELSE 0 END"
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 900
       GroupMethod = groupmethodAverage
       ColFormat = "0.00%"
    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 (CASE WHEN TransactionEntry.QuantityDiscountID <> 0 THEN QuantityDiscount.Description ELSE 'Quantity Discount' END) WHEN 3 THEN 'Buydown Discount' WHEN 4 THEN 'Price Level Disc.' WHEN 5 THEN 'Sale Price' WHEN 6 THEN 'Disc. from Reg. Price' WHEN 7 THEN 'Disc. from Cur. Price' WHEN 8 THEN 'Cost Markup Disc.' WHEN 9 THEN 'Profit Margin Disc.' WHEN 10 THEN 'Cashier Set' WHEN 11 THEN 'Component'  WHEN 12 THEN 'Price Level A Disc.' WHEN 13 THEN 'Price Level B Disc.' WHEN 14 THEN 'Price Level C Disc.'  WHEN 15 THEN 'Disc. from Reg. Price' WHEN 16 THEN 'Disc. from Cur. Price' WHEN 17 THEN 'Cost Markup Disc.' WHEN 18 THEN 'Profit Margin Disc.' 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


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,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans