Epicor SSRS Error

Epicor ERP 10
1

I received this error when I am trying to run a select statement in a text box in SSRS. So, basically, I am trying to return a field which is not in the given dataset. So I have run a subquery which returns the value using the existing dataset as a container.

But I don’t know how to represent that return value in the Expression field. Because the expression field returns a value which is within the dataset provided.

I have provided the SQL that I have written.

Any suggestions would be much appreciated.

My intention is to return the “CommentText” value. However, the dataset does not contain any commenttext field, but an EmpID field. So I have created the subquery below that brings up the CommentText from LaborDtlComment table and when it matches with the EmpID in the report dataset it returns the CommentText Value.

<select

[CommentReturnQuery].[LaborDtlComment_CommentText] as

[LaborDtlComment_CommentText],

[EmpBasic].[EmpID] as [EmpBasic_EmpID]

from Erp.EmpBasic as EmpBasic

inner join (select

[LaborDtlComment].[CommentText] as [LaborDtlComment_CommentText],

[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum]

from Erp.LaborDtlComment as LaborDtlComment

inner join Erp.LaborDtl as LaborDtl on

LaborDtlComment.Company = LaborDtl.Company

and LaborDtlComment.LaborHedSeq = LaborDtl.LaborHedSeq

and LaborDtlComment.LaborDtlSeq = LaborDtl.LaborDtlSeq)  as

CommentReturnQuery on

EmpBasic.EmpID = CommentReturnQuery.LaborDtl_EmployeeNum>

My aim is to show the CommentText value in a text field. So I will create the

text field and it will contain the SQL that I have written. Can anyone help

me in this case?

2

Sounds like you would want to update your Report Data Definition to include this data and write either a sub report, an additional dataset, or a better select statement.

1 Like
3

Hi Jason
Thanks for your comment. I have added the subquery in the dataset SQL embedded in the report as I don’t have any option (permission) to add a new set. However it is not returning any value atm. I believe I have written the query properly. There’s something I am missing.

My subquery returns the PersonID which is returned by the SELECT statement. I have updated the report definition by uploading new report and have RUN it

Do you know if I remove the ‘=’ and “quote” marks from the SQL in the dataset, will it work underneath the report?

4

Did you add it into the query for your dataset or did you create a new data set and paste your query into the query editor? What report are you editing?

5

My advice for modifying Epicor SSRS dataset queries is to edit the query directly in the RDL (XML) file. Use a good Text Editor (like TextPad) so you know where the single quotes and double quotes are exactly. I have found this is easier than trying to modify the query within the GUI designer.

6

Hi Thomas, Thanks for your suggestions. Just to be sure, If one of the tables is not in the dataset, then I can add them via report data definition and then sync the data definition with the reporting style?