MAPPED

1. People and departments query

Select c.firstname, c.lastname, e.title, d.departmentID, p.Name as DeptName
From HumanResources.EmployeeDepartmentHistory D INNER JOIN
HumanResources.Employee E ON D.EmployeeID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
HumanResources.Department P ON P.departmentid = d.departmentid
ORDER BY DeptName, c.LastName

LINKED

1. Employee Query for linked report demo

Select c.firstname, c.lastname, e.title, d.departmentID
From HumanResources.EmployeeDepartmentHistory D INNER JOIN
HumanResources.Employee E ON D.EmployeeID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
Where D.DepartmentID=@dept
Order By c.lastname

2. Dept query for linked report demo

SELECT   DepartmentID, Name
FROM     HumanResources.Department
ORDER BY Name

SUBREPORT

1. Child query

SELECT   DepartmentID, Name
FROM     HumanResources.Department
WHERE DepartmentID = @mydeptid
ORDER BY Name


2. Parent query

SELECT   DepartmentID, Name
FROM     HumanResources.Department
ORDER BY Name

DYNAMIC

1. Employee Query for dynamic sql demo

Select c.firstname, c.lastname, e.title, d.departmentID
From HumanResources.EmployeeDepartmentHistory D INNER JOIN
HumanResources.Employee E ON D.EmployeeID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
Where D.DepartmentID=1
Order By c.lastname

2. Department Query for dynamic sql demo

SELECT   0 AS DepartmentID, 'All' AS Name
UNION
SELECT   DepartmentID, Name
FROM     HumanResources.Department
ORDER BY Name

3. Green Bar format

=iif(RowNumber(Nothing) Mod 2,"Green","White")

4. Dynamic version of Employees query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &
"From HumanResources.EmployeeDepartmentHistory D " &
"INNER JOIN HumanResources.Employee E " &
"ON D.EmployeeID = E.EmployeeID " &
"INNER JOIN Person.Contact C " &
"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "",  "WHERE D.DepartmentID = " & Parameters!Department.Value) & 
"ORDER BY C.LastName"

5. Text for text box

="Employees for Department: " & Parameters!Department.Label


DATA DRIVEN SUB

1. Connection string for subscribers
data source=gsnowmantosh; initial catalog=Subscribers

2. Query for subscriptions
select * from subscriber


