CODEWITHSUNDEEP
reporting-servicesdatasetssrs-tablixsubreport
Ngoc Truong Duc
Ngoc Truong Duc

Reputation: 21

SSRS Subreport's dataset run multiple times

I have a subreport which use a parameter from main report to filter the data. The subreport's dataset is the same but SSRS keep making call to SQL to get it for every parent in main report.

I already set the cache for all dataset and set the parameter as not used by query. Is there anyway to set the subreport's dataset run only one and then filter the data in subreport using the parameter?

Here is an example of my query

Main Report: Select ClassID, ClassName from Class (Dataset 1)

Sub Report: Select ClassID, StudentName from Student (Dataset 2)

Main report will pass ClassID as parameter to Sub report. Sub report will use it as a filter for Dataset 2 and return StudentName with same ClassID in subreport

My issue is if Dataset 1 return 8 rows, then the Subreport will run Dataset 2 8 times.

Upvotes: 2

Views: 888

Answers (2)

StevenWhite
StevenWhite

Reputation: 6024

If you use a dataset filter instead of a report parameter, it will be able to take advantage of the report cache.

Upvotes: 0

Polymar
Polymar

Reputation: 31

Have you tried using a separated dataset? I had a similar problem once and it turned out being some kind of loop.

Upvotes: 0

Related Questions