Multilevel data report

I'm working on a report with multi level data.

let's 4 levels: project level, subproject elvel, component level and item level.
report need to display:
Project name and 3 other project leel data fields
subproject associated with each project, and other subproject level data fields
component associated with each subproject, and other component level data fields
item associated with each component, and other item level data fields

I was thinking to build 4 levels of drill down to display each level, but each level has its own level fields to display, which make column title issue. Then I thought about subreport, but I still have no idea.

How to display or make the report? What would be the normal way to deal with such kind reports? Please help.

Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>with all four being connected to their own data source.
>and other { x } level data fields
Nope.  The above statements are mutually exclusive, unless you want a super wide data source.

Better to have four data sources, with all the columns / parameters needed to daisy chain them together to pull off the drill down, and whatever other { x }-specific columns you need.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Smells like a report with three subreports, with all four being connected to their own data source, and connecting the parameters to make them work together.  (Right-Click, Subreport properties, match fiels with parameters).
minglelinchAuthor Commented:
Ok. If I create 3 subreports,  with all four being connected to their own data source. How the report look like?

If 3 subreports contain the following, but how to get together?
level4ID level4field1 level4field2 level3ID
                                                   level3ID level3field1 level3field2 level2ID
                                                                                                      level2ID level2field1 level2fie

My mind is full of table row with drill down. How my report should look like? I still need help.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

minglelinchAuthor Commented:
So 4 datasets and drill down, that chain together and report looks likeas below, right?

L1id, L1D1, L1D2, L1D3
                                         L2id, L2D1,L2D2, L2D3
                                                                               L3id, L3D1, L3D2,L3D3
                                                                                                                       L4id, L4D1, L4D2, L4D3

3 subreports with each subreport has its own title, right?

So create subreport for level4 in one data cell of level3, and create subreport for level3 in one data cell of level2, and same for up to level1, and when clicking drill down subreport opens. Is that right? This is my first time to create rdl subreport. I appreciate help.
ValentinoVConnect With a Mentor BI ConsultantCommented:
To be honest I wouldn't use subreports for this.  Just a table with three row groups.

If the rows of the different groups need different layout you can achieve that by nesting tables inside the main one.  Try it out, you'll see.  Drag a table inside one of the cells and see how it behaves.  Before doing this, ensure you've increased the row height on your main table because manipulation will become difficult otherwise.

And as for titles, do you really want/need each level to have its own title row?  That's a lot of titles and wasted space.  I usually only add column titles for the detail level, no titles for the grouped levels because their meaning is usually clear by the name/title of the report, such as "Performance Breakdown by Project by Subproject by Component".
minglelinchAuthor Commented:
Thank you both for the ideas. it seems like I still need 3 datasets for either way.

I still need help with those level id parameters passing. If I build 3 datasets at design time, each dataset need at least one (levelid) parameter. How to pass it?

My mind is filled with impression that a store procedure call a function by passing id which calls another function by passing another id ... to get a full data. But how to break down to feed three datasets on report side?

I appreciate help.
minglelinchAuthor Commented:
Nesting table is a nice way.  "add column titles" - I guess that's the column name, which is good. But how to connect them by passing levelIDs?
ValentinoVBI ConsultantCommented:
But how to connect them by passing levelIDs?

My mind has lost yours here.  Can you explain a bit more what you're referring to?  An example can help.

What I would build is something like this (using dots to simulate columns):

Column headers........................C1...C2...C3..C4
Project-level data......................P1..P2
....Subproject-level data............S1...S2
........Component-level data.......C1...C2
............Item-level detail data....I1....I2...I3...I4
minglelinchAuthor Commented:
Thanks for solution for the layout.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.