SSRS Proforma Duplicating Misc Charge

BKen · September 9, 2021, 8:48pm

I have a customized Proforma invoice form that we use for commercial invoices. On this document, I have to show the Schedule B Code, the Country of Origin, the FOB terms, and the Cust ID.
This means that I need to join a number of additional tables. I have done all of that and it mostly works, but I am getting a duplicate of the Miscellaneous Line Charges. Below are my table joins:
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 OrderMsc_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T4
ON T1.Company = T4.Company AND T2.PartNum = T4.PartNum
LEFT OUTER JOIN FOB_" + Parameters!TableGuid.Value + " T5
ON T1.Company = T5.Company AND T1.FOB = T5.FOB
LEFT OUTER JOIN Country_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T4.ISOrigCountry = T6.CountryNum
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T7
ON T1.Company = T7.Company AND T1.CustNum = T7.CustNum"

If I DELETE that last left out join of the Customer table, the duplicate miscellaneous line charges go away, but then I am not able to show the Cust ID. Is my join setup incorrect?

Duplicated:

Not Duplicated:

But then without Cust ID:

ckrusen · September 9, 2021, 11:46pm

What relationships did you add in the RDD?

Wunschi · September 10, 2021, 5:31am

Similar to Calvin’s question, check the RDD first.
It has the customer table already assigned, so it should already be
Just copy it and untick “Exclude Column”

Alternatively, add customer as a Linked Table agianst OrderHed

BKen · September 13, 2021, 1:41pm

Compared to the standard RDD, I added 4 additional relationships (OrderDtl2Part, OrderHed2Customer, OrderHed2FOB, and Part2Country).

BKen · September 13, 2021, 1:53pm

@Wunschi , I do have CustID un-excluded:

But I don’t have the Customer table linked to the OrderHed (through LinkedTables). I thought that is what my additional relationship (OrderHed2Customer) would do.

TomAlexander · September 13, 2021, 2:00pm

Since your joins look fine I’m wondering if you’re running into this bug?:

Case Sensitivity in RDD/SSRS - ERP 10 - Epicor User Help Forum (epiusers.help)

Either way, the CustID field should be available through Linked Tables… If you add “Customer” as a Picked Link, then go to the Description Fields tab it should give you a CustID.

jkane · September 13, 2021, 2:56pm

Even though there is not a join called out in the RDD, Epicor has built the join in the background. Just add the table and join to the report query and add the field and you should be fine. Took me a long time to figure that one out. The best way to check these types of things is to run the report with retention of a day and then do a SQL query against the report DB for the tables included in the RDD.

ckrusen · September 13, 2021, 3:21pm

I’m thinking you need to do nothing but enable the field Customer.CustID (as you did), and add it to the SELECT clause of the RDL.

While you said that the RDL’s query expression doesn’t have any JOINS for the customer table, you may find that the fields from the “customer” table in the RDD are actually added to the OrderHed_GUID table.

It’s a common mistake to think that the “tables” in an Epicor supplied RDD are the actual DB tables. They’re often a table with the same name, but include calculated fields and even fields from other tables.

Take a look at the SELECT clause of the RDL’s query expression, Do you see any filed names with a table name prefixed, like T1.Customer_ShipTo That would be how the Customer.ShipTo field would be referenced fromin the OrderHed_GUID table.

I’d first try T1.CustID in the SELECT clause. Then ‘T1.Customer_CustID’

BKen · September 13, 2021, 3:55pm

This is confusing, because a relationship between Order Header and Customer is pretty obvious, but so is the relationship between Order Header to Order Detail and a Report Relationship is shown on the standard RDD for Pro-Forma Invoice for OrderHed2OrderDtl, but NOT for OrderHed2Customer:

The only linked table on the standard RDD for Pro-Forma Invoice is for OrderDtl to PartNum:

If I delete my created RDD Report Relationship of OrderHed2Customer, and do nothing else to my RDL file, then my duplicate Line Miscellaneous Charges go away.

jkane · September 13, 2021, 4:42pm

It is really confusing as it does not make much sense and I don’t even know why it works the way it does.

I just ran the Pro-Forma standard report and below is the list of tables that were created for the report, notice that Customer is there.

Out of the box, the CustID field is not included, so I need to create a new RDD. Once I have created the new RDD, I change the default report to use the new RDD. When I run that and then go look in the Customer table that was created, you can see the CustID is there.

Now you just need to get it into the report. Change the Dataset query of the report to include the Customer table, like this.

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 OrderMsc_" + Parameters!TableGuid.Value + " T3
          ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
		  LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T4
		  ON T1.Company = T4.Company AND T1.CustNum = T4.CustNum"

Add the field in the Dataset query and properties too and you now have the field available and no duplications.

ckrusen · September 13, 2021, 4:50pm

It is odd that the RDD would create a Customer_GUID table, and then not reference it in the RDL. Perhaps it is created for use in a Sub-report.

ckrusen · September 13, 2021, 4:56pm

Hmmm … The Customer_GUID table is its own Dataset in the ProFormaInvc.RDL

You should be able to add the CustID field to the SELECT clause here, and add the Query Fields.

No additional joins should be necessary.

jkane · September 13, 2021, 5:04pm

Either way works. I prefer to keep all of the fields in one table so I end up editing the query to have it all there.

jkane · September 13, 2021, 5:05pm

All of the reports are like this. Any table that is listed in the RDD creates a table in the Report DB.

ckrusen · September 13, 2021, 5:22pm

Fixed

And tables created in the Report DB, may not have a one-to-one correlation with datasources in the RDL. As seen in this very example (there is no OHOrderMsc dataset in the RDL, but there is that datasource in the RDD)

And lets not even get into what happens to the datasources in the RDL if you click the dreaded “Sync Data” button on the Report Style maintenance !

BKen · September 13, 2021, 8:51pm

Thank you for all the help. My reports are now running without duplicating the Line Miscellaneous Charges. Now my user wants to add the Ship to contact name to the proforma invoice document.

What is the best approach? Adding a new table with new relationships?

I have tried adding the CustCnt table with the following relationship to the OrderHed:

I get no error when I upload the rdl files, but when I try to generate a report, it fails to generate and the System Monitor give me a ServerError.

TomAlexander · September 13, 2021, 10:03pm

Glad to hear you got it working.

On the new error - is it a “query execution failed for dataset…” error? Or something else?

I’m sure if you post the full error message someone on the site can figure it out.

BKen · September 14, 2021, 1:44pm

Here is the new error message (from the System Monitor):
Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
Ice.Core.SsrsReportService.ReportExecutionSoapException: Exception of type ‘Ice.Core.SsrsReportService.ReportExecutionSoapException’ was thrown.
SOAP Fault: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘OrderHed’. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors)
Detail: rsProcessingAborted400An error has occurred during report processing.https://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Exceptions.ErrorStrings&EvtID=rsProcessingAborted&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=15.0.1102.861Microsoft SQL Server Reporting Services15.0.1102.8611033OsIndependent1033Microsoft.ReportingServices.ProcessingCoreAn error has occurred during report processing.Microsoft.ReportingServices.ProcessingCoreQuery execution failed for dataset ‘OrderHed’.For more information about this error navigate to the report server on the local server machine, or enable remote errors)
at Ice.Core.SsrsReportService.ReportExecutionService.SetExecutionParameters(ParameterValue[] parameters, String parameterLanguage) in C:_Releases\ICE\ICE4.1.100.0\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 189
at Ice.Core.SsrsReporting.SsrsRendererBase.ConfigureReportServerForReport(String reportPath, ParameterValue[] parameters) in C:_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 169
at Ice.Core.SsrsReporting.SsrsSingleDocumentRenderer.<>c__DisplayClass1_0.b__0() in C:_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsSingleDocumentRenderer.cs:line 49
at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func1 timedAction) in C:\_Releases\ICE\ICE4.1.100.11\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\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 247 at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 408 at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163 at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\ICE4.1.100.11\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\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 309 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 233 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func2 executeCommand, Func2 executeReader, SqlObjectsCreated sqlObjectsCreated) in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111 at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93 at Ice.Core.RptTaskBase1.XMLClose() in C:_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 223
at Ice.Core.RptTaskBase1.RestartProcess(String outputFileName) in C:\_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 363 at Ice.Core.TaskBase1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:_Releases\ICE\ICE4.1.100.11\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 79
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:_Releases\ICE\ICE4.1.100.0\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 117
at Ice.Hosting.TaskCaller.ExecuteTask() in C:_Releases\ICE\ICE4.1.100.0\Source\Server\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\ICE4.1.100.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:_Releases\ICE\ICE4.1.100.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 455

TomAlexander · September 14, 2021, 2:46pm

That means something’s off with the dataset query of the OrderHed dataset in your RDL, but it’s not off in a way that would be caught by Report Builder (such as a syntax error)…

BKen · September 14, 2021, 6:05pm

So I rewrote my RDL Left Outer Join statement for the CustCnt table and instead of using all 4 relationship links that I used on my RDD, I only used 2:
LEFT OUTER JOIN CustCnt_" + Parameters!TableGuid.Value + " T8
ON T1.Company = T8.Company AND T1.CustNum = T8.CustNum

Then I named the datafield CustCnt_Name so I could add it in the correct location on Report Builder.
Every test I tried brought in the Ship To Contact name, so it looks like it is working.

Topic		Replies	Views
Troubleshooting error - Commercial Invoice - Added Country to RDD Kinetic ERP ssrs , reports	15	777	February 29, 2024
New Dataset and new data field - RMA Form - Epicor 11, SRSS Kinetic ERP ssrs	7	323	June 4, 2024
Double Child on SSRS report Epicor ERP 10 e10 , ssrs , kinetic , rdd , rdl	14	1692	March 2, 2022
Trouble adding table to sub report in Job traveler Epicor ERP 10 e10 , ssrs , reports , rdd , sub-report	29	2655	November 18, 2020
RDD: Multiple relationships to same child table Epicor ERP 10 e10 , ssrs , reports	11	2416	May 27, 2021

SSRS Proforma Duplicating Misc Charge

Related Topics