CODEWITHSUNDEEP
reporting-servicesparametersssrs-2008
Andrew
Andrew

Reputation: 31

SSRS: ASP.NET ReportViewer parameters reset to default when clicking View Report

I have a SQL Reporting Services report (SQL 2008 built using Report Builder v2.0) which has a multi select integer parameter (in this case a list of stores).

The default value is 0 ("All Stores"). The parameter is passed to a stored procedure as a varchar(1024).

This all works fine in Report Builder or from the Reporting Services website.

However from an ASP.NET website using the ReportViewer component the parameters are reset to the default each time you choose "View Report".

I've tried setting the view to non-asynchronous loading (changes the rendering container?) plus any other setting I could find, without success. Does anyone know a trick to fix this?

Upvotes: 1

Views: 9805

Answers (3)

Andrew
Andrew

Reputation: 31

I made a mistake. The ReportViewer control was inside a Master Page. Each time the "View Report" button was clicked it did a postback and reset the session.

I added if (!IsPostBack) to before the report setup method.

Upvotes: 2

todd
todd

Reputation:

I found a similar code error as the original poster. I was (re)setting the report server credentials every time the page was loaded, instead of just when it was initialized. Apparently, setting the report credentials resets all of the parameters.

Upvotes: 1

kreadus
kreadus

Reputation:

For those of you who experience this error for reasons not listed by the original poster...

Here is a similar scenario.

You have a report which uses a multi-select report parameter. This parameter is populated by a datasource, which bases its values off some table query.

When you use the 'Select All' option for this report parameter and press the 'View Report' button, the parameter is reset to blank and your report is not generated.

I believe this problem occurs because something invalidates your dataset while the query is being performed, but I cannot be 100% certain.

What I do know, is that by using a table variable as the dataset's query source, you make this problem go away.

For example, your CityDataSource could be populated by the query:

SELECT DISTINCT city, city as SortOrder from accounts
UNION
SELECT 'All' as city, '0' as SortOrder
ORDER BY SortOrder

If you've been making SQL Reporting Services reports, you may have stumbled upon this solution once or twice.

Now, we change the CityDataSource query to look like the following:

DECLARE @citytable TABLE (city varchar(255), sortorder varchar(255))
INSERT INTO @citytable (city, sortorder) VALUES
(
SELECT DISTINCT city, city as SortOrder from accounts
)
SELECT city, sortorder FROM @citytable ORDER BY sortorder

And by doing that your report's parameters won't reset anymore.

A stored procedure would also work, I suspect.

Upvotes: 1

Related Questions