10.2 SSRS Report Builder Custom Standard Report "Adding table to a data set expression not in RDD"

(tyler fenwick) #1

Has anyone had trouble adding a new table to there data set expression after upgrading to 10.2? In the past on 10.1.600 I used to be able to call tables in the data set via below. But I receive an error now.
*Report server has not changed since upgrade. Epicor and SSRS live on the same server

Data set expression table addition:
FROM MtlQueue_" + Parameters!TableGuid.Value + " T1
ON T1.JobNum = T2.JobNum"

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: System.Web.Services.Protocols.SoapException: 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 ‘MtlQueue’. —> System.Data.SqlClient.SqlException: Invalid object name ‘RASE10PROD.Erp.JobOper’.

(tyler fenwick) #2

Actually if I was paying attention and not calling the SQL instance instead of the database, it might actually work haha.

(Lee Burns) #3

I was looking in my system for something to try and help answer your question when I discovered a major issue on my end. Basically, my ReportServer isn’t purging tables (any, ever), and I have 72 GB of Report Server databases and over 40,000 records of stale reports that have run since we went Live in July of last year.

So, while I wasn’t able to help you with your question, I wanted to say you inadvertently helped me uncover a major problem that I need to deal with–and one that is likely a good reason why our Reporting is so slow. So, thanks! lol

(tyler fenwick) #4

@LeeB I believe in version 600 and up they started clearing report tables automatically unless you choose to archive when printing. I found out yesterday that they were clearing tables because if you try to test a SSRS report with a guid from system monitor it fails saying rpt parameter cant be found. Basically the temp report table with the long guid is removed. If you need to test a report with a successfully printed report with its guid you can choose upon printing the report to set the archive at a day instead of zero.

(Lee Burns) #5

That’s what I was reading in another thread on e10help… But I’m on 10.1.600.5, and have been since it was the new release, you’d think it would apply. I only ever choose Archive when I need to preserve a GUID for Report Building. I’m about to upgrade to 10.1.600.20 this weekend, maybe it will start clearing itself. If not, maybe Support will have a solution (for once), or i may have to explore some SQL options. Thanks!

(Shawn Hobdy) #6

Hey @LeeB,

Where do you go to see if it is clearing out?

Our reports are pretty slow and I was wondering if this might have something to do with it.

I am currently running 10.1.500.23 BTW.

Boss is looking to upgrade to 10.2 .100.9 soon.



(Lee Burns) #7

SSMS crashes if I try to browse the full list of EpicorLive10Reports tables. There’s just too many, so I found this query:

SELECT * FROM [EpicorLive10].[Ice].[SysRptLst]

Referenced Here: