If lines/releases are closed out on my Sales Order, how do I not make them show up on the Acknowledgement Form as well as how do I make the next available line/release come up first and not leave an empty space?
I don’t believe that’s how the out of the box report works. Sounds like some modifications have been made. Have you tried running the Out of the Box “Standard SSRS” report style?
So I was able to make the closed lines/releases not show up by adding a hidden expression for OpenLine and OpenRelease set to False. But I am still having an empty space where those closed lines/releases should be
You need to hide/unhide the tablix ROW, not the rectangle/textbox.
I looked at the SQL expression on the Orderhed database.
It looks like this:
="SELECT T1.RptLanguageID,T1.CardNumber,T1.NeedByDate,T1.OrderComment,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_BillToAddressList,T1.Calc_BillToContact,T1.Calc_CardNumber,T1.Calc_CustPartOpts,T1.Calc_FobDesc,T1.Calc_MultiNeedDate,T1.Calc_MultiRef,T1.Calc_MultiShipContacts,T1.Calc_MultiShipTo,T1.Calc_MultiShipVia,T1.Calc_PrcEmail,T1.Calc_PrcFax,T1.Calc_PrcPhone,T1.Calc_SalesPerson,T1.Calc_ShipToAddressList,T1.Calc_ShipToContact,T1.Calc_ShipToEmail,T1.Calc_ShipToFax,T1.Calc_ShipToPhone,T1.Calc_ShipViaDesc,T1.Calc_TermsDesc,T1.CurrencyCode_CurrDesc,T1.CurrencyCode_CurrSymbol,T1.Customer_ResaleID, T1.Calc_CurrSymbol, T1.Calc_MiscTotal, T1.Calc_LineMiscTotal, T2.Company,T2.ContractNum,T2.DisplaySeq,T2.DocDiscount,T2.DocUnitPrice,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.OrderComment AS OrderDtl_OrderComment,T2.OrderLine,T2.PartNum,T2.POLine,T2.PricePerCode,T2.Reference,T2.RevisionNum,T2.SalesUM,T2.SellingQuantity,T2.WarrantyComment,T2.XPartNum,T2.XRevisionNum,T2.Calc_ExtPrice,T2.Calc_LineDesc,T2.PartNum_PartDescription,T2.WarrantyCode_WarrDescription,T2.Calc_UOMForeignDesc,T3.NeedByDate AS OrderRel_NeedByDate,T3.OrderRelNum,T3.Reference AS OrderRel_Reference,T3.ReqDate,T3.SellingReqQty,T3.ShipViaCode,T3.Calc_ResaleID,T3.Calc_ShipToAddressList AS OrderRel_Calc_ShipToAddressList,T3.Calc_ShipToContact AS OrderRel_Calc_ShipToContact,T3.ShipViaCode_Description,T3.Calc_OrderRelCounter,T4.Calc_ExtPrice AS RptLiteralsCalc_ExtPrice,T4.Calc_OrderTotal AS RptLiteralsCalc_OrderTotal,T4.Calc_SalesPerson AS RptLiteralsCalc_SalesPerson,T4.Calc_ShipToContact AS RptLiteralsCalc_ShipToContact,T4.RptLiteralsDiscount,T4.FaxNum AS RptLiteralsFaxNum,T4.FOB AS RptLiteralsFOB,T4.NeedByDate AS RptLiteralsNeedByDate,T4.OrderDate AS RptLiteralsOrderDate,T4.RptLiteralsLLine,T4.OrderQty AS RptLiteralsOrderQty,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.PhoneNum AS RptLiteralsPhoneNum,T4.Reference AS RptLiteralsReference,T4.ResaleID AS RptLiteralsResaleID,T4.RevisionNum AS RptLiteralsRevisionNum,T4.RptLiteralsLCrdNum,T4.RptLiteralsLCustPart,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLPhone,T4.RptLiteralsLLNMSC,T4.RptLiteralsLOrderAck,T4.RptLiteralsLORMSC,T4.RptLiteralsLORNUM,T4.RptLiteralsLOurPart,T4.RptLiteralsLPage,T4.RptLiteralsLPONum,T4.RptLiteralsLPriPer,T4.RptLiteralsLPrtDes,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLSHSHC,T4.RptLiteralsLSHSHM,T4.RptLiteralsLSLSHE,T4.RptLiteralsLSoldTo,T4.RptLiteralsLSubTotal,T4.RptLiteralsLTerms,T4.RptLiteralsLYURPOLN,T4.RptLiteralsSalesKit,T4.UnitPrice AS RptLiteralsUnitPrice,T4.Warranty AS RptLiteralsWarranty,T4.ShipViaCode AS RptLiteralsShipViaCode,T4.OrderLine AS RptLiteralsOrderLine, T4.RptLiteralsSeeBelow
FROM OrderHed_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID"
If I add this at the end " where T2.SellingQuantity > 0" then those zeroed out line don’t pull in at all.
The result of the SQL expression is this:
="SELECT T1.RptLanguageID,T1.CardNumber,T1.NeedByDate,T1.OrderComment,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_BillToAddressList,T1.Calc_BillToContact,T1.Calc_CardNumber,T1.Calc_CustPartOpts,T1.Calc_FobDesc,T1.Calc_MultiNeedDate,T1.Calc_MultiRef,T1.Calc_MultiShipContacts,T1.Calc_MultiShipTo,T1.Calc_MultiShipVia,T1.Calc_PrcEmail,T1.Calc_PrcFax,T1.Calc_PrcPhone,T1.Calc_SalesPerson,T1.Calc_ShipToAddressList,T1.Calc_ShipToContact,T1.Calc_ShipToEmail,T1.Calc_ShipToFax,T1.Calc_ShipToPhone,T1.Calc_ShipViaDesc,T1.Calc_TermsDesc,T1.CurrencyCode_CurrDesc,T1.CurrencyCode_CurrSymbol,T1.Customer_ResaleID, T1.Calc_CurrSymbol, T1.Calc_MiscTotal, T1.Calc_LineMiscTotal, T2.Company,T2.ContractNum,T2.DisplaySeq,T2.DocDiscount,T2.DocUnitPrice,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.OrderComment AS OrderDtl_OrderComment,T2.OrderLine,T2.PartNum,T2.POLine,T2.PricePerCode,T2.Reference,T2.RevisionNum,T2.SalesUM,T2.SellingQuantity,T2.WarrantyComment,T2.XPartNum,T2.XRevisionNum,T2.Calc_ExtPrice,T2.Calc_LineDesc,T2.PartNum_PartDescription,T2.WarrantyCode_WarrDescription,T2.Calc_UOMForeignDesc,T3.NeedByDate AS OrderRel_NeedByDate,T3.OrderRelNum,T3.Reference AS OrderRel_Reference,T3.ReqDate,T3.SellingReqQty,T3.ShipViaCode,T3.Calc_ResaleID,T3.Calc_ShipToAddressList AS OrderRel_Calc_ShipToAddressList,T3.Calc_ShipToContact AS OrderRel_Calc_ShipToContact,T3.ShipViaCode_Description,T3.Calc_OrderRelCounter,T4.Calc_ExtPrice AS RptLiteralsCalc_ExtPrice,T4.Calc_OrderTotal AS RptLiteralsCalc_OrderTotal,T4.Calc_SalesPerson AS RptLiteralsCalc_SalesPerson,T4.Calc_ShipToContact AS RptLiteralsCalc_ShipToContact,T4.RptLiteralsDiscount,T4.FaxNum AS RptLiteralsFaxNum,T4.FOB AS RptLiteralsFOB,T4.NeedByDate AS RptLiteralsNeedByDate,T4.OrderDate AS RptLiteralsOrderDate,T4.RptLiteralsLLine,T4.OrderQty AS RptLiteralsOrderQty,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.PhoneNum AS RptLiteralsPhoneNum,T4.Reference AS RptLiteralsReference,T4.ResaleID AS RptLiteralsResaleID,T4.RevisionNum AS RptLiteralsRevisionNum,T4.RptLiteralsLCrdNum,T4.RptLiteralsLCustPart,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLPhone,T4.RptLiteralsLLNMSC,T4.RptLiteralsLOrderAck,T4.RptLiteralsLORMSC,T4.RptLiteralsLORNUM,T4.RptLiteralsLOurPart,T4.RptLiteralsLPage,T4.RptLiteralsLPONum,T4.RptLiteralsLPriPer,T4.RptLiteralsLPrtDes,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLSHSHC,T4.RptLiteralsLSHSHM,T4.RptLiteralsLSLSHE,T4.RptLiteralsLSoldTo,T4.RptLiteralsLSubTotal,T4.RptLiteralsLTerms,T4.RptLiteralsLYURPOLN,T4.RptLiteralsSalesKit,T4.UnitPrice AS RptLiteralsUnitPrice,T4.Warranty AS RptLiteralsWarranty,T4.ShipViaCode AS RptLiteralsShipViaCode,T4.OrderLine AS RptLiteralsOrderLine, T4.RptLiteralsSeeBelow
FROM OrderHed_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID where T2.SellingQuantity > 0"
1 Like
So doing that just hides it from the report form. I would like that space to be removed. So say for example all of line 1 was closed including all of its releases, then the report would start from line 2 without leaving any empty space. Say then line 3 was partially closed, it would keep line 3 info and name whichever releases were open without any white space
Again, if you hide the ROW of the tablix and not the rectangle/textbox the lines will not show a space.
Also @estm8ben’s suggestion of using the SQL query would work.
....
WHERE T2.OpenLine = 1
this would never reach the tablix so no blank lines would appear
2 Likes
When I add that bit of code into the dataset expression it throws an error in system monitor
Is there a specific section I should be adding it to or at the end is fine?
Providing the error would be helpful
Sorry, here you go:
Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
System.InvalidOperationException: Response is not well-formed XML. —> System.Xml.XmlException: Unexpected end of file while parsing Name has occurred. Line 1, position 65537.
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.ParseQName(Boolean isQName, Int32 startOffset, Int32& colonPos)
at System.Xml.XmlTextReaderImpl.ParseElement()
at System.Xml.XmlTextReaderImpl.ParseElementContent()
at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)
at System.Xml.XmlLoader.ReadCurrentNode(XmlDocument doc, XmlReader reader)
at System.Xml.XmlDocument.ReadNode(XmlReader reader)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadSoapException(XmlReader reader)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
— End of inner exception stack trace —
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Ice.Core.SsrsReportService.ReportExecutionService.Render(String Format, String DeviceInfo, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds) in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 633
at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 148
at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func1 timedAction) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 88 at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReport(String ssrsRenderFormat, String printProgram, Boolean ignorePageSettings) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 247 at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 408 at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163 at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 31 at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func2 reportsRenderer, Action1 fillSysRptLstRow, Action2 processReport, Func3 filterTableAttachmentsFunc) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 305 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 229 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func2 executeCommand, Func2 executeReader, SqlObjectsCreated sqlObjectsCreated) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111 at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93 at Ice.Core.RptTaskBase1.XMLClose() in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 229
at Erp.Internal.OM.SalesOrderAck.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:_releases\ERP\ERP10.2.700.0\Source\Server\Internal\OM\SalesOrderAck\SalesOrderAck.cs:line 646
at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 83
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:_releases\ICE\ICE3.2.700.12\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 117
at Ice.Hosting.TaskCaller.ExecuteTask() in C:_releases\ICE\ICE3.2.700.12\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 59
at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 454
A basic SQL query is structured as such. This is an example
SELECT T1.FieldName
FROM TableName T1 INNER JOIN AnotherTable T2 ON T1.FieldToLinkWith = T2.MatchingField
WHERE T1.FieldName = 1
I am unsure of what your query looks like, but I’m going to assume you have no WHERE clause. Add the line beneath the FROM Clause and the joins
1 Like
This is what the SQL query looks like before adding in the T2 and T3 where clauses:
=“SELECT T1.RptLanguageID,T1.DocTotalDiscount,T1.CardNumber,T1.NeedByDate,T1.OrderComment,T1.OrderNum,T1.Customer_CustID,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_BillToAddressList,T1.Calc_BillToContact,T1.Calc_CardNumber,T1.Calc_CustPartOpts,T1.Calc_FobDesc,T1.Calc_MultiNeedDate,T1.Calc_MultiRef,T1.Calc_MultiShipContacts,T1.Calc_MultiShipTo,T1.Calc_MultiShipVia,T1.Calc_PrcEmail,T1.Calc_PrcFax,T1.Calc_PrcPhone,T1.Calc_SalesPerson,T1.Calc_ShipToAddressList,T1.Calc_ShipToContact,T1.Calc_ShipToEmail,T1.Calc_ShipToFax,T1.Calc_ShipToPhone,T1.Calc_ShipViaDesc,T1.Calc_TermsDesc,T1.CurrencyCode_CurrDesc,T1.CurrencyCode_CurrSymbol,T1.Customer_ResaleID, T1.Calc_CurrSymbol, T1.Calc_MiscTotal, T1.Calc_LineMiscTotal,T2.Company,T2.ContractNum,T2.DisplaySeq,T2.DocDiscount,T2.DocUnitPrice,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.OrderComment AS OrderDtl_OrderComment,T2.OpenLine,T2.OrderLine,T2.PartNum,T2.POLine,T2.PricePerCode,T2.Reference,T2.RevisionNum,T2.SalesUM,T2.SellingQuantity,T2.WarrantyComment,T2.XPartNum,T2.XRevisionNum,T2.Calc_ExtPrice,T2.Calc_LineDesc,T2.PartNum_PartDescription,T2.WarrantyCode_WarrDescription,T2.Calc_UOMForeignDesc, T2.Calc_FSAInstallationDesc, T2.Calc_FSAInstallationType, T2.Calc_FSAInstallPrice,T2.Calc_LineTaxAmt,T2.Calc_FSAInstallationRequired, T3.NeedByDate AS OrderRel_NeedByDate,T3.OpenRelease,T3.OrderRelNum,T3.Reference AS OrderRel_Reference,T3.ReqDate,T3.SellingReqQty,T3.ShipViaCode,T3.Calc_ResaleID,T3.Calc_ShipToAddressList AS OrderRel_Calc_ShipToAddressList,T3.Calc_ShipToContact AS OrderRel_Calc_ShipToContact,T3.ShipViaCode_Description,T3.Calc_OrderRelCounter, T3. Calc_AttributeSetShortDesc, T4.Calc_ExtPrice AS RptLiteralsCalc_ExtPrice,T4.Calc_OrderTotal AS RptLiteralsCalc_OrderTotal,T4.Calc_SalesPerson AS RptLiteralsCalc_SalesPerson,T4.Calc_ShipToContact AS RptLiteralsCalc_ShipToContact,T4.RptLiteralsDiscount,T4.FaxNum AS RptLiteralsFaxNum,T4.FOB AS RptLiteralsFOB,T4.NeedByDate AS RptLiteralsNeedByDate,T4.OrderDate AS RptLiteralsOrderDate,T4.RptLiteralsLLine,T4.OrderQty AS RptLiteralsOrderQty,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.PhoneNum AS RptLiteralsPhoneNum,T4.Reference AS RptLiteralsReference,T4.ResaleID AS RptLiteralsResaleID,T4.RevisionNum AS RptLiteralsRevisionNum,T4.RptLiteralsLCrdNum,T4.RptLiteralsLCustPart,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLPhone,T4.RptLiteralsLLNMSC,T4.RptLiteralsLOrderAck,T4.RptLiteralsLORMSC,T4.RptLiteralsLORNUM,T4.RptLiteralsLOurPart,T4.RptLiteralsLPage,T4.RptLiteralsLPONum,T4.RptLiteralsLPriPer,T4.RptLiteralsLPrtDes,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLSHSHC,T4.RptLiteralsLSHSHM,T4.RptLiteralsLSLSHE,T4.RptLiteralsLSoldTo,T4.RptLiteralsLSubTotal,T4.RptLiteralsLTerms,T4.RptLiteralsLYURPOLN,T4.RptLiteralsSalesKit,T4.UnitPrice AS RptLiteralsUnitPrice,T4.Warranty AS RptLiteralsWarranty,T4.ShipViaCode AS RptLiteralsShipViaCode,T4.OrderLine AS RptLiteralsOrderLine, T4.RptLiteralsSeeBelow, T1.Calc_TaxMethod, T4.RptLiteralsLTotalTax, T1.DocTotalTax, T1.DocOrderAmt, T1.Calc_TotalTaxAmt, T4.RptLiteralsLInstallation, T4.RptLiteralsLInstallTotal, T4.RptLiteralsLAttributeSetShortD
FROM OrderHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID"
="SELECT T1.RptLanguageID,T1.DocTotalDiscount,T1.CardNumber,T1.NeedByDate,T1.OrderComment,T1.OrderNum,T1.Customer_CustID,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_BillToAddressList,T1.Calc_BillToContact,T1.Calc_CardNumber,T1.Calc_CustPartOpts,T1.Calc_FobDesc,T1.Calc_MultiNeedDate,T1.Calc_MultiRef,T1.Calc_MultiShipContacts,T1.Calc_MultiShipTo,T1.Calc_MultiShipVia,T1.Calc_PrcEmail,T1.Calc_PrcFax,T1.Calc_PrcPhone,T1.Calc_SalesPerson,T1.Calc_ShipToAddressList,T1.Calc_ShipToContact,T1.Calc_ShipToEmail,T1.Calc_ShipToFax,T1.Calc_ShipToPhone,T1.Calc_ShipViaDesc,T1.Calc_TermsDesc,T1.CurrencyCode_CurrDesc,T1.CurrencyCode_CurrSymbol,T1.Customer_ResaleID, T1.Calc_CurrSymbol, T1.Calc_MiscTotal, T1.Calc_LineMiscTotal,T2.Company,T2.ContractNum,T2.DisplaySeq,T2.DocDiscount,T2.DocUnitPrice,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.OrderComment AS OrderDtl_OrderComment,T2.OpenLine,T2.OrderLine,T2.PartNum,T2.POLine,T2.PricePerCode,T2.Reference,T2.RevisionNum,T2.SalesUM,T2.SellingQuantity,T2.WarrantyComment,T2.XPartNum,T2.XRevisionNum,T2.Calc_ExtPrice,T2.Calc_LineDesc,T2.PartNum_PartDescription,T2.WarrantyCode_WarrDescription,T2.Calc_UOMForeignDesc, T2.Calc_FSAInstallationDesc, T2.Calc_FSAInstallationType, T2.Calc_FSAInstallPrice,T2.Calc_LineTaxAmt,T2.Calc_FSAInstallationRequired, T3.NeedByDate AS OrderRel_NeedByDate,T3.OpenRelease,T3.OrderRelNum,T3.Reference AS OrderRel_Reference,T3.ReqDate,T3.SellingReqQty,T3.ShipViaCode,T3.Calc_ResaleID,T3.Calc_ShipToAddressList AS OrderRel_Calc_ShipToAddressList,T3.Calc_ShipToContact AS OrderRel_Calc_ShipToContact,T3.ShipViaCode_Description,T3.Calc_OrderRelCounter, T3. Calc_AttributeSetShortDesc, T4.Calc_ExtPrice AS RptLiteralsCalc_ExtPrice,T4.Calc_OrderTotal AS RptLiteralsCalc_OrderTotal,T4.Calc_SalesPerson AS RptLiteralsCalc_SalesPerson,T4.Calc_ShipToContact AS RptLiteralsCalc_ShipToContact,T4.RptLiteralsDiscount,T4.FaxNum AS RptLiteralsFaxNum,T4.FOB AS RptLiteralsFOB,T4.NeedByDate AS RptLiteralsNeedByDate,T4.OrderDate AS RptLiteralsOrderDate,T4.RptLiteralsLLine,T4.OrderQty AS RptLiteralsOrderQty,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.PhoneNum AS RptLiteralsPhoneNum,T4.Reference AS RptLiteralsReference,T4.ResaleID AS RptLiteralsResaleID,T4.RevisionNum AS RptLiteralsRevisionNum,T4.RptLiteralsLCrdNum,T4.RptLiteralsLCustPart,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLPhone,T4.RptLiteralsLLNMSC,T4.RptLiteralsLOrderAck,T4.RptLiteralsLORMSC,T4.RptLiteralsLORNUM,T4.RptLiteralsLOurPart,T4.RptLiteralsLPage,T4.RptLiteralsLPONum,T4.RptLiteralsLPriPer,T4.RptLiteralsLPrtDes,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLSHSHC,T4.RptLiteralsLSHSHM,T4.RptLiteralsLSLSHE,T4.RptLiteralsLSoldTo,T4.RptLiteralsLSubTotal,T4.RptLiteralsLTerms,T4.RptLiteralsLYURPOLN,T4.RptLiteralsSalesKit,T4.UnitPrice AS RptLiteralsUnitPrice,T4.Warranty AS RptLiteralsWarranty,T4.ShipViaCode AS RptLiteralsShipViaCode,T4.OrderLine AS RptLiteralsOrderLine, T4.RptLiteralsSeeBelow, T1.Calc_TaxMethod, T4.RptLiteralsLTotalTax, T1.DocTotalTax, T1.DocOrderAmt, T1.Calc_TotalTaxAmt, T4.RptLiteralsLInstallation, T4.RptLiteralsLInstallTotal, T4.RptLiteralsLAttributeSetShortD
FROM OrderHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID
WHERE T2.OpenLine = 1"
EDIT: His first double quote character was a different symbol
Yep that’s where I added that and it didn’t seem to like it
Have you confirmed that the OpenLine field has been unexcluded from your RDD?
Yes I have unexcluded that and OpenRelease
WHERE T2.OpenLine = 1 and T3.OpenRelease = 1"
Can I just put it like that?
Even though my query looks identical to the one you pasted into the report, i noticed the the first double quotes looked off in notepad++. Copy and paste my reply, or replace the double quotes with the double quote key from your keyboard
yes that syntax is fine