asked on

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.

Thanks.

Jim Horn

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).

minglelinch

ASKER

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

minglelinch

ASKER

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.

SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

minglelinch

ASKER

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.

minglelinch

ASKER

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?

ValentinoV

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

minglelinch

ASKER

Thanks for solution for the layout.