Answer: My First Report

I need to create a report for a phone directory. Basically my report needs this format.

department
employee
employee
and so on.....
department
employee
employee
and so on.....
department
My data is stored in two tables

department -

deptID
deptName

Main -

deptID
empName
title
order by

Departments are listed in department table. there is an associated deptID in both tables. in the main table every employee is listed and they have a deptID that is associated with the department table. The order by column is an integer that list the order they should appear in the format. 1 would be the top person and so on.

How would you bring in the data into reporting services to display in this format? Would this be one SQL Statement or am I looking at multiple Datasets?

I was playing with the Union ALL and realized I don't have equal parts.

I will be pulling the deptid and deptname from the department tables

from the main table I need deptid to match tables, and then I need to display. name, title, and phone. department table is just used to display the department name.

|||Do let me know if the following is what you were looking to accomplish:

I created the following 2 tables (similar to yours I believe):

create table department (deptID int, deptName varchar(100))

create table Main (deptID int, empName varchar(100), title varchar(100))

When designing the report:

Enter the following in the Query String (or the query that you have to select all the required fields from the tables) -
select * from department
join main on main.deptid = department.deptid

Click Next, Choose Tabular Matrix
Click Next, Choose DeptName as 'Group' and EmpName and Title as 'Details'
Click Next, select 'Enable drilldown' if you wanted to expand the deptnames
Enter report name, click Finish and view the Preview.

You will find the report grouped by Dept Name, with empname, title appearing under each dept name.