web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Help with Report Error
Small and medium business | Business Central, N...
Unanswered

Help with Report Error

(0) ShareShare
ReportReport
Posted on by Community Member

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.

performance.PNG

//--- 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

I have the same question (0)
  • ToddB Profile Picture
    ToddB on at

    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://community.dynamics.com/rms/b/rmssupport/archive/2015/10/22/microsoft-dynamics-rms-2-0-mainstream-support-discontinuation-july-10-2016

    https://support.microsoft.com/en-us/lifecycle/search?alpha=dynamics%20retail%20management%20system

    .pastedimage1596811663911v1.png

    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.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,092

#2
YUN ZHU Profile Picture

YUN ZHU 663 Super User 2025 Season 2

#3
Sumit Singh Profile Picture

Sumit Singh 515

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans