Hello,
Could somebody tell me how to make a HQ report showing no sales with a reason code please? I found the no sale report for store level in the forum but not for HQ. It's going to be for auditing purposes.
Thank you.
Al
*This post is locked for comments
You're amazing! Thank you for your help!
1. Replace the script in between <BEGIN> and <END> with the below
FROM NonTenderTransaction LEFT OUTER JOIN Store ON NonTenderTransaction.StoreID=Store.ID LEFT OUTER JOIN Cashier ON NonTenderTransaction.CashierID=Cashier.ID AND NonTenderTransaction.StoreID=Cashier.StoreID LEFT OUTER JOIN ReasonCode ON NonTenderTransaction.ReasonCodeID=ReasonCode.ID
2. Then inside the SelCriteria line (just after <END>) put this "NonTenderTransaction.TransactionType=13"
3. After that,also add the below block of script just after the line "//--- Columns ---//" and it will display the Store name
Begin Column FieldName = "Store.Name" DrillDownFieldName = "" DrillDownReportName = "" Title = "Store Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2000 GroupMethod = groupmethodNone ColFormat = "" End Column
Michael, Thank you very much for your help. I have one more question though. I was just wondering if there's any way I can view the report in Manager instead of running your query in Administrator. Because at the store level, I can just view the following report at Store Manager(which was found in one of the old posts in this forum). I just want to view this same report in HQ Manager (with an additional column showing store names). How can I modify this report to add store name column?
Thank you.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeCustom
ReportTitle = "No Sale List"
PageOrientation = pageorientationPortrait
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = "Total"
DisplayLogo = False
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = <BEGIN>
FROM NonTenderTransaction
LEFT JOIN Cashier WITH(NOLOCK) ON NonTenderTransaction.CashierID = Cashier.ID
LEFT JOIN ReasonCode WITH(NOLOCK) ON NonTenderTransaction.ReasonCodeID = ReasonCode.ID
<END>
SelCriteria = ""
GroupBy = ""
SortOrder = "NonTenderTransaction.[Time]"
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 = "As Of: <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow
//--- Filters ---//
Begin Filter
FieldName = "NonTenderTransaction.Time"
FilterOp = reportfilteropBetween
FilterLoLim = "<WeekStart>"
FilterHilim = "<Today>"
End Filter
//--- Columns ---//
Begin Column
FieldName = "NonTenderTransaction.BatchNumber"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Batch # "
VBDataType = vbLong
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "NonTenderTransaction.Time"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Time"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignRightCenter
End Column
Begin Column
FieldName = "Time"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Time"
VBDataType = vbString
Formula = "CONVERT(nvarchar,NonTenderTransaction.Time,20)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 1875
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Cashier.Name"
DrillDownFieldName = "Cashier.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Cashier"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1500
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "NonTenderTransaction.Comment"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Comment"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ReasonCode.Code"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Reason Code"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1665
GroupMethod = groupmethodCount
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Try this against your HQ database
select Store.Name Store ,Cashier.Name Cashier,ReasonCode.Description,NonTenderTransaction.[Time] from NonTenderTransaction LEFT OUTER JOIN Store ON NonTenderTransaction.StoreID=Store.ID LEFT OUTER JOIN Cashier ON NonTenderTransaction.CashierID=Cashier.ID AND NonTenderTransaction.StoreID=Cashier.StoreID LEFT OUTER JOIN ReasonCode ON NonTenderTransaction.ReasonCodeID=ReasonCode.ID WHERE NonTenderTransaction.TransactionType=13 Order by NonTenderTransaction.[Time]
Let me know if it returns what you want
Hello Michael,
Could you please tell me the full script please? I do not know how to generate the report with the query. Thank you for your help.
Al
Run the below query against your HQ database, you will find what you are looking for, then use it to build your auditing report
select * from NonTenderTransaction order by ID desc.
Let me know if you need the full script to generate this report
Hi,
Unfortunately(if am not wrong) the table that logs this type of transaction only exists at the shop database not HQ
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,458
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
