I get the following error when running the Store Performance report. I can run the SQL query no problem, so the error must be in the column definition part.
(-2147217900) The multi-part identifier "Transaction.Time" could not be bound.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Store Performance Report by Day"
PageOrientation = pageorientationPortrait
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
SELECT [Transaction].Time,[Transaction].Total,[Transaction].SalesTax,[Transaction].TransactionNumber,Customer.AccountNumber,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '1' AND '1' THEN 'January'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '2' AND '2' THEN 'February'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '3' AND '3' THEN 'March'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '4' AND '4' THEN 'April'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '5' AND '5' THEN 'May'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '6' AND '6' THEN 'June'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '7' AND '7' THEN 'July'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '8' AND '8' THEN 'August'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '9' AND '9' THEN 'September'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '10' AND '10' THEN 'October'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '11' AND '11' THEN 'November'
WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '12' AND '12' THEN 'December' ELSE NULL END AS month
from [Transaction] LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.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 = "TransactionTime"
FilterOp = reportfilteropBetween
FilterLoLim = "<MonthStart>"
FilterHilim = "<Today>"
End Filter
//--- Columns ---//
Begin Column
FieldName = "NULL"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Group"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1500
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "TransactionTime"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Date Sold"
VBDataType = vbDate
Formula = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1500
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Total"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Total Sales w GST"
VBDataType = vbCurrency
Formula = "SUM([Transaction].Total)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 1475
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "TotalWOGST"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Total Sales w/o GST"
VBDataType = vbCurrency
Formula = "SUM([Transaction].Total-[Transaction].SalesTax)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 2000
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "TotalGSTonly"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Total Sales Tax"
VBDataType = vbCurrency
Formula = "SUM([Transaction].SalesTax)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 1475
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "NumTrans"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "# Transactions"
VBDataType = vbDouble
Formula = "COUNT([Transaction].TransactionNumber)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1625
GroupMethod = groupmethodNone
ColFormat = "#"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "AvgTicket"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Avg Sales per Trans"
VBDataType = vbCurrency
Formula = "SUM([Transaction].Total)/COUNT([Transaction].TransactionNumber)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 3000
GroupMethod = groupmethodNone
ColFormat = "$#.##"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "AvgTicketWOGST"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Avg Sales per Trans w/o GST"
VBDataType = vbCurrency
Formula = "SUM([Transaction].Total-[Transaction].SalesTax)/COUNT([Transaction].TransactionNumber)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 3000
GroupMethod = groupmethodNone
ColFormat = "$#.##"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "NumCustomers"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "# Customers"
VBDataType = vbDouble
Formula = "COUNT(Customer.AccountNumber)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1625
GroupMethod = groupmethodNone
ColFormat = "#"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "AvgSalesCust"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Avg Sales per Customer"
VBDataType = vbCurrency
Formula = "CASE WHEN COUNT(Customer.AccountNumber) <> 0 THEN SUM([Transaction].Total)/COUNT(Customer.AccountNumber) ELSE NULL END"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 3000
GroupMethod = groupmethodNone
ColFormat = "$#.##"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "AvgSalesCustWOGST"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Avg Sales per Customer w/o GST"
VBDataType = vbCurrency
Formula = "CASE WHEN COUNT(Customer.AccountNumber) <> 0 THEN SUM([Transaction].Total-[Transaction].SalesTax)/COUNT(Customer.AccountNumber) ELSE NULL END"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 3000
GroupMethod = groupmethodNone
ColFormat = "$#.##"
ColAlignment = flexAlignCenterCenter
End Column
Hi Danny,
In testing I got the same error; and when I removed the [Transaction].Time column information it then shows the same error but on the next column.
Can you let me know how you are wanting to use this report; and what you are wanting the report to look like when printed?
I see that you are setting the month in the select script; but am not seeing the month being used in the columns below.
________________________________
Just a note that Mainstream support for Microsoft Dynamics RMS 2.0 ended on July 10, 2016; and extended support ends on 7/13/2021:
https://support.microsoft.com/en-us/lifecycle/search?alpha=dynamics%20retail%20management%20system
.
This is less than 12 months from now; and you can continue using RMS after this date but technical support will not be available.
I would recommend checking with your partner in regard to what they may recommend as a replacement system after the extended support end date.
Thank you in advance.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,240 Super User 2024 Season 2
Martin Dráb 230,149 Most Valuable Professional
nmaenpaa 101,156