Multiple Levels in Table

Hi all,

I have a data set where every element has a parent (which can have another parent and so on and on and on...) and it's level in the hierarchy assigned to it.

The problem is that the number of different levels is dynamic and I don't know how to implement this in BIRT with groups.

It should look something like this in the end:

AMOUNT
Element1_____________________****
...Element2___________________****
...Element3
......Element4
...Element5
......Element5
.........Element6
..............Element7
.........Element8
.........Element9
Element10
Element11
...Element12
......Element13

Any help would be greatly appreciated!

Thanks

Stan

Find more posts tagged with

Comments

averma

Hi Stan:

Drill-down reports is one way to implement this. You can find several examples and articles on this topic on this site.

Ashwini

sbanaszak

Hello Ashwini,

Thanks for your reply! I searched on this site and on google for examples of drill down reports but I don't see how that would help me in creating this table. If I am getting this right it would open a new window with the details of the next level if you click on the parent. Is there no way to just have one table to show it all? Maybe we are not meaning the same thing or I probably just don't get something about drill down.

You said it's only one way of doing it?

Thank you very much!

Regards,
Stan

sbanaszak

Anyone

?

I solved the problem with CONNECT_BY_ROOT for the time being but it's not an optimal solution (elements that have children can't have an amount and I just hide them) and I'd be very thankful if there is a way to implement it right or if somebody could explain to me how drill-down would help me with this.

mwilliams

Stan,

How do you distinguish which ones are parents, children, etc?

sbanaszak

Hi Michael,

Every element has an ID and a Parent_ID, where Parent_ID says which ID the element's parent has.

Example:
ID///Element///ParentID
00///Element0///NULL
01///Element1///00
02///Element2///00
03///Element3///01
04///Element4///03
05///Element5///04

The problem is that the number of levels (how deep it goes) is dynamic (also which one is a parent of others etc).

Also, every element has an amount associated with it. Parent elements cannot have an amount.

Thanks

sbanaszak

*bump*

Worth a try to bring it back

mwilliams

Stan,

Do the rows always come in in order like you show them in your sample data?

sbanaszak

Michael,

Every level is sorted alphabetically like this (update: and are pretty random in the table):

A
..A
..B
....A
..C
B
C
D
..A
..B
E

Rgds,
Stan

sbanaszak

*bump*

I know it's annoying but I'm not giving up on this yet

mwilliams

Stan, 
 
So, is the data you showed me earlier the way the data comes in? Just like this only with a value as well? 
 
<blockquote class='ipsBlockquote' data-author="sbanaszak">
Example: 
ID///Element///ParentID 
00///Element0///NULL 
01///Element1///00 
02///Element2///00 
03///Element3///01 
04///Element4///03 
05///Element5///04 
 </blockquote>
 
If not, let me know how it looks in your dataSet. If it looks like this, I have a way to make the data look like the following screenshot. Also, I can determine a "Level" field with the same logic in a computed column as you can see in the screenshot, which may help if you need to use grouping.

sbanaszak

Hi Michael,

The data comes from a table with the columns
ID, NAME, PARENT_ID, AMOUNT, AMOUNT_FLAG

but it is not sorted, so members of the same group are spread over the whole table (so the element with ID 123 can be a child of element 1). Maybe my example was a bit misleading.

Also if an element is a parent of others it can't have an amount, so the AMOUNT_FLAG is set to 'No'.

I know it's a hassle like this I can't post the report file because without the source data it is pretty useless

.

The way it's shown in your screenshot is great!

Thank you
Stan

mwilliams

Stan,

That does make it tougher not having it in order when it comes in. I'll have to think about that. How to sort it to get it the right way if possible. It may require another computed column that determines the root parent of each element.

mwilliams

Stan,

I was able to make a computed column that determined the root parent, but that didn't help a whole lot. I don't see a way so far to get them ordered in the correct way because of the multiple, dynamic levels. Having a way to sort the data before bringing it in would be great.

sbanaszak

Thanks for your help Michael. I'm going to see if I can get the data ordered within the table somehow. Maybe add a report_id column and order by that or something like that. Then it would work, right?

mwilliams

Stan, 
 
If you can get them in the correct order somehow. Here's what I did to find the level and display the Element_Name the way I did. In a computed column called "level", you use integer as the type and this as the expression: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (row["ParentID"] == "NULL"){
reportContext.setPersistentGlobalVariable(row["ID"], "0");
0;
}
else{
j=row["ParentID"];
i = reportContext.getPersistentGlobalVariable(j);
i = parseInt(i);
i++;
k=i.toString();
reportContext.setPersistentGlobalVariable(row["ID"], k);
i;
}
</pre>
 
Then, in the table, I deleted the Element_Name data item and put a dynamic text box there with the following code: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
i=0;
display = "";
while (i<row["Level"]){
display = display + "...............";
i++;
}
display = display + row["Element"];
display;
</pre>
 
If you think the "rootParent" column would be helpful, let me know. I can tell you what I did to determine it as well. Good luck!!

sbanaszak

Thanks a lot Michael I will try this.

Rgds.
Stan