Any one help me out!

Hi

I have a requirement in SSRS where in i need to generate a report in which initially there will be one record (say, a manager of a department and some values corresponding to him ) now i need to provide a drill-down on this employee(mamager) such that the next level of employees reporting to him should be displayed with all the parameters and this has to continue till the leaf level employee.

Thanks in advance.

will the number of levels be known in advance or is it parent-child?

RS is not very good at dealing with an arbitary number of grouping levels.

One way to do it would be define a report with a manager parameter that renders a table of subordiantes. Then in a master report, nest the subreport in a table for all expected levels passing the employee id to the subreport. Problem with that is that it wouldn't export very well.

The other ooption would be to PIVOT the parent-child table i.e. flatten it out into columns per each level, then build your table based on that.

Either way you'll need to know the number of levels in advance.

|||

Yes the number of levels will be known in advance.

i'm a novice in SSRS and does not much about it so can you throw some light on what you have just said.

|||

Please tell me the structure of the data and we'll take it from there.

Is it SQL or OLAP? A table or a dimension? what are the columns or levels?

|||

Hi adam,

I'll get an employee id as a parameter to the stored procedure and then i'll get a dataset which will contain all the data for me for all the employees.

Then from this data set i need to generate the drill down report as

(the root level employee)

(then his subordinates)

(then for each employee in the above level we need to get the lower levels here.)

............................................

And this hierarchy goes on till the last employee.

Thanks in advance.|||

ok, I'll ask the question again.

In format does the data come back? What are the columns?

is it employee_id, manager_id (where manager _id is the employee_id of the manager)

OR

is it employee_level_1_id, employee_level_2_id, employee_level_3_id,......

The latter is what you want to end up with to structure your report.

|||

Hi adam,

The data is in the following format

EmployeeID ManagerID Level(the level of the employee)

Thanks in advance.

|||

OK what about the top level manager, is his manager_id = employee_id or is it NULL?

Does the Level column representative of the number of levels in the hierarchy? In it numeric or a textual descriptor?

I assume you also have employee_name an well as some other employee information.

Can you post a little bit of dummy data?

|||

hi

top level managertop level manager is NULL and you will have columns like

EMP_NO , EMP_NAME , MGR_NO , LEVEL(int) , and some other fields............................

12345 xyz 32141 1

level ranges from 1-some known value

|||

The following is a code example of my implementation. Copy the xml into a file and save as .RDL.

You may need to modify the connection as locally I have SQL 2005 installed as a named instance.

The code flattens a table by performing a join onto itself for every level in the hierarchy. I'll try to come up with a neater solution because it feels like I should be able to use the new PIVOT functionality of SQL.

[code]

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.\sql2005;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>008a7b74-651a-4e19-ad0d-a1af34c884ef</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<DataSetName>dst_employee</DataSetName>
<Top>0.91429cm</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_level">
<rd:DefaultName>l1_employee_level</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_id">
<rd:DefaultName>l1_employee_id</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_name">
<rd:DefaultName>l1_employee_name</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l1_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l1_employee_level">
<GroupExpressions>
<GroupExpression>=Fields!l1_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l1_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_level">
<rd:DefaultName>l2_employee_level</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SlateGray</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_id">
<rd:DefaultName>l2_employee_id</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_name">
<rd:DefaultName>l2_employee_name</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l2_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l2_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l2_employee_id.Value, CStr(Fields!l2_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l2_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l2_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_level">
<rd:DefaultName>l3_employee_level</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#8fa0b0</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_id">
<rd:DefaultName>l3_employee_id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_name">
<rd:DefaultName>l3_employee_name</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l3_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l3_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l3_employee_id.Value, CStr(Fields!l3_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l3_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>7.61904cm</Width>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.55873cm</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
</TableColumns>
<Height>2.15872cm</Height>
</Table>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.91429cm</Height>
<Value>Report3</Value>
</Textbox>
</ReportItems>
<Height>3.60635cm</Height>
</Body>
<rd:ReportID>0b7fa61b-6ddf-4332-bf17-5b42a10a7437</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_employee">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH employee AS
(
SELECT employee_id = 1, employee_name = 'Manager 1', manager_id = NULL, level_no = 1
UNION ALL
SELECT employee_id = 2, employee_name = 'Sub Manager 1', manager_id = 1, level_no = 2
UNION ALL
SELECT employee_id = 3, employee_name = 'Leaf Employee 1', manager_id = 2, level_no = 3
UNION ALL
SELECT employee_id = 4, employee_name = 'Leaf Employee 2', manager_id = 1, level_no = 2
)
SELECT l1_employee_id = l1.employee_id
, l1_employee_name = l1.employee_name
, l1_employee_level = l1.level_no

, l2_employee_id = l2.employee_id
, l2_employee_name = l2.employee_name
, l2_employee_level = l2.level_no

, l3_employee_id = l3.employee_id
, l3_employee_name = l3.employee_name
, l3_employee_level = l3.level_no

FROM employee l1

LEFT JOIN employee l2
ON l1.employee_id = l2.manager_id

LEFT JOIN employee l3
ON l2.employee_id = l3.manager_id

WHERE ISNULL(l1.level_no, 1) = 1
AND ISNULL(l2.level_no, 2) = 2
AND ISNULL(l3.level_no, 3) = 3</CommandText>
<DataSourceName>ds_master</DataSourceName>
</Query>
<Fields>
<Field Name="l1_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_id</DataField>
</Field>
<Field Name="l1_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l1_employee_name</DataField>
</Field>
<Field Name="l1_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_level</DataField>
</Field>
<Field Name="l2_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_id</DataField>
</Field>
<Field Name="l2_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l2_employee_name</DataField>
</Field>
<Field Name="l2_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_level</DataField>
</Field>
<Field Name="l3_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_id</DataField>
</Field>
<Field Name="l3_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l3_employee_name</DataField>
</Field>
<Field Name="l3_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_level</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>22.85714cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

[/code]

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.