topic Re: Second Level of Sub Report Error In Paginated Report in Developer

Second Level of Sub Report Error In Paginated Report

Anonymous — Wed, 31 Aug 2022 15:30:32 GMT

LookupSet or Lookup functions seems to be breaking a sub report within a sub report so 2 levels deep. Any ideas why? Removing LookupSet function in a field and report starts to work.

Setup

Report 1 has Report 2 as a subreport and Report 2 has a subreport Report 3. The lookup function is on Report 2 and Report 1 does not work. Report 2 works. Removing lookup function from Report 2 causes Report 1 to work.

Thanks

Re: Second Level of Sub Report Error In Paginated Report

Anonymous — Fri, 02 Sep 2022 09:05:42 GMT

Hi @Anonymous ,

Please review the following threads which has the same problem as yours, hope it can help you.

SSRS 2008 R2 - Lookup from subreport

No, the lookup function won't go beyond the scope of the report. Your question isn't very clear on what exactly you're trying to accomplish (which would be helpful) but I'll try to guess.

If you're looking to pass some values to the subreport from the parent's dataset you could check this blog post about doing that, though I haven't tried it myself. You could also use a shared dataset to avoid rewriting your queries if you need to use the dataset again in both of the reports.

According to this msdn social thread it doesn't appear to be possible to pass a value to the parent report from the subreport. However, another thread on sqlservercentral.com seems to have some promise but it didn't seem to work for everyone. Once again, I haven't tried it myself.

Report Builder functions - Lookup function in a paginated report (Report Builder)

Best Regards

Re: Second Level of Sub Report Error In Paginated Report

Anonymous — Fri, 02 Sep 2022 14:11:25 GMT

Hi and thank you for the info. This was not about lookup function going beyond the scope of the report like using a lookup function to lookup data in another subreport.

This is about a subreport that has a lookup function within its own report fails. Here is more info:

Report 1 has Report 2 as a subreport. Report 1 has its own dataset1 and passes parameter to Report 2.

Report 2 has a subreport Report 3. Report 2 has dataset2a and dataset2b. Report 2 has a lookup function in a table using dataset2a lookup on dataset2b. Report 2 passes parameter to Report 3.

Report 3 has one dataset3 and no lookup functions and is the last report in the chain.

Report 2 loads fine and you can see Report 3. Report 1 does not work. Removing lookup function in Report 2 fixes the issue.

Is this more clear?