Skip to main content

Notifications

Small and medium business | Business Central, N...
Unanswered

Help with Report Error

Posted on by Microsoft Employee

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

  • ToddB Profile Picture
    ToddB on at
    RE: Help with Report Error

    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

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,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans