Hiding Sub report in SSRS (and not executing the Stored Procedure)

Question

I can set the hidden property using an expression which will hide/show the sub-report, but what I found using SQL Profiler, is that the sub-report stored procedure is still executed, even if the sub-report is hidden.

Is anybody aware of a way to avoid this, other than changing the stored procedure itself. If not, does anybody know the reason why the stored procedure is executed even thought the sub-report is hidden?

This is same question as This one but no answer there so i am asking again any help will be great

See my answer on [this question](http://stackoverflow.com/questions/18247840/control-executing-of-hidden-subreports-inside-tablix-ssrs) — StevenWhite, Aug 15 '13 at 17:26

score 6 · Answer 1 · answered Jul 18 '14 at 16:01

I have a large complex report (up to 10k controlling rows) where there are 1:n rows in 7 subreports. This runs like a dog unless you can suppress the SQL execution in the subreports (since most of the subreports are hidden most of the time).

After much pain, I have found that it is really easy to suppress the subreport rendering and dataset execution. All you need is a required report parameter in the subreport. There is usually an ID field passed to a subreport, so this should be no problem.

In the main report, the parameter is passed by an expression similar to:

=Iif(Fields!SubReportXisHidden.Value,Nothing,Fields!ID.Value)

The subreport will barf on the missing parameter, will not render and will not execute the SQL.

Deep joy!

score 0 · Answer 2 · answered Aug 14 '13 at 15:57

Just do that logic in SQL Management Studio. SSRS is not an event driven system really and you can do some small things in code blocks and with Visual Basic but not much. It is not meant to program execution plan level coding on complex if then else scenarios for doing at run time execution plans. In fact it gets mad if you try to do advanced looping and indexes as that is meant for the server more than this add on. It is meant to present your data you give to it and some very basic conditioning of elements. If you really want to either do a query and the cost of it or not just add a bit flip to a proc created in SQL Management Studio like:

create procedure dbo.ToCreateOrNotToCreate 
    (
        @input varchar(32)
    ,   @Run bit
    )
as 

BEGIN
    if @Run = 1 
        Select 'You selected to run "' + @input + '" at a cost!'
    else 
        Select 'Don''t run and save performance'
END


exec dbo.ToCreateOrNotToCreate @input = 'My Input', @Run = 1
exec dbo.ToCreateOrNotToCreate @input = 'My Input', @Run = 0

Hiding Sub report in SSRS (and not executing the Stored Procedure)

2 Answers2