How to control SSRS subreport based on report parameter value

Question

I have an main report which are having 4 sub reports.

wanted to know how to control the subreport based on Main report parameter value? i mean execution of the sub report shoulbe done based on main report paramter value.

There is a report parameter called "Region" based on this parameter value those sub reports has to be executed.

For example: "MiddleEast" is the Region value

based on this value, only MiddleEast subreport has to be executed. Remaining reports should not executed. Can you please suggest a way on this? Appreciate your help.

Thanks & Regards,

Anil

Should the region parameter change which subreport is shown? Or change all 4 subreports to show data for the selected region? — StevenWhite

Alan Schofield Alan Schofield · Accepted Answer · 2017-03-09T18:51:11

Set the visibility property of the the subreports based on the parameter so only one is visible.

The following is not required but you should consider it if your subreports are not very quick.

Hiding the subreports will not stop them executing which is a waste of time and resource so, to stop the other hidden subreports from executing pass a parameter to the subreports that you can pass to the dataset.

So, in you subreport dataset the SQL would be something like

IF @canRun = 1
   BEGIN
      SELECT .....  -- you existing query here
   END
ELSE
   BEGIN
      SELECT NULL, NULL .... --- query to return zero records
   END

How to control SSRS subreport based on report parameter value

1 Answers