SQL server reporting services: how to stop a report firing when opened

Question

I found that I had to set at least one of the report parameters to not have a default to keep the report from autorunning.

I had to use this configuration (notice that all 3 of the parameters I left without defaults accept Nulls so the users can just click the Null checkboxes):

[screenshots missing]

to get the users to see this and to keep the report from autorunning:

[screenshots missing]

I set the default value of one of my parameter selections to a value of -1 which does not exist (not a valid parameter value). This did not generate an error. It simply set the parameter drop down box to and prevented the report from running. I couldn't use a default value of NULL because NULL selects everything, and I wanted the user to purposefully make the selection of ALL (NULL) before the report runs. It takes several minutes for the report to render if ALL is selected.

I have done that by changing my query slighlty to require parameters when it is run.

I have then after I have published the report on the report site, specified that the parameter, should prompt the user. This does have the effect that the report does not pull the sql server to its knees when users just open the report to see.

I know this post is a bit old, but since I have another solution here to, I just posting it in case someone need it.

If you are using ReportViewer its posible to set the property ShowBody="False". Then on the OnSubmittingParameterValues event, change the ShowBody property to true. Then you do not need any extra parameters or parameters without default value in the report.

<rsweb:ReportViewer 
        ID="rv" 
        runat="server"  
        Width="100%" 
        Height="100%" 
        SizeToReportContent="false" 
        ZoomMode="PageWidth"
        KeepSessionAlive="true" 
        ProcessingMode="Remote"
        PromptAreaCollapsed="false" 
        InteractivityPostBackMode="AlwaysAsynchronous"
        AsyncRendering="true" 
        ExportContentDisposition="AlwaysInline"
        ShowReportBody="False"
        ShowPrintButton="false"
        OnSubmittingParameterValues="rv_SubmittingParameterValues"/>

And then in the rv_SubmittingParameterValues method:

this.rv.ShowReportBody = true;

I found a nice trick for this when working with reports that have optional fields, but pull a huge amount of data if the optional fields are blank.

Step 1: Prevent Auto Firing

• Make sure "Allow null value" is not enabled for the optional parameters
• Make sure there is no default value for the optional parameters

Step 2: Make parameters optional without using 'null'

• Enable "Allow blank value" for the optional parameters
• Modify your where clause for the optional parameters to WHERE (@param="" OR column = @param)

With this method there is are extra fields for the end users to worry about, the report will not fire until it is requested, and the conditions in the where clause will not be evaluated if the text box is left empty.

---

note: if the report specifies Available Values then any value that is not valid for your table structure may be used instead of "", you can also use with other data types (non-string) this way

I found the best way was to add in a parameter that does not allow nulls, but is not used by the report. This stops it from rendering at the start, but does not affect the report.

The only down side is if you are displaying the report in a report viewer you have a box at the top which looks a bit strange. I'm sure you could use some C#/CSS to hide it though.

As I'm not using a report viewer to display, only to render at the back end this doesn't affect me.

Even if you are using a report viewer it's helpful when developing!