Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Customized HQ report e...
Microsoft Dynamics RMS (Archived)

Customized HQ report error

(0) ShareShare
ReportReport
Posted on by nelsonq 55

Hi all, this is a customized report that I did. But I encounter an error when I try to view the report in Headquarters Manager:
"Conversion failed when converting the nvarchar value '3000/A11' to data type int." 

Do note that 3000/A11 is my customer account no.

Please help! Thanks.
Nelson

Below is my raw code:

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

Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Tender Summary - HQ"
PageOrientation = pageorientationPortrait
WordWrap = True
ShowDateTimePicker = False
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VIEWTenders') DROP VIEW VIEWTenders"
PreQuery2 = <BEGIN>

CREATE VIEW VIEWTenders AS

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,
Register.Number AS Register,
Cashier.Name,
Cashier.Number,
Customer.AccountNumber,
TenderEntry.Description,
[Transaction].CustomerID,
CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101) as Date,
[Transaction].Time as Time,
[Transaction].TransactionNumber,
NULL AS OtherNumber,
TenderEntry.Amount,
CASE WHEN RecallType = 1 THEN 'Return' ELSE CASE WHEN RecallType = 3 THEN 'Void' ELSE 'Sale' END END as TrxType

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN [Transaction] ON TenderEntry.TransactionNumber = [Transaction].TransactionNumber
AND TenderEntry.StoreID = [Transaction].StoreID
LEFT JOIN Batch ON [Transaction].BatchNumber = Batch.BatchNumber
AND [Transaction].StoreID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Batch.StoreID = Register.StoreID
LEFT JOIN Cashier ON [Transaction].CashierID = Cashier.ID
AND [Transaction].StoreID = Cashier.StoreID
LEFT JOIN Customer ON [Transaction].CustomerID = Customer.ID
AND [Transaction].StoreID = Cashier.StoreID

WHERE Register.Number IS not null


UNION

SELECT
Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,
Register.Number AS Register,
Cashier.Name,
Cashier.Number,
Customer.AccountNumber,
TenderEntry.Description,
Payment.CustomerID,
CONVERT(datetime, CONVERT(nvarchar, Payment.Time, 101), 101) as Date,
Payment.Time as Time,
NULL as TransactionNumber,
Payment.ID as OtherNumber,
TendErentry.Amount,
'Payment' as TrxType

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN Payment ON Payment.ID = Tenderentry.PaymentID
AND TenderEntry.StoreID = Payment.StoreID
LEFT JOIN Batch ON Payment.BatchNumber = Batch.BatchNumber
AND Payment.StoreID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Batch.StoreID = Register.StoreID
LEFT JOIN Cashier ON Payment.CashierID = Cashier.ID
AND Payment.StoreID = Cashier.StoreID
LEFT JOIN Customer ON Payment.CustomerID = Customer.ID

WHERE Register.Number IS NOT NULL

UNION

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,
Register.Number AS Register,
Cashier.Name,
Cashier.Number,
0 as CustomerID,
TenderEntry.Description,
0 as CustomerID,
CONVERT(datetime, CONVERT(nvarchar, DropPayout.Time, 101), 101) as Date,
DropPayout.Time as Time,
NULL as TransactionNumber,
DropPayout.ID as OtherNumber,
TenderEntry.Amount,
'Drop/Payout' as TrxType

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN DropPayout ON DropPayout.ID = TenderEntry.DropPayoutID
AND TenderEntry.StoreID = DropPayout.StoreID
LEFT JOIN Batch ON DropPayout.BatchNumber = Batch.BatchNumber
AND DropPayout.StoreID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Batch.StoreID = Register.StoreID
LEFT JOIN Cashier ON DropPayout.CashierID = Cashier.ID
AND DropPayout.StoreID = Cashier.StoreID


WHERE Register.Number IS NOT NULL

UNION

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,
Register.Number AS Register,
Cashier.Name,
Cashier.Number,
0 as CustomerID,
TenderEntry.Description,
[Order].CustomerID,
CONVERT(datetime, CONVERT(nvarchar, OrderHistory.Date, 101), 101) AS Date,
OrderHistory.Date as Time,
NULL as TransactionNumber,
OrderHistory.OrderID as OtherNumber,
TenderEntry.Amount,
CASE WHEN Type = 2 THEN 'Work Order' ELSE CASE WHEN Type = 5 THEN 'Layaway' ELSE 'Back Order' END END as TrxType

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN OrderHistory ON OrderHistory.ID = TenderEntry.OrderHistoryID
AND TenderEntry.StoreID = OrderHistory.StoreID
LEFT JOIN [Order] ON OrderHistory.OrderID = [Order].ID
AND OrderHistory.ID = [Order].StoreID
LEFT JOIN Batch ON OrderHistory.BatchNumber = Batch.BatchNumber
AND OrderHistory.StoreID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Batch.StoreID = Register.StoreID
LEFT JOIN Cashier ON OrderHistory.CashierID = Cashier.ID
AND OrderHistory.StoreID = Cashier.StoreID


WHERE OrderHistory.TransactionNumber = 0
<END>
TablesQueried = "FROM VIEWTenders LEFT JOIN Customer ON ViewTenders.CustomerID = Customer.ID AND ViewTenders.StoreID = Customer.StoreID"
SelCriteria = ""
GroupBy = ""
SortOrder = "VIEWTenders.Time, VIEWTenders.Amount DESC"
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 ---//


//--- Columns ---//

Begin Column
FieldName = "VIEWTenders.Date"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.TrxType"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Trx Type"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1704
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "VIEWTenders.StoreName"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store Name"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1392
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Tender"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1956
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.TransactionNumber"
DrillDownFieldName = "[Transaction].TransactionNumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Trx #"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 840
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.OtherNumber"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Other #"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 840
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.AccountNumber"
DrillDownFieldName = "VIEWTenders.AccountNumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Customer #"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1092
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.Amount"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Amount"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1080
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.Register"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Register #"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1020
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.Number"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Cashier #"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 960
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.StoreID"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store ID"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1392
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

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

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

Begin Column
FieldName = "VIEWTenders.Amount"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Max Amount"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1212
GroupMethod = groupmethodMax
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.Amount"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Min Amount"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1188
GroupMethod = groupmethodMin
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.Amount"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Avg Amount"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodAverage
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "VIEWTenders.RegDesc"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Reg. Descrptn."
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1380
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "Customer.FirstName"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Customer First Name"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 840
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

Begin Column
FieldName = "Customer.LastName"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Customer Last Name"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 840
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftTop
End Column

*This post is locked for comments

  • Guy Schulmeister Profile Picture
    Guy Schulmeister 50 on at
    RE: Customized HQ report error

    I konw this is old, but as a DBA I would be pretty sure that the issue is with the following command in the view (after the union)

    0 as CustomerID

    This is implying that the field could be an integer, I would change it to

    '0' as CustomerID

    Cheers

    Guy

  • zxun Profile Picture
    zxun 140 on at
    RE: Customized HQ report error

    Can you please try to change the customer account and avoid using "/"?

    I have a feeling. It is not your report issue, but customer account.

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans