SSRS Issue : SSRS 2008 R2 - Default Multi Value Parameters are Not Selected when there is NULL in the Datasets result
Problem :
I've been running into this issue quite a few times, and have been unable to solve it through reading various posts/forums. Here is the issue
Software Details:
Datawarehouse Database - SQL Server 2008 R2
Reporting Services Version - SSRS 2008 R2
Development Environment - Visual Studio 2008
Problem Details:
I have been writing reports based of a Relational Datamart with dimensions and facts. The report in question uses dimensions for parameters and facts for content. Multi valued parameters are enabled in the report query by using the 'IN(@Paramenter)' statement within the report query. It has a total of 6 multi-value parameters. I assign the same available values from dimension datasets for each parameter to it's default parameter. Theoretically all the values in the option lists should be selected after first render. This is not always the case.
Example:
SELECT *
FROM dbo.MyTable
WHERE
TableColumn0 IN (@Parameter0)
AND
TableColumn1 IN(@Parameter1)
AND ....N (continue syntax to 6 multi valued parameters)
Some of the multi-value drop down lists are defaulting to have every value selected, and some are not . The problem is I need to know why some parameters are not selecting all by default when specified.
Solution :
I was experiencing the same thing as well and tried to find the answer here unsuccessfully. Default values for some multi-value dropdowns are selected and some are not. It was just really weird and annoying as there are no error/warning messages from the compiler and everything seems ok except for the fact that the default values will not be selected for some of the dropdown parameters.
After some fiddling around, i found out the reason for this behavior. If the default values return a NULL as part of its dataset, then it will not work (the multi-value dropdowns do not allow NULLs to be selected anyway). You can include a WHERE clause to remove the NULLs from the query and the default values will be selected when the report is run.
I've been running into this issue quite a few times, and have been unable to solve it through reading various posts/forums. Here is the issue
Software Details:
Datawarehouse Database - SQL Server 2008 R2
Reporting Services Version - SSRS 2008 R2
Development Environment - Visual Studio 2008
Problem Details:
I have been writing reports based of a Relational Datamart with dimensions and facts. The report in question uses dimensions for parameters and facts for content. Multi valued parameters are enabled in the report query by using the 'IN(@Paramenter)' statement within the report query. It has a total of 6 multi-value parameters. I assign the same available values from dimension datasets for each parameter to it's default parameter. Theoretically all the values in the option lists should be selected after first render. This is not always the case.
Example:
SELECT *
FROM dbo.MyTable
WHERE
TableColumn0 IN (@Parameter0)
AND
TableColumn1 IN(@Parameter1)
AND ....N (continue syntax to 6 multi valued parameters)
Some of the multi-value drop down lists are defaulting to have every value selected, and some are not . The problem is I need to know why some parameters are not selecting all by default when specified.
Solution :
I was experiencing the same thing as well and tried to find the answer here unsuccessfully. Default values for some multi-value dropdowns are selected and some are not. It was just really weird and annoying as there are no error/warning messages from the compiler and everything seems ok except for the fact that the default values will not be selected for some of the dropdown parameters.
After some fiddling around, i found out the reason for this behavior. If the default values return a NULL as part of its dataset, then it will not work (the multi-value dropdowns do not allow NULLs to be selected anyway). You can include a WHERE clause to remove the NULLs from the query and the default values will be selected when the report is run.
Comments
Post a Comment