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

Question

Hi,

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 varchar(1024).

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

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

I've tried setting the view to None Async loading (changes the rendering container?) plus any other setting i could find?

Any tick that anyone knows to fix this?

Answer 1

Hi all,

I made a stupid mistake...so obvious (but sometime we miss the obvious). The Report Viewer 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.

How stupid do i feel.

Answer 2

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.

Answer 3

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.