SSRS - Unchecked some values by Default from Multiple Value Parameter

SSRS - Unchecked some values by Default from Multiple Value Parameter.

There was one requirement in SSRS that Client wants to select some values in multiple value parameter by default but they need the functionality to see all values in same parameter. For example please have look in below screen shot..

here client wants to unchecked Afghanistan and Albania (marked in Red) by default but they want to see these values in parameter so that in future they can run report for Afghanistan and Albania by selecting these values. For these such type of functionality I tried a scenario as below:

Step-1
Make connection with AdventureWorks Database..

Step-2
Add Dataset1 in SSRS with SQL - select * from Person.CountryRegion.

Step-3
Add Dataset2 in SSRS with SQL - select * from Person.CountryRegion
where CountryRegionCode not in ('AF','AL','AQ') -- it will filter some countries like Afghanistan, Albania.

Step-4
Add Parameter "Counrty" select allow multiple values:

Step-5
Go to Available Values and choose Dataset1 for values from Query:

Step-6
Go to Default Values and Choose DATASET2 for values from Query:

Click OK..

Run the Report and you will get parameter as below:

Thanks .. 

Instant SQL Formatter

Instant SQL Formatter

Below is the link of Instant SQL Formatter 

We can Format our SQL Code or we can download this too.

http://www.dpriver.com/pp/sqlformat.htm

SQL SERVER – Query to Find Column From All Tables of Database

Below script will return all the tables containing specific column along with their schema name.

USE Master

GO

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%NAME%'

ORDER BY schema_name, table_name