SSRS SubReport error "Data retrieval failed for subreport"

Epicor ERP 10
1

After tweaking a Report Data Definition, I’m getting the following error for all subreports

"Data retrieval failed for subreport, ..."

I restored the RDD to the way it was, but continue to get the error.

I opened one of the subreports in SQL Server Report Builder to see if it would run by itself.
It would not. The error the subreport generates is

"Incorrect syntax near the keyword 'LEFT'"

So digging into the dataset queries yielded the following:

FROM InvcHead_d0d9250d2202486aafcca28c5c38c0b8 T1 
LEFT OUTER JOIN InvcTax_d0d9250d2202486aafcca28c5c38c0b8 T2 ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum 
LEFT OUTER JOIN RptLabels_d0d9250d2202486aafcca28c5c38c0b8 T3 ON T1.RptLanguageID = T3.RptLanguageID WHERE T1.Company = 'MC' AND T1.InvoiceNum = '58445'  
LEFT OUTER JOIN BankAcct_d0d9250d2202486aafcca28c5c38c0b8 T4 ON  T1.Company = T4.Company AND T1.OurBank = T4.BankAcctID AND T1.InvoiceNum = T4.Calc_InvoiceNum

I think the problem is that the WHERE clause is before the last LEFT OUTER JOIN

Moving it to after the last Left Outer Join, makes it run okay.

Also, the Query had a non-exiting field in the SELECT phrase. It had “T1.Calc_VoucherString” and “T1.Calc_Voucher_String”. I had to delete the one with the underscore between “Voucher” and “String” to get it to finally run.

How could these two thing happen?

2

I’m going to chalk this all up to the unpredictable (and often bad) results of using the “Synch Dataset” function in Report Style Maintenance. I’ve only ever had the Synch Dataset work for BAQ reports. It’s especially problematic when the report started out with a Base Definition RDD

I ended up going into each subreport RDL and editing the dataset queries - moving the “WHERE …” clause to after the last “LEFT JOIN …”.

And had to remove the “…Calc_Voucher_String” from the SELECT clause.

Looking at the original ARForm RDD, it shows there are two calculated fields for VoucherString

image.png775×326 9.66 KB

One with a “Calc_VoucherString” and the other with “Calc_Voucher-String” (note that’s a dash and not an underscore in the second).

Yet the subreport queries specified “Calc_Voucher_String” in their select clauses.

Go figure…

1 Like
3

Thanks for sharing this Calvin!
I ran across this same exact thing in 10.2.200.10 with the POForm.
However it was a custom RDD and Report Style from a prior version of 10… so I wonder if it’s the Resync function or data that is causing this to get screwed up.
In my case, after performing the Resync two of six subreports were messed up so the data retrieval error appeared. I had backups of the subreports before the tweaks I made and replacing them fixed my issue.
Thanks again for sharing!

2 Likes
4

A reporting consultant from Epicor told me never to touch the Sync Dataset button, and they proved to be wise words. As I have run into similar issues in the past. These RDD’s seem so fragile at times. I have a request out there to modify a report with one of those black box type RDD’s, and I’m scared.

1 Like
5

I’ve often debated customizing the the Report Style form to make the Sync button really or small, mostly covered by a warning box.

1 Like