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
Power BI, Parallelism And Dependencies Between SQL Queries In DirectQuery
Mode
-
This is going to sound strange, but one of the things I like about tuning
Power BI DirectQuery semantic models is that their generally-slower
performance a...
4 days ago
No comments:
Post a Comment