Grouping issue in SSRS

Question

So I have a table that has divisions and sub-divisions. The structure is as follows:

COMPANYID | DIVISIONID | DIVISION | PARENTID
    1     |     1      | North    | NULL
    1     |     2      | South    | NULL
    1     |     3      | East     | NULL
    1     |     4      | West     | NULL
    1     |     5      | Sales    | 1
    1     |     6      | Service  | 1
    1     |     7      | Sales    | 2
    1     |     8      | Service  | 2
    1     |     9      | Sales    | 3
    1     |     10     | Service  | 3
    1     |     11     | Sales    | 4
    1     |     12     | Service  | 4

The idea behind the table is that the data would indicate all of those divisions/subdivisions are the for the same company. Anything with a null PARENTID is a "Division" and anything that has a PARENTID is a "Sub-Division". There will be a max of 2 tiers (Division --> Subdivision). This table would create the following hierarchy:

Company 1
    North
        Sales
        Service
    South
        Sales
        Service
    East
        Sales
        Service
    West
        Sales
        Service

I am trying to make an SSRS report that shows details about the company including a table with all the divisions and sub-divisions. I am using the following query to retrieve a list of all the divisions and subdivisions:

SELECT division, parentid
FROM division
WHERE companyid=@companyid

@companyid is a parameter for the report. My thought process is that I want create a table that has a Parent group and a Detail group where the Parent group would be all those rows that have a null PARENTID and then the Detail group would list all rows whose PARENTID equals the divisionid of Parent row. I'm just not sure how to set that up in the groups. Filters? Separate queries? Do it in the SQL query?

Any and all help is greatly appreciated! Thanks!

Silagy · Answer 1 · 2013-01-30T23:29:30.777

1

If i understood your goal correctly, you want the report to display the information in hierarchy mode.

If so you can achieve it with setting a Recursive Hierarchy Group.

Reporting services has the ability to display "hierarchy" information in a "tree" layout

For more information look here

You don't have to create another query the first one that has the CompanyID And the ParentID is just fine

edited Jan 30 '13 at 23:29

answered Jan 30 '13 at 23:20

Silagy

3,053
2
27
39

score 1 · Accepted Answer · answered Jan 30 '13 at 23:21

1

For a simple hierarchy with a maximum of 2 levels you can just join the table on itself:

SELECT D1.CompanyId, D1.DivisionId, D1.Division, D2.Division AS Subdivision
FROM DivisionTable D1
INNER JOIN DivisionTable D2 ON D1.DivisionId = D2.ParentId AND D1.CompanyId = D2.CompanyId
WHERE D1.CompanyId = @CompanyId

answered Jan 30 '13 at 23:21

Chris Latta

20,316
4
62
70

This is what I ended up doing...I joined it on itself and selected division and parent.division then just grouped on parent.division. Thanks for the help! – D.R. Jan 31 '13 at 03:21

score 0 · Answer 3 · answered Jan 30 '13 at 23:23

You just need your select statement as a dataset and SSRS can do the grouping for you. You can even tier on multiple levels or better yet collapse and expand your groupings based on filters.

How to get started:

Create your dataset as you have shown
Put in a table object from the toolbox
Only put in the columns of detail data
In the lower left of the 'Design' layout click the 'detail's and click 'add Group>Parent Group'. Select your grouping
If you want you can 'collapse' on demand the details columns. You can choose 'Visibility' on the detail grouping. 'When the report is initially run'>Choose 'Hide'. Check 'Display can be toggled by this report item': Select a cell reference to what you want to expand the details with.

Grouping issue in SSRS

3 Answers3