Need to add ReceiptDate to Receipt Inventory Movement Report

Epicor ERP 10
1

I need to add the field receipt date to the inventory movement report for receipts, but this SSRS report only pulls in 1 of the tables from the RDD, PartTran. I see the the rcvhead is already in the RDD, and I modified the query in SSRS to link the table to Parttran and return the field but i get this error trying to run it

For more information about this error navigate to the report server on the local server machine, or enable remote errors

Query execution failed for dataset ‘PartTran’. (rsErrorExecutingCommand)

An error has occurred during report processing. (rsProcessingAborted)

2

You didn’t modify the Rdd at all?

3

No I did not as the table and field are both already included

4

Did you navigate to the report server and look at the error in the even viewer?

5

I am not seeing where to find this in the Event Viewer on the server

6

It should show something under the Windows Logs > Application log

7

There are no errors there corresponding with when I tried to run the report

8

Alright

9

Did you modify it correctly?

If you download that report and then try and run it in report builder using a guid from an archived report run in Epicor it should generate.

Try running it in Report Builder and use the guid from your system monitor. Make sure you change your archive period on your report prompt to 1 day.

10

Sorry should have clarified. Report Builder is where I am receiving the error when I try to generate it using the guid.

I added the field T5.ReceiptDate to the select and added the below to join it:

Left Outer Join RcvHead_" + Parameters!TableGuid.Value + " T5
on T1.Company = T5.Company and T1.PONum = T5.PONum and T1.PackSlip = T5.PackSlip

11

Marc,

Have you verified that RcvHead is actually getting created in the SSRS database when the report is ran?

If so, are all of the fields that you are joining on also present in the table?

12

You have archived the report and its data correct? Otherwise when you query using the guid in report builder there is a chance that the tables have already been dropped from the reporting database.

13

Yes, I selected 1 day archive when I ran it. It runs correctly in report builder before I make changes.

Not were were to look for the SSRS database to see if RcvHead is being created correctly

Edit: Found the DB and the RcvHead table is present with the right guid and all of the fields

14

can you paste the query in the dataset here?

15
="SELECT T1.ActTranQty, T1.AsOfDate, T1.AssemblySeq, T1.AssetNum, T1.BurUnitCost, T1.Calc_DocNum, T1.Calc_InvoiceNum, T5.ReceiptDate, T1.Calc_LineNum, T1.Calc_PrintedBy, T1.Calc_SNList, T1.Calc_VendorAddrList, T1.Calc_VendorID, T1.Calc_WareHouseDesc, T1.Company, T1.EmpID, T1.ExtCost, T1.InvAdjReason, T1.JobNum2, T1.JobSeq2, T1.LotNum2, T1.OrderLine, T1.OrderNum, T1.PackLine, T1.PackNum, T1.PackSlip, T1.PBInvNum, T1.PORelNum, T1.POUnitCost, T1.ProjProcessed, T1.SubUnitCost, T1.SysDate, T1.SysTime, T1.TranDocTypeID, T1.TranNum, T1.TranQty, T1.WareHouse2, T1.AdditionNum, T1.AsOfSeq, T1.AssemblySeq2, T1.BinNum, T1.BinNum2, T1.BinType, T1.Calc_Bin2Description, T1.Calc_BinDescription, T1.Calc_EntryPerson, T1.Calc_InvoiceDate, T1.Calc_ReasonDescription, T1.Calc_WareHouse2Desc, T1.Calc_Weight, T1.DisposalNum, T1.EntryPerson, T1.InvAdjSrc, T1.JobNum, T1.JobSeq, T1.LbrUnitCost, T1.LegalNumber, T1.LoanFlag, T1.LotNum, T1.MscNum, T1.MtlUnitCost, T1.OrderRelNum, T1.PartDescription, T1.PartNum, T1.Plant, T1.Plant2, T1.POLine, T1.PONum, T1.POReceiptQty, T1.PurPoint, T1.TranDate, T1.TranReference, T1.TranType, T1.UM, T1.WareHouseCode, T1.RptLanguageID, T1.MtlBurUnitCost, T2.ShipName, T2.ShipAddress1, T2.ShipAddress2, T2.ShipCity, T2.ShipState, T2.ShipZip, T2.ShipCountry, T3.BTOOrderNum, T3.BTOOrderLine, T2.CommentText, T4.JobNum as JobNum3

FROM PartTran_" + Parameters!TableGuid.Value + " T1

Left Outer Join POHeader_" + Parameters!TableGuid.Value + " T2
On T1.Company = T2.Company

Left Outer Join PODetail_" + Parameters!TableGuid.Value + " T3
on T1.POLine = T3.POLine and T1.PONum = T3.PONUM and T1.TranNum = T3.Calc_TranNum

Left Outer Join RcvHead_" + Parameters!TableGuid.Value + " T5
on T1.Company = T5.Company and T1.PONum = T5.PONum and T1.PackSlip = T5.PackSlip

left outer join
(
select
a1.JobNum,
a1.PONum,
a1.POLine
from erp10.erp.PORel a1
where a1.JobNum <> ‘’
group by
a1.JobNum,
a1.PONum,
a1.POLine
) T4
on T1.POLine = T4.POLine and T1.PONum = T4.PONUM"

16

Can you also give me the reportID so I can look it up?

17

ReceiptInvMvmnt

18

If you take this out, will it run?

19

Note that this was modified by a consultant and it looks like they added a direct connection to PORel table that you would need to remove if running yourself

20

No, removing that did not work