CODEWITHSUNDEEP
sql-serverreporting-services
Anthony
Anthony

Reputation: 5226

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

We have some SQL server reporting services reports. I didn't write then but I have to take care of them.

These reports fire when opened in the browser, and with the default parameters (search terms and restrictions are blank) they retrieve a lot of data, which is slow. The client would prefer that the report is not generated until the user enters parameters and presses "view report"

Unfortunately I don't know SSRS at all well - how do I stop the report from firing when it is opened?

The details of how to do it on a deployed report (as per Rihan Meij's answer) is as follows:

Click on a report, click on "properties" at the top. You may have to wait a bit, because the slow report may be running now. Then click on "parameters" on the left.

For each parameter, make sure that "Prompt User" is checked, and for at least one parameter, "Has Default" is not checked. Click on "View" again at the top left (or go back to the folder and click on the report name) to view the report, and note that the report does not fire right away.

In the report builder, you can do this via the "Filter" menu. De-select values from at least one filter, and save the report.

Is it also possible to stop reports from firing on loading when the report has no parameters?

Upvotes: 41

Views: 77372

Answers (8)

Arnt
Arnt

Reputation: 1

I added this in my query:

where 'Start' = @Start

and made a parameter with only available value 'Start'. The report is waiting for input.

Upvotes: -3

Rihan Meij
Rihan Meij

Reputation: 1759

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.

Upvotes: 4

0bytes
0bytes

Reputation: 83

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]

Upvotes: 21

Wayne
Wayne

Reputation: 497

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

Upvotes: 5

Jim W.
Jim W.

Reputation: 35

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.

Upvotes: 5

Teis Lindemark
Teis Lindemark

Reputation: 362

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;

Upvotes: 6

George
George

Reputation: 1

I did this by just supplying a default value to the key parameter that doesn't return any results.

Upvotes: 2

James Prout
James Prout

Reputation: 21

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!

Upvotes: 2

Related Questions