Pro SQL Server 2012 Reporting Services

CHAPTER 6  BUILDING REPORTS

Figure 6-27. No parameter values available via drop-down list

The following scripts can be used to add two datasets to populate the Branch and Employee drop-
down lists for the parameters:

1. In the Report Data window, create two new datasets embedded in the report,
   Employee_DS and Branch_DS, by right-clicking the Datasets folder and
   selecting New Dataset. For both datasets you create, you will add simple
   queries that will return the IDs (for the Value attribute) and names (for the
   Label attribute) for the employee and the branch. Notice in the WHERE clause
   of the employee query that follows that you are including only a known set of
   employees for simplicity. In a real world scenario, business rules dictate filters,
   but you probably wouldn’t hard code your values in this fashion. We are just
   showing one way that you could hard code a filter to return a subset of data to
   be used in our parameter drop-down list.

--Query for Employee Parameter
SELECT
EmployeeTblID
, RTRIM(RTRIM(E.LastName) + ', ' + RTRIM(E.FirstName)) as Employee_Name
FROM
Employee E
WHERE
(E.EmployeeTblID IN (32, 15, 34, 44, 129, 146, 159, 155, 26))

--Query for Branch Parameter
SELECT
BranchID, BranchName
FROM
Branch
UNION
SELECT NULL AS BranchID, NULL AS BranchName