SSRS: Parameter Data List from Database Query

Next topic is about how to get parameter data list from database query. User will be able to choose an item of list that retrieved from database. Still using this report, will create list for year (tahun)  and city (kota).

Each parameter would need a dataset for retrieving data. To add a dataset view Data tab on the rdl file, drop down list combo of Dataset an choose< new dataset... >

Query for Year/Tahun:
SELECT DISTINCT '%' AS YearID, 'All' AS YearDisplay
UNION ALL
SELECT DISTINCT [Year] AS YearID, [Year] AS YearDisplay FROM Student

Set a name for dataset. Just like when we made data source for the report table. We also can choose command type. Text for SELECT query or using Stored Procedure. Finally click OK.

Also set for city/kota parameter, using this query:
SELECT DISTINCT '%' AS CityID, 'All' AS CityDisplay
UNION ALL
SELECT DISTINCT City AS CityID, City AS CityDisplay FROM Student


We'll have 2 new dataset in Data source list: Year and City.

To view content of dataset, choose dataset to view then click run icon/exclamation mark (!)

Next, we'll set parameter list. Go to menu, Report -> Report Parameter.

Setting parameter "Available Values" as image below.

Click tab Preview to see the result.

Click here if you like this article.


Related Article:

1. Installing Business Intelligence SQL Server 2005
2. Creating Report with SQL Server Reporting (Wizard)
3. Creating Report with SQL Server Reporting (without wizard) | Part 1
4. Creating Report with SQL Server Reporting (without wizard) | Part 2
5. Design Layout Report
6. Creating Parameter for Filtering
7. Stored Procedure as Report Data Source
8. Parameter Data List from Database Query
9. Creating Report Group
10. Create Sequence Number Using RowNumber Function
11. Uploading Report FIle (.rdl) to Report Server
12. Remove Report Page Break/Page Break 
13. Query to Generate Subscription List 
14. Direct Printing to Printer