Tuesday, April 14, 2009

Multi-Selection Parameter Reset to Blank

I encountered a situation where a multi-selection parameter is reset to blank sometimes by the Reporting Services.

Here is the reason that the Feature parameter was reset to blank or giving out error message:

Somehow the Reporting Service re-evaluates and invalidates the parameter values when a parameter is dynamically generated based on other parameter. In our case, Feature list is dynamically generated based on Primary Workstream selection. The work around is to use a table variable instead of straight SQL query to generate the dataset that populates the parameter values. For some reason the Reporting Services doesn’t re-evaluate the values if a table variable is used.

After some digging, I found a work around:
http://stackoverflow.com/questions/684250/ssrs-asp-net-reportviewer-parameters-reset-to-default-when-clicking-view-report

The following query is used:

DECLARE @FeatureList TABLE (Feature NVARCHAR(256))
INSERT @FeatureList

SELECT DISTINCT Cobalt_Common_Feature AS Feature FROM dbo.[Work Item]
WHERE Cobalt_Common_Feature IS NOT NULL
AND (@PrimaryWorkStream='_All Workstreams' OR Cobalt_Common_PrimaryWorkstream=@PrimaryWorkStream)
UNION SELECT 'ZZZ Unknown' AS Feature

SELECT * FROM @FeatureList ORDER BY Feature

No comments: