SSRS Report Parameter Post Back

Here is a oldie but a goodie.

I’ve created a capacity trending and forecasting report, when I uploaded the report to the SSRS web console, the report tool for ever to load and everytime I clicked on the report parameters a post back is occuring slowing down the report. One of the report parameters contains 6000+ server names. Report parameter postback normally happens when there is a dependacy of one parameter on another and when the return set from the post back is quite big the user-experience of the user opening the report is really crappy.

My solution after struggling for a couple days to find out what’s happening is to create “dummy” sql statements and contain these statements in a data set. The parameter default and available values is then the contents of these datasets

Doing this drastically improved the report performance and prevented post backs in totallity.

What I did:

One of the report parameters was the days of forecasting for the report

 Image

I’ve then created a data set called “otherdefaults”

Image

The sql query for this dataset looks like this

 Image

As you can see the defalt value for the forecasting to be performed by the report is 10 days, there is other information in the query as well for the other parameters in the report.

The parameter details are as follows

Image

As you can see the defaultdays value in the sql query is the available value for the parameter.

By creating and using datasets for your report parameters you can drastically improve responsiveness and performance.

Output of the report

 

Capture

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s