Editing SSRS Report Error for Epicor cloud

Kinetic ERP
, , ,
1

Epicor version 11.2.400

Something is wrong, please correct me.

We’re using the Epicor cloud system and I’ve been asked to add a column to an SSRS report. I’m going to add a field LegerNumber form AgedPayablesReport Error

Program Ice.Services.Lib.RunTask when executing task 442600 raised an unexpected exception with the following message: RunTask:
Ice.Core.SsrsReporting.SsrsCaller.SsrsException: The SSRS server returned the status code 500 (InternalServerError) with the following error text:
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 ‘RptLabels’. —> System.Data.SqlClient.SqlException: Invalid column name ‘RptLiteralsLAPAcct’.
Invalid column name ‘RptLiteralsLAPTot’.
Invalid column name ‘RptLiteralsLContPer’.
Invalid column name ‘RptLiteralsLCurrency’.
Invalid column name ‘RptLiteralsLCurTot’.
Invalid column name ‘RptLiteralsLDate’.
Invalid column name ‘RptLiteralsLDueDate’.
Invalid column name ‘RptLiteralsLExtCompany’.
Invalid column name ‘RptLiteralsLExtCompTot’.
Invalid column name ‘RptLiteralsLInvDate’.
Invalid column name ‘RptLiteralsLInvoice’.
Invalid column name ‘RptLiteralsLPageNum’.
Invalid column name ‘RptLiteralsLPhone’.
Invalid column name ‘RptLiteralsLPurOrd’.
Invalid column name ‘RptLiteralsLReportTitle’.
Invalid column name ‘RptLiteralsLRptTotal’.
Invalid column name ‘RptLiteralsLVendor’.
Invalid column name ‘RptLiteralsLVendTot’.
Invalid column name ‘RptLiteralsLPrePayment’.
Invalid column name ‘RptLiteralsLRefDate’.
Invalid column name ‘RptLiteralsLContractRef’.
Invalid column name ‘RptLiteralsLRefPO’.
Invalid column name ‘RptLiteralsLTime’.
Invalid column name ‘RptLiteralsLEndOfReport’.

image
image1035×246 7.19 KB

2

So where are you at? Is that error from the base/unmodified report?

Or have you copied the report and modified the query? I don’t see your LegerNumber in the query…

There is also the Report Data Definition program you may need to use to make sure the LegerNumber field is available for SSRS to pull.

3

I have configured the settings according to the picture details. But when printing the document it doesn’t work.

image
image1589×843 94.8 KB

4

Your screenshot shows that you added the field to the APDtls dataset, but the query text you show on your original post seems to relate to the RptLabels dataset (although it’s been amended to incorrectly refer to wrong table for T1.

Your query for RptLabels in the unmodified state should be:

="SELECT T1.Calc_RptUserID,T1.CurrencyCode,T1.RptLiteralsLAPAcct,T1.RptLiteralsLAPTot,T1.RptLiteralsLContPer,T1.RptLiteralsLCurrency,T1.RptLiteralsLCurTot,T1.RptLiteralsLDate,T1.RptLiteralsLDueDate,T1.RptLiteralsLExtCompany,T1.RptLiteralsLExtCompTot,T1.RptLiteralsLInvDate,T1.RptLiteralsLInvoice,T1.RptLiteralsLPageNum,T1.RptLiteralsLPhone,T1.RptLiteralsLPurOrd,T1.RptLiteralsLReportTitle,T1.RptLiteralsLRptTotal,T1.RptLiteralsLVendor,T1.RptLiteralsLVendTot,T1.RptLiteralsLPrePayment,T1.RptLiteralsLRefDate,T1.RptLiteralsLContractRef,T1.RptLiteralsLRefPO, T1.RptLiteralsLTime,T1.RptLiteralsLEndOfReport
 FROM RptLabels_" + Parameters!TableGuid.Value + " T1"

The query you need for the APDtls dataset is:

="SELECT T1.CurrencyCode,T1.DebitMemo,convert(nvarchar, T1.InvoiceDate, 111) as InvoiceDate,T1.InvoiceNum,T1.Calc_AgeBaseAll,T1.Calc_AgeCurAll,T1.Calc_AgeInvAmt1,T1.Calc_AgeInvAmt2,T1.Calc_AgeInvAmt3,
T1.Calc_AgeInvAmt4,T1.Calc_AgeInvAmt5,T1.Calc_AgeInvAmt6,T1.Calc_AgeLbl1,T1.Calc_AgeLbl2,T1.Calc_AgeLbl3,T1.Calc_AgeLbl4,T1.Calc_AgeLbl5,T1.Calc_AgeLbl6,T1.Calc_curDesc,T1.Calc_CurDueDate,T1.Calc_PONumList,
T1.Calc_PrimCont,T1.Calc_PrimContPh,T1.Calc_RptAPAcctID,T1.Calc_RptExtCompany,T1.Calc_RptTitle2,T1.Calc_RptUserID,T1.Calc_VendName,T1.Calc_VendorID,CAST( T1.Calc_RefPONum as nvarchar ) as Calc_RefPONum,
T1.Calc_ContractRef,T1.Calc_ContractRefDate,T1.PrePayment,T1.Calc_PaymentHold,T1.APPromNoteID, T2.LegalNumber
 FROM APDtls_" + Parameters!TableGuid.Value + " T1 LEFT JOIN InvcHead_" + Parameters!TableGuid.Value + " T2 ON T1.InvoiceNum = T2.InvoiceNum "
5

I have entered the values in Table APDtls and added Table InvcHead where I have entered the code you provided and the data is not displayed.

69
691587×808 95 KB

thank you

6

It’s not displayed, but you’ve got no error which is good.

I notice you don’t have any data in the report - as the new field LegalNumber is connected via the InvoiceNum, if the InvoiceNum is blank then the LegalNumber will also be blank.

Not sure what those 2 lines in the grid are - but don’t look like full data rows.

7

I tried the BAQ test and the data showed normal. I’m not sure how to set it up. that makes information appear

70
70734×510 25.3 KB