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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Jim HornMicrosoft 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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.
ValentinoVBI 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.